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