Sql String: Counting Words Inside a String

SQL String: Counting Words inside a String

Here is one way to do it:

Create and populate sample table (Please save is this step in your future questions)

DECLARE @T AS TABLE
(
    id int identity(1,1),
    string varchar(100)
)

INSERT INTO @T VALUES
('I am not your father.'),
('Where are your brother,sister,mother?'),
('Where are your brother, sister and mother?'),
('Who are     you?')

Use a cte to replace multiple spaces to a single space (Thanks to Gordon Linoff's answer here)

;WITH CTE AS
(
SELECT  Id,
        REPLACE(REPLACE(REPLACE(string, ' ', '><' -- Note that there are 2 spaces here
                               ), '<>', ''
                       ), '><', ' '
                ) as string
FROM @T
)

Query the CTE - length of the string - length of the string without spaces + 1:

SELECT id, LEN(string) - LEN(REPLACE(string, ' ', '')) + 1 as CountWords
FROM CTE 

Results:

id  CountWords
1   5
2   4
3   7
4   3

Find the count of words in string

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

Search count of words within a string using SQL

declare @search varchar(10) = 'I'

select len(replace(PageText, @search, @search + '#')) 
- len(PageText) as Count from YourTable 

How can I count the number of words in a column in SQL Server

You could sum this function call:

SELECT SUM([dbo].[WordCount]([my_column]))
FROM   [my_table]

How do I Count the words in a string using regex

INSTR is also a viable option. By looking for the second occurrence of a space, that will indicate that the string has at least 3 words.

WITH
    books
    AS
        (SELECT 'Tom Sawyer' title FROM DUAL
         UNION ALL
         SELECT 'A tale of two cities' FROM DUAL
         UNION ALL
         SELECT 'The Little Prince' FROM DUAL
         UNION ALL
         SELECT 'Don Quixote' FROM DUAL)
SELECT title
  FROM books
 WHERE instr(title, ' ', 1, 2) > 0;

If you do with to stick with regex, the regex expression below can be used to find books that have 3 or more words.

WITH
    books
    AS
        (SELECT 'Tom Sawyer' title FROM DUAL
         UNION ALL
         SELECT 'A tale of two cities' FROM DUAL
         UNION ALL
         SELECT 'The Little Prince' FROM DUAL
         UNION ALL
         SELECT 'Don Quixote' FROM DUAL)
SELECT title
  FROM books
 WHERE REGEXP_LIKE (title, '(\S+\s){2,}');

(Thanks @Littlefoot for the books!)

How to count instances of character in SQL Column

In SQL Server:

SELECT LEN(REPLACE(myColumn, 'N', '')) 
FROM ...

How can I count the number of words in a string in Oracle?

You can use something similar to this. This gets the length of the string, then substracts the length of the string with the spaces removed. By then adding the number one to that should give you the number of words:

Select length(yourCol) - length(replace(yourcol, ' ', '')) + 1 NumbofWords
from yourtable

See SQL Fiddle with Demo

If you use the following data:

CREATE TABLE yourtable
    (yourCol varchar2(15))
;

INSERT ALL 
    INTO yourtable (yourCol)
         VALUES ('Hello To Oracle')
    INTO yourtable (yourCol)
         VALUES ('oneword')
    INTO yourtable (yourCol)
         VALUES ('two words')
SELECT * FROM dual
;

And the query:

Select yourcol,
  length(yourCol) - length(replace(yourcol, ' ', '')) + 1 NumbofWords
from yourtable

The result is:

|         YOURCOL | NUMBOFWORDS |
---------------------------------
| Hello To Oracle |           3 |
|         oneword |           1 |
|       two words |           2 |

How do you count the number of occurrences of a certain substring in a SQL varchar?

The first way that comes to mind is to do it indirectly by replacing the comma with an empty string and comparing the lengths

Declare @string varchar(1000)
Set @string = 'a,b,c,d'
select len(@string) - len(replace(@string, ',', ''))

---

Related Topics