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.
Count number of occurrences of character or word in a string in Sql

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

  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)
    RETURN (DATALENGTH(@string) - DATALENGTH(REPLACE(@string,@tosearch,''))) / DATALENGTH(@tosearch)

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.
Next Post »

If you have any question about any post, Feel free to ask.You can simply drop a comment below post or contact via Contact Us form. Your feedback and suggestions will be highly appreciated. Also try to leave comments from your account not from the anonymous account so that i can respond to you easily..