CODEWITHSUNDEEP
sqlsql-serverregex
Hooman Nemati
Hooman Nemati

Reputation: 97

sql server: finding names with repetitive char

I want to find names with repetitive char in them for example :

I found a similar question here but they were consecutive ones.

I know 2 way :

1.using helping table and join

2.a very long "where like" statement

is there any way to do it with regex?

Upvotes: 0

Views: 235

Answers (1)

Sreenu131
Sreenu131

Reputation: 2516

This codes will helps you find the repetitive characters find in the word and count of characters

Sample Data

    DECLARE @Table TABLE (ID INT,String nvarchar(100))
    INSERT INTO @Table
    SELECT 1,'ana'          UNION ALL
    SELECT 2,'asdbbiop'     UNION ALL
    SELECT 3,'a1for1'       UNION ALL
    SELECT 4,'@mail@ban'

    SELECT  * FROM @Table

Using Recursive Cte we get the expected Result

;With cte
AS
(
SELECT ID, String
        ,CAST(LEFT(String,1)AS VARCHAR(10)) AS Letter
        ,RIGHT(String,LEN(String)-1)  AS Remainder 
FROM @Table
WHERE LEN(String)>1

UNION ALL
SELECT ID, String
        ,CAST(LEFT(Remainder,1)AS VARCHAR(10)) AS Letter
        ,RIGHT(Remainder,LEN(Remainder)-1)  AS Remainder 
FROM cte
WHERE LEn(Remainder)>0
)
SELECT ID,
       String, 
       letter, 
       Ascii(letter) AS CharCode, 
       Count(letter) AS CountOfLetter 
FROM   cte 
GROUP  BY ID,
          String, 
          letter, 
          Ascii(letter) 
HAVING COUNT(letter)>1

Result

ID  String  letter  CharCode    CountOfLetter
---------------------------------------------
1   ana         a     97          2
2   asdbbiop    b     98          2
3   a1for1      1     49          2
4   @mail@ban   @     64          2
4   @mail@ban   a     97          2

Upvotes: 1

Related Questions