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