Extracting a specific number of words from a string in sql

Question

Extracting a specific number of words from a string in sql

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply

chinhvowili Grasshopper Points: 16 More actions · Answer 1

Adam Haines - Wednesday, February 27, 2008 7:32 AM
I found a function that may be more along the lines of what you are looking for. This function requires two inputs and the number of words you would like returned. I did not write the code and will give credit to the programmer. His link is http://www.tom-muck.com/blog/index.cfm?newsid=72CREATE FUNCTION udf_GetNumberOfWords ( @stringToSplit varchar(8000), @numberOfWords int)RETURNS varchar(8000) AS BEGIN DECLARE @currentword varchar(8000)DECLARE @returnstring varchar(8000)DECLARE @wordcount intSET @wordcount = 0SET @returnstring = ''SET @currentword = ''SET @stringToSplit = ltrim(rtrim(@stringToSplit))Declare @index intWHILE @wordcount 0 BEGIN Select @index = CHARINDEX(' ', @stringToSplit) if @index = 0 BEGIN SELECT @currentword = ltrim(rtrim(@stringToSplit)) SELECT @wordcount = @numberOfWords END else BEGIN IF (len(@stringToSplit) - @index > 0) BEGIN SELECT @currentword = ltrim(rtrim(LEFT(@stringToSplit, @index-1)))--the new shortened string SELECT @stringToSplit = RIGHT(@stringToSplit,LEN(@stringToSplit) - @index) -- the rest END END SELECT @returnstring = @returnstring + ' ' + @currentword SELECT @wordcount = @wordcount + 1 ENDSET @returnstring = LTRIM(@returnstring)RETURN @returnstringENDThe function can be called like this: (2 is the number of words to return)select dbo.udf_GetNumberOfWords(mycolumn,2)from mytable

Thanks so much, Cool script 😀

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

chinhvowili - Sunday, November 19, 2017 12:01 AM
Adam Haines - Wednesday, February 27, 2008 7:32 AM
I found a function that may be more along the lines of what you are looking for. This function requires two inputs and the number of words you would like returned. I did not write the code and will give credit to the programmer. His link is http://www.tom-muck.com/blog/index.cfm?newsid=72CREATE FUNCTION udf_GetNumberOfWords ( @stringToSplit varchar(8000), @numberOfWords int)RETURNS varchar(8000) AS BEGIN DECLARE @currentword varchar(8000)DECLARE @returnstring varchar(8000)DECLARE @wordcount intSET @wordcount = 0SET @returnstring = ''SET @currentword = ''SET @stringToSplit = ltrim(rtrim(@stringToSplit))Declare @index intWHILE @wordcount 0 BEGIN Select @index = CHARINDEX(' ', @stringToSplit) if @index = 0 BEGIN SELECT @currentword = ltrim(rtrim(@stringToSplit)) SELECT @wordcount = @numberOfWords END else BEGIN IF (len(@stringToSplit) - @index > 0) BEGIN SELECT @currentword = ltrim(rtrim(LEFT(@stringToSplit, @index-1)))--the new shortened string SELECT @stringToSplit = RIGHT(@stringToSplit,LEN(@stringToSplit) - @index) -- the rest END END SELECT @returnstring = @returnstring + ' ' + @currentword SELECT @wordcount = @wordcount + 1 ENDSET @returnstring = LTRIM(@returnstring)RETURN @returnstringENDThe function can be called like this: (2 is the number of words to return)select dbo.udf_GetNumberOfWords(mycolumn,2)from mytable
Thanks so much, Cool script 😀

It's NOT a "cool script". It's a performance killer. I recommend that you DON'T use it. Even the other script on this post that uses the numbers table will do better.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)