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 @DemoTable
Output:-
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:-
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.
LikeLike
Hi Jonathan,
I’ve added few more scripts to handle the those criteria as well. thanks for reading my space.
–Prashanth
LikeLike
Pretty cool Prashanth 🙂
Thanks for sharing mate.
Best Wishes,
~Sam~
LikeLike
That’s brilliant, thank you for sharing!
LikeLike