How to Find Character Repeated in a String Few Times using CTE?

Introduction

This tip shows how to find single character repeated in a string few times by using Common Table Expressions (CTE).

Why CTE?

CTE is recursive query, which returns a temporary result set. Using CTE, you're able to get string from field and to work with it as long as is needed.

Sample Query

--declare variable, type: table
DECLARE @MyTable TABLE (Input NVARCHAR(30))

--insert sample values
INSERT INTO @MyTable (Input)
VALUES('Abracadabra'), ('Hocus Pocus'), ('Korona Kielce Królem'), ('Chamba Wamba'), ('Vinietai'), ('Corozo')

--here CTE begins: 
;WITH CTE AS
(
    --initial query
    SELECT Input, CONVERT(VARCHAR(1),LEFT(Input,1)) AS Letter, RIGHT(Input, LEN(Input)-1) AS Remainder
    FROM @MyTable
    WHERE LEN(Input)>1
    --recursive part
    UNION ALL
    --recursive query
    SELECT Input, CONVERT(VARCHAR(1),LEFT(Remainder,1)) AS Letter, _
        RIGHT(Remainder, LEN(Remainder)-1) AS Remainder
    FROM CTE
    WHERE LEN(Remainder)>0
)
SELECT Input, Letter, ASCII(Letter) AS CharCode, COUNT(Letter) AS CountOfLetter
FROM CTE
GROUP BY Input, Letter, ASCII(Letter)
HAVING COUNT(Letter)>2

A Brief Description of Sample Query...

...in other words: what the above query does?

Initial query gets 3 columns:

1. Input field as a first result column. This column is not necessary, but has been placed to display input data,
2. Letter columns is a result of LEFT[^] function. It gets only one character.
3. Remainder field is a result of RIGHT[^] function. It gets the string from Input field without first letter.

This query returns:

Abracadabra                    A    bracadabra
Hocus Pocus                    H    ocus Pocus
Korona Kielce Królem           K    orona Kielce Królem
Chamba Wamba                   C    hamba Wamba
Vinietai                       V    inietai
Corozo                         C    orozo

The magic is in the recursive part!

Recursive query goes through the result set of initial query(for the first time) and itself till the length of Remainder field is bigger than one.

Based on "first" record: Abracadabra, see how query process through the Remainder string in each loop:

--first loop of first recursive query
Abracadabra    b    racadabra
--second loop
Abracadabra    r    acadabra
--third loop
Abracadabra    a    cadabra
--and so on
Abracadabra    c    adabra
Abracadabra    a    dabra
Abracadabra    d    abra
Abracadabra    a    bra
Abracadabra    b    ra
Abracadabra    r    a
--last loop
Abracadabra    a    

When each character in Input field has been split into rows, you're able to count it by using aggregate functions[^]. See the last SELECT statement.

SELECT Input, Letter, ASCII(Letter) AS CharCode, COUNT(Letter) AS CountOfLetter
FROM CTE
GROUP BY Input, Letter, ASCII(Letter)
HAVING COUNT(Letter)>2

For further information, please see:

• COUNT[^]
• HAVING[^]

Final Note

I hope you learn something interesting by reading this tip. Feel free to change CTE to your needs.

History

• 2014-12-09 - Query has been improved
• 2014-12-06 - Initial version