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
Storing Image in Database Directly or as Base64 Data
Select Rows Having 2 Columns Equal Value
How to Return the Most Recent Logtime for Each Sku
Number of Times a Value Appears (Sql)
Postgres: Insert If Does Not Exist Already
Iif Statement With Multiple Conditions
How to Update Millions of Records in MySQL
How to Calculate Percentage of Counts in SQL
Auto Increment the Column Value by 1 Conditionally in Select Query in SQL
Sql Select Count for Multiple Columns in a Single Query
Splitting Date into 2 Columns (Date + Time) in SQL
A SQL Query to Get All the Records Where 5 Columns Are Same But Only One Column Is Different
Get the Number of Digits After the Decimal Point of a Float (With or Without Decimal Part)
How to Remove Line Feed Characters When Selecting Data from SQL Server
Create a Query That Will Display Employee Name and Department Number
How to Compare Timestamp Dates With Date-Only Parameter in MySQL