RETURN shorter word from WORD COUNT 2

[Out2work]

Hi , I need a function where i pass it a description and get a truncated description if the word count = 30

can someone please help me with this

 

[Out2work]

Not fully tested but this seems to return a complete word at the end of set length which helps

--Code
select top 1 Left([description],(121-Charindex(' ',Reverse(left([description],120)))))from Table

 

[r937]

how do you want to truncate it?

and why exactly 30 words?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[bborissov]

Not sure what you want.
Could you post some example data and what you want from it?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

You may want to provide more description - the input and desired output.

In the meantime, I think this link of how to find how many occurrences of a string in a string may be of some help

http://wiki.lessthandot.com/index.php/Find_Out_How_Many_Occurrences_Of_A_Substring_Are_In_A_String

 

[Out2work]

THis is for a website

Product descriptions can be long and im using a popup with set length and width to display the item description.

So i want the end of description to say ...(Click to read more)

-----Sample Data of description example:------

"Sample desciption Armoir helps preserve your items with a special lining while it carefully organizes your treasures with cubbies, hooks, drawers, and compartments for everything. Spacious is a fine piece of furniture, crafted from fine wood and wood veneers.

* keeper anti-tarnish lining to keep your silver jewelry from tarnishing for up to 40 years with proper use
* Holds approximately 500 pieces of items
* Specially designed compartments and 10 earring stands
* Tilit-back lid with wood-framed mirror
* Measures approximately 44 x 21 x 12
* Made in China
"

------Output should be:-------

"Sample desciption Armoir helps preserve your items with a special lining while it carefully organizes your treasures with cubbies, hooks, drawers, and compartments for everything. Spacious is a " + ...(Click to read more)

 

[markros]

Do you always want 30 words or pick some length and return a string within this length but ended with a word (not truncate words)?

 

[Out2work]

Yes i would like to force an amount of words 30 in this case for all descriptions

 

[bborissov]

And what if the 31th starts word on 1243 char?
(there are languages with very long words, you know)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[bborissov]

Check this:

http://www.universalthread.com/Repo...A685059415944324A4F72796D72484263756E63413264

Maybe there is a function that could help you.
Esp. OCCURS(), GETWORDNUM() ...

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

I think we need to split words and create a string as we go, so in this particular case I recommend to start from

http://berezniker.com/content/pages/sql/microsoft-sql-server/alines-udf-string-split

and modify to return a scalar instead consisting of N number of words.

Usually I recommend to use different functions to splitting words, but in this case this function may be the best to work with.

 

[RyanEK]

Hi,

If you're using SQL2005+ you can solve this using XML PATH for.

First create a SPLIT function

CREATE FUNCTION Split
(
  @delimited nvarchar(max),
  @delimiter nvarchar(20)
) RETURNS @t TABLE
(
  val nvarchar(max)
)
AS
BEGIN
  declare @xml xml
  set @xml = N'<root><r>' + replace(@delimited,@delimiter,'</r><r>') + '</r></root>'

  insert into @t(val)
  select 
    r.value('.','varchar(5)') as item
  from @xml.nodes('//root/r') as records(r)

  RETURN
END

Test it

select val 
from dbo.split('one two three four    five',' ')
where val <> ''

Notice all the empty characters are removed? You can then use XML path again to rebuild your string. In this case i'm grabbing the first 3 words.

select top 1 replace(
(select top 3 val as 'data()'         
 from (
  select val 
  from dbo.split('one two three four    five',' ')
  where val <> ''
 ) as t1
for xml path('')),' ',' ')

Result should be

one two three

Ryan

 

[markros]

IMHO, we're doing two steps operation - split first and then concatenate back. I was thinking it's better to modify the split function to do both operations at once.

I'll post a function version to get N words from a string later on.

 

[markros]

I just slightly modified the function I gave a reference earlier (didn't test it much) - it would be nice if you can compare it with Ryan's approach in terms of speed/performance:

ALTER FUNCTION GetShorterString 
	(@STR VARCHAR(8000),
	 @separator VARCHAR(16)=' ',
	 @NumOfWords int = 30) 
RETURNS varchar(8000)
AS
/* Splits passed string into items based on the specified separator string
 Parameters:
	@str  - The string to split
	@separator - The separator string ( space is default)
	@NumOfWords - number of items to retrieve
 Returns string 
*/
 
BEGIN
	DECLARE @Item VARCHAR(128), @pos INT, @FinalResult varchar(8000), @ItemNumber int
	set @FinalResult = ''
	set @ItemNumber = 0
	WHILE DATALENGTH(@STR) > 0 AND @ItemNumber < @NumOfWords
	BEGIN
		SET @pos = CHARINDEX(@separator, @STR)
		IF @pos = 0 		    
			SET @pos = DATALENGTH(@STR)+1
        set @ItemNumber = @ItemNumber + 1
		SET @Item = LEFT(@STR, @pos -1 )
		SET @STR = SUBSTRING(@STR, @pos + DATALENGTH(@separator), 8000)
		set @FinalResult = @FinalResult + @separator + @Item
	END
 
	RETURN substring(@FinalResult,2,len(@FinalResult))
 
END