x002548's Blog

Not Just a Number - Brett Kaiser
posts - 89, comments - 586, trackbacks - 39

Find a word in a string

By doing this with an identity Column on the row, you have a pseudo array for sql server.  This user defined function will grab the nth occurance of a word in a string.  By marrying that with the IDENTITY, it can be assumed to be an array.  The next parts of these would be to be able to perform functions like DELETE and UPDATE of the word in that location (which may be more trouble than it's worth.)  Pluse in Ken Henderson's Book, “The Guru's Guide to Stored Procedures, XML, and HTML”, has an entire chapter dedicated to “creating” arrays in sql server.  You have to be extremely careful about memory management and object management.  Suffice it to say, I don't have a box I can thouroughly destroy, so I never played with it.

But this concept is similar.  At the very minium, this is an extension of all the “How do I parse words from a string” questions.

Anyway, here's the udf.

   @str nvarchar(4000)
 , @Word int
 , @Delim char(1)
RETURNS nvarchar(4000)
 DECLARE @LastPosition int, @DelimFound int, @Start int, @End int, @WordFound nvarchar(4000)

 SELECT @LastPosition = 0, @DelimFound = 0
 WHILE (@DelimFound < @Word-1)
  IF (CHARINDEX(@Delim, @str, @LastPosition + 1) = 0)
   SET @LastPosition = CHARINDEX(@Delim, @str, @LastPosition + 1)
   SET @DelimFound = @DelimFound + 1
 SET @Start = @LastPosition + 1
 SET @End = CHARINDEX(@Delim, @str, @LastPosition + 1) - @Start
 IF (@End = 0) SET @End = LEN(@str)
 SELECT @WordFound = SUBSTRING(@str,@start,@end)
 RETURN @WordFound


Print | posted on Monday, May 09, 2005 3:51 PM | Filed Under [ SQL Server ]



# re: Find a word in a string

Thanks Tara...I remeber when he first posted that...man how time has flown.....

# re: Find a word in a string

Check out my upcoming title, _Pro SQL Server 2005_ (Apress), which will discuss creating arrays using managed types. I think you'll find it it be a much nicer strategy for SQL Server 2005 -- and it's surprisingly simple to pet together!
5/12/2005 9:21 AM | Adam Machanic

# re: Find a word in a string

Hi Brett,

I'm pretty sure my array code won't "thoroughly destroy" your box (or anyone else's, for that matter), so I'd encourage you to give it a try if you need real arrays in pre-Yukon T-SQL. The code works, is relatively fast, and is as integrated with T-SQL as it can be given the constraints.


7/18/2005 9:25 PM | Ken Henderson

# Thanks Ken

Gotta talk to Bill, but I was never emailed about your post here.

Doesn't your book discuss the importance of destroying the array object after they aren't needed anymore?

Otherwise you could start to eat up memory?

I'm pretty sure a bounce would clean it up though. Isn't that the case?

And Ken, thanks so much for your comments.

8/1/2005 12:32 PM | Brett
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET