Find the count of words in string

SQL: How to find the count of words in following example?

declare @s varchar(55) = 'How to find the  count  of words in this string ?'

Subquestions:

1. How to count spaces?

2. How to count double/triple/… spaces as one? answer by Gordon Linoff here

3. How to avoid counting of special characters? Example: 'Please , don't count this comma'

4. Is it possible without string_split function (because it’s available only since SQL SERVER 2016)?

Summary with the best solutions HERE

Answer

Using string_split (available only since SQL SERVER 2016):

declare @string varchar(55) = 'How to find the  count  of words in this string ?';

select count(*) WordCount from string_split(@string,' ') where value like '%[0-9A-Za-z]%'

The same idea is used in following answers:

• https://stackoverflow.com/a/57783421/6165594
• https://stackoverflow.com/a/57783743/6165594

Without using string_split:

declare @string varchar(55) = 'How to find the  count  of words in this string ?';

;with space as 
    (   -- returns space positions in a string
        select cast(           0                   as int) idx union all
        select cast(charindex(' ', @string, idx+1) as int)     from  space
        where       charindex(' ', @string, idx+1)>0
    )
select count(*) WordCount from space 
where substring(@string,idx+1,charindex(' ',@string+' ',idx+1)-idx-1) like '%[0-9A-Za-z]%'
OPTION (MAXRECURSION 0);

The same idea is used in following answers:

• https://stackoverflow.com/a/57787850/6165594

As Inline Function:

ALTER FUNCTION dbo.WordCount
(   
    @string        NVARCHAR(MAX) 
,   @WordPattern   NVARCHAR(MAX) = '%[0-9A-Za-z]%'
)
/*  
Call Example:

    1) Word count for single string:

    select * from WordCount(N'How to find the  count  of words in this string ? ', default)


    2) Word count for set of strings:

    select *
     from (
               select 'How to find the  count  of words in this string ? ' as string union all 
               select 'How many words in 2nd example?'
           ) x    
    cross apply WordCount(x.string, default)

Limitations:
    If string contains >100 spaces function fails with error:

    Msg 530, Level 16, State 1, Line 45
    The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

    NB! OPTION (MAXRECURSION 0); -- don't work within inline function

*/
RETURNS TABLE AS RETURN 
(    
    with space as 
    (   -- returns space positions in a string
        select cast(           0                   as int) idx union all
        select cast(charindex(' ', @string, idx+1) as int)     from  space
        where       charindex(' ', @string, idx+1)>0
    )
    select count(*) WordCount from space 
    where substring(@string,idx+1,charindex(' ',@string+' ',idx+1)-idx-1) like @WordPattern
    --    OPTION (MAXRECURSION 0); -- don't work within inline function
);

go