[Solved] Find occurrence of any char or word in SQL query?  

This is how it begins.. 

I happen to write this when I googled to find the answers to solve using SQL procedure instead of writing in programming language.

And mostly, during interview these are the frequently question.

When you are given a string and a word your task is that count the number of the occurrence of the given word in the string and print the number of occurrence of the word.

In this article - let's cover four aspects the interview questions asked.

1. Find FIRST occurrence of any character or word in the string

In the given below example, you need to search for the first occurrence of word 'Get' in the sentence.

SQL Query

DECLARE @String AS VARCHAR(100)

DECLARE @strSearch AS VARCHAR(100)


SET @String ='Get Set Solution is free source'

SET @strSearch ='Get'


--Find First occurrence of any character or word in the string

SELECT CHARINDEX(@strSearch ,@String) AS[Result]

Answer : 1

2. Find LAST occurrence of any character or word in the string

In the given below example, you need to search for the last occurrence of word 'source' in the sentence.

SQL Query

DECLARE @String AS VARCHAR(100)

DECLARE @strSearch AS VARCHAR(100)


SET @String ='Get Set Solution is free source'

SET @strSearch ='source'


--Find Last occurrence of any character or word in the string

SELECT DATALENGTH(@String)-CHARINDEX(REVERSE(@strSearch),REVERSE(@String))-1 AS [Result]

Answer : 25

3. Find number of times occurred of any character in the string

In the given below example, you need to find count of occurrence of char 'e' in the sentence.

SQL Query

DECLARE @String AS VARCHAR(100)

DECLARE @strSearchChar AS VARCHAR(3)


SET @String ='Get Set Solution is free source'

SET @strSearchChar ='e'


--Find count occurrence of any character or word in the string

SELECT LEN(@String)-LEN(REPLACE(@String,@strSearchChar,'')) AS [Result]

Answer : 5 Times 'e' is occurred

4. Find number of times occurred of any word in the string

In the given below example, you need to find count of occurrence of word 'get' in the sentence.

SQL Query

DECLARE @String AS VARCHAR(100)

DECLARE @strSearch AS VARCHAR(3)


SET @String ='Get Set Solution is free source available and get easy access'

SET @strSearch ='get'


--Find count occurrence of any word in the string

SELECT (LEN(@String)-LEN(REPLACE(@String,@strSearch,''))) / LEN(@strSearch) AS [Result]

Answer : 2 Times 'get' is occurred