How to count number of occurrences of character or word in a string in Sql

Introduction: In this article I have explained how to find number of times a particular character or word or text exists/appears in a string or sentence in SQL.

In previous articles i explained 20 main differences between stored procedures and functions in sql and Pass table name as parameter to sql server stored procedure or query and Remove first or last character from string or column in sql and Query to count male and female and total employees and Convert comma separated string into table rows in sql server

Implementation:  Let’s understand by taking an example

DECLARE @string VARCHAR(MAX)='This is test string to test'

DECLARE @tosearch VARCHAR(MAX)='test'

SELECT (DATALENGTH(@string) - DATALENGTH(REPLACE(@string,@tosearch,''))) / DATALENGTH(@tosearch) AS OccurrenceCount

Explanation:

The length of the our main string is calculated using DATALENGTH(@string) and it is of length 27. 

Then using REPLACE function I replaced 'test' in the string with '' so eight characters were replaced and then calculated the length of replaced string using DATALENGTH(REPLACE(@string,@tosearch,'')) and it is of length 19.

Finally I calculated the length of the string that we want to search within our string I.e. 'test' using DATALENGTH(@tosearch) and it is of length 4.

So to calculate the No. of occurrences , query will be evaluated as= (27-19)/4=2

So the word 'test' occurred in the string ‘This is test string to test’ 2 times

Note:

1. If we are working with unicode data i.e. using NVARCHAR instead of VARCHAR, it will show double length i.e. (54-38)/8=2, as a character takes two bytes. But the result will be the same as you can see.
2. We can even use LEN function instead of DATALENGTH function in the above query to count the length of string. LEN function counts the spaces in between the character and leading spaces but does not count the trailing spaces so may confuse you if the string contains space at the end. So better is to use DATALENGTH that even counts trailing spaces.

Let's take one step further. We can even create a user defined function to find the occurrence of ant word or character in a string as:

User Defined Function to Count number of occurrence of character of word in a string/sentence

CREATE FUNCTION fnCountOccurrencesOfString

(

    @string NVARCHAR(MAX),

    @tosearch NVARCHAR(MAX)

)

RETURNS INT

AS

BEGIN

    RETURN (DATALENGTH(@string) - DATALENGTH(REPLACE(@string,@tosearch,''))) / DATALENGTH(@tosearch)

END

To call:

SELECT dbo.fnCountOccurrencesOfString('This is test string to test','test') AS OccurrenceCount

Result will be 2

SELECT dbo.fnCountOccurrencesOfString('This is test string to test','t') AS OccurrenceCount

Result will be 7 

Now over to you:

A blog is nothing without reader's feedback and comments. So please provide your valuable feedback so that i can make this blog better and If you like my work; you can appreciate by leaving your comments, hitting Facebook like button, following on Google+, Twitter, Linkedin and Pinterest, stumbling my posts on stumble upon and subscribing for receiving free updates directly to your inbox . Stay tuned and stay connected for more technical updates.

• Tweet
• Share
• Share
• Share

Related Post