Count Strings value in Column using SQL Query

how to count strings value in a column in SQL Query?


Column1                                     Count

Yes Indeed                                    2
I love Fruit                                     3
Fox Jumps Over the Fence         5

I really appreciate any help.

Thank you.
Queennie LAsked:
Who is Participating?
Bill PrewCommented:
One approach is the following (assuming there is one space between each word), see if this works for you:

SELECT column1, LEN(column1)-LEN(REPLACE(column1, ' ', ''))+1 Words FROM Table1;

Open in new window

Queennie LAuthor Commented:
Thank you Bill Prew.

It is working.

Have a good day sir!
Nitin SontakkeDeveloperCommented:
Little late, but post it anyway, now that I have typed so much in query analyser, might as well paste it here...

declare @someTable table
  [someColumn] varchar(100) not null

insert into @someTable values
 ('Yes Indeed')
,('I love Fruit')
,('Fox Jumps Over the Fence')

select [someColumn], len([someColumn]) - len(replace([someColumn], ' ', '')) + 1 [wordCount]
from @someTable

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.