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
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 @DemoTableOutput:-
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:-
