Count number of words in a string

Tony Rogerson in his weblog posted about Counting the number of words in a string

Here are two more methods

1 Using the method I posted already about Squeeze Function to remove multiple spaces

Declare @s varchar(100)

set @s=' See how many words this has '

Select len(string)-len(replace(string,' ',''))+1 as no_of_words from

(

Select replace(replace(replace(ltrim(@s),' ',' ~!@#'),'~!@# ',''),'~!@#','') as string

) as t

2 Using Number table approach used to Extract only numbers from a String

Declare @s varchar(100),@result varchar(100)

set @s=' See how many words this has ' set @result=''

Select @s=replace(replace(replace(ltrim(@s),' ',' ~!@#'),'~!@# ',''),'~!@#','')

select count(*)+1 as no_of_words from

(

select substring(@s,number,1) as number from

(

select number from master..spt_values where type='p' and number between 1 and len(@s)

) as t

) as t

where number='' 

 

Published Friday, December 21, 2007 9:32 AM by Madhivanan

Comments

# kraiger22 » Count number of words in a string

Saturday, December 22, 2007 11:02 PM by kraiger22 » Count number of words in a string

PingBack from http://kraiger22.adoption23.info/1969/12/31/count-number-of-words-in-a-string/