T-SQL – How to Find Number of Words in a Given String

TSQL: To find number of words in a given string

Download the code https://gallery.technet.microsoft.com/T-SQL-How-to-Find-Number-533b6b39

DECLARE @DemoTable Table
    (Col varchar(500));

INSERT  INTO @DemoTable (Col)VALUES ('JOHN M SMITH ABC COMPANY')
 

SELECT col,
  len(Col) - len(replace(col, ' ', '')) + 1 No_Of_Words
  from @DemoTable

No_Of_Words

When the string has more spaces

DECLARE @DemoTable Table
    (Col varchar(500));

INSERT  INTO @DemoTable (Col)VALUES ('JOHN     M SMITH ABC      COMPANY asd   ')
 

SELECT col,
  len((replace(replace(replace(col,' ','<>'),'><',''),'<>',' '))) - len(replace((replace(replace(replace(col,' ','<>'),'><',''),'<>',' ')),' ','')) + 1 No_Of_Words
  from @DemoTable

Output:-

No_Of_Words_1

When the string has tabs, spaces and carriage return

DECLARE @DemoTable Table
    (Col varchar(500));

INSERT  INTO @DemoTable (Col)
VALUES ('JOHN		 M SMITH ABC COMPANY 
LLC  
USA')

SELECT col,
  len((replace(replace(replace(REPLACE(REPLACE(col,char(13),''),char(9),''),' ','<>'),'><',''),'<>',' '))) - len(replace((replace(replace(replace(REPLACE(REPLACE(col,char(13),''),char(9),''),' ','<>'),'><',''),'<>',' ')),' ','')) + 1 No_Of_Words
  from @DemoTable

Output:-

No_Of_Words_3

Advertisements

About Prashanth Jayaram

DB Technologist, Author, Blogger, Operations-Mgr at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in SQL. Bookmark the permalink.

4 Responses to T-SQL – How to Find Number of Words in a Given String

  1. Jonathan says:

    Very handy trick; thanks for sharing it. It presumes that there are no carriage returns or tabs in the string, and that there are not two spaces together. If you can assure that the data meets those criteria, this works well.

  2. Sam says:

    Pretty cool Prashanth 🙂
    Thanks for sharing mate.
    Best Wishes,
    ~Sam~

  3. That’s brilliant, thank you for sharing!

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