Search Count of Words Within a String Using SQL

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 

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

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 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, ',', ''))

How to count instances of character in SQL Column

In SQL Server:

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

MS SQL - Count the occurrences of a word in a field

There is a STRING_SPLIT support from sql-server 2016 if your version lower than 2016,you can try to write a split function to split your column by ,

CREATE FUNCTION fn_split ( @stringToSplit VARCHAR(MAX) )
RETURNS
 @returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN

 DECLARE @name NVARCHAR(255)
 DECLARE @pos INT

 WHILE CHARINDEX(',', @stringToSplit) > 0
 BEGIN
  SELECT @pos  = CHARINDEX(',', @stringToSplit)  
  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

  INSERT INTO @returnList 
  SELECT @name

  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
 END

 INSERT INTO @returnList
 SELECT @stringToSplit

 RETURN
END

then use CROSS APPLY to get count by the name.

SELECT Name,count(*)
FROM T t1 CROSS APPLY fn_split(t1.col) v
group by Name

sqlfiddle

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!)

---

Related Topics