Count number of words in a string in Sqlserver.

Hi all, in this post we try to calculate total number of words in a given string.

Logic: First of all we trim our input string from both ends and then we replace multiple white spaces with single white space. Our required output is total white space plus one, and to get it we subtract string length without white space from our final string’s length.

Ex:

input string: ‘    Hi all          this       is                                         test                               entry to demonstrate               word                  count         ‘

After Trim:’Hi all          this       is                                         test                               entry to demonstrate               word                  count’

Replace multiple spaces: ‘Hi all this is test entry to demonstrate word count’

Length of our string: LEN(t.NewString)=51

Length of our string without white space = LEN(replace(t.NewString,’ ‘,”))=42

Total words: 51-42+1 = 10

DECLARE @str nvarchar(1000),@len int, @next int

set @str=LTRIM(RTRIM(‘    Hi all          this       is                                         test                               entry to demonstrate               word                  count         ‘))

 select LEN(t.NewString)-LEN(replace(t.NewString,’ ‘,”))+1 from (

SELECT replace(replace(REPLACE(@str,’ ‘,’ *’),’* ‘,”),’*’,”) as NewString

) as t

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s