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.

Solarwinds

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

If there is single word to find in the character, both the answer will be same. Here is the example of the same.

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

This will return following resultset.

CntReplacedChars     CntOccuranceChars
-------------------- --------------------
3                    3

I hope using this hint along with recursive CTE you will be able to solve the T-SQL Challenge. Additionally, make sure that your strings does not have leading or trailing empty spaces. If you have it, you may want to use LTRIM or RTRIM functions.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
SQLAuthority News – Bookmark Link for Sync Framework for SQL Azure
Next Post
SQLAuthority News – Speaking Online at Virtual Techdays – Aug 18, 2010 – Spatial Datatypes

Related Posts

42 Comments. Leave new

  • So with your example with the word ‘long’. Is there a way to pull out the word ‘long’ in a select statement for each time that it occurs? Perhaps separated by a column in the record? so the result would be ‘long’, ‘long’?

    Reply

Leave a Reply

Menu