SQL SERVER – Finding the Occurrence of Character in String

This article is written in response to provide hint to TSQL Beginners Challenge 14. The challenge is about counting the number of occurrences of characters in the string. Here is quick method how you can count occurrence of character in any string.

Here is quick example which provides you two different details.

How many times the character/word exists in string?
How many total characters exists in Occurrence?
Let us see following example and it will clearly explain it to you.

DECLARE @LongSentence VARCHAR(MAX)
DECLARE @FindSubString VARCHAR(MAX)
SET @LongSentence = 'My Super Long String With Long Words'
SET @FindSubString = 'long'
SELECT (LEN(@LongSentence) - LEN(REPLACE(@LongSentence, @FindSubString, ''))) CntReplacedChars,
(LEN(@LongSentence) - LEN(REPLACE(@LongSentence, @FindSubString, '')))/LEN(@FindSubString) CntOccuranceChars

This will return following resultset.

CntReplacedChars     CntOccuranceChars
-------------------- --------------------
2                    2

Read more: Journey to SQL Authority with Pinal Dave

Posted via email from .NET Info