asked on

Select first 10 words

Hello, How do you select the first 10 words of a column named description with a datatype of text?

first, in sql server 2005, you have to avoid the TEXT data type, and use VARCHAR(MAX) instead.
then, what do you define "word", ie separator between the words? only space? or can there be comma, dot, ?, !, - , etc..?

gogetsome

ASKER

Hello Angel, is VARCHAR(MAX) available in 200 as well? I would like to use a space as the delimeter but will use a period is space would not work.

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Lowfatspread

try this

Select case when datalength(words +' ') - datalength(replace(words,' ','')) <=10 then words
else substring(words,1,
charindex(' ',words+space(20),
       charindex(' ',words+space(20),
       charindex(' ',words+space(20),
charindex(' ',words+space(20),
charindex(' ',words+space(20),
charindex(' ',words+space(20),
charindex(' ',words+space(20),
charindex(' ',words+space(20),
charindex(' ',words+space(20),charindex(' ',words+space(20),charindex(' ',words+space(20))+1)+1)
+1)
+1)
+1)
+1)
+1)
              +1)
              +1)
+1)
)
from (
select rtrim(ltrim(replace(convert(varchar(8000),yourcol),space(2),space(1)))) as words
,....
from yourtable
Where ...
) as x

you could also make it into a function...

Lowfatspread

hmmm yes forgot about "punctation"