November 19, 2017 at 12:01 am
Adam Haines - Wednesday, February 27, 2008 7:32 AMI 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 @returnstringEND
The 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
November 19, 2017 at 9:06 am
chinhvowili - Sunday, November 19, 2017 12:01 AMAdam Haines - Wednesday, February 27, 2008 7:32 AMI 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 @returnstringEND
The 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
Change is inevitable... Change for the better is not.
Viewing 2 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy