[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 426
  • Last Modified:

Select first 10 words

Hello, How do you select the first 10 words of a column named description with a datatype of text?
0
gogetsome
Asked:
gogetsome
  • 2
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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..?
0
 
gogetsomeAuthor Commented:
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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Hello Angel, is  VARCHAR(MAX) available in 2000 as well?
no. as you (cross) posted in the sql 2005 zone, I did not write that.

>I would like to use a space as the delimeter but will use a period is space would not work.
no problem. just get these 2 functions:


CREATE FUNCTION dbo.ParmsToList (@Parameters varchar(8000), @sep varchar(10) )
returns @result TABLE (Value varchar(8000))
AS  
begin
     DECLARE @TempList table
          (
          Value varchar(8000)
          )

     DECLARE @Value varchar(8000), @Pos int

     SET @Parameters = LTRIM(RTRIM(@Parameters))+ @sep
     SET @Pos = CHARINDEX(@sep, @Parameters, 1)

     IF REPLACE(@Parameters, @sep, '') <> ''
     BEGIN
          WHILE @Pos > 0
          BEGIN
               SET @Value = LTRIM(RTRIM(LEFT(@Parameters, @Pos - 1)))
               IF @Value <> ''
               BEGIN
                    INSERT INTO @TempList (Value) VALUES (@Value) --Use Appropriate conversion
               END
               SET @Parameters = RIGHT(@Parameters, LEN(@Parameters) - @Pos + len(@sep) - 1)
               SET @Pos = CHARINDEX(@sep, @Parameters, 1)

          END
     END    
     INSERT @result
     SELECT value
        FROM @TempList
     RETURN
END    


create function dbo.GetFirst10Words(@text varchar(8000))
returns varchar(8000)
as
begin
  declare @res varchar(8000)
  select top 10 @res = coalesce(@res + ' ') + value from dbo.ParmsToList(@text, ' ')
  return (@res)
end


and use the second function:

select dbo.GetFirst10Words(cast(yourfield as varchar(8000))) from yourtable
0
 
LowfatspreadCommented:
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...
0
 
LowfatspreadCommented:
hmmm yes forgot about "punctation"
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now