T-SQL How to get all records where a specific char is repeated consecutively?

Question

I'm looking a way to get all records where the word column consists entirely of a single letter repeated 2 or more times

Example: In a table I have something like this:

id  word
1   house
2   dog
3   aaaa
4   eee
5   aaaaaa
6   car
7   aa
8   hook
9   arrow
10  oooooo
11  feel
12  tooth

I would like a way to do a query on that table and get only the ids: 3,4,5,7,10

I guess this is possible with an regular expression

Similar one, please refer to below link https://stackoverflow.com/questions/14574990/finding-strings-with-duplicate-letters-inside — KoP, Mar 02 '18 at 19:36

Martin Smith · Accepted Answer · 2018-03-02T20:02:02.653

5

Here's one way.

SELECT *
FROM   YourTable
WHERE Word = REPLICATE(LEFT(Word,1),LEN(Word)) AND LEN(Word) >= 2

edited Mar 02 '18 at 20:02

answered Mar 02 '18 at 19:46

Martin Smith

438,706
87
741
845

Thank you for your answer but I need it to be able to identify only consecutive repeated chars. But this code is very interesting! – Andrés Castrillo Mar 02 '18 at 19:49
What makes you think this doesn't identify consecutive repeated characters? It returns `3`, `4`, `5` and `7` as requested in your question. – Martin Smith Mar 02 '18 at 19:50
you are right, it does return them, but what i need is that ONLY returns the consecutive. I think i did make myself clear in my question, sorry – Andrés Castrillo Mar 02 '18 at 19:52
This does return **only** the consecutive. Your question asks for `get only the ids: 3,4,5 and 7` and that is exactly what this returns. Demo http://rextester.com/YAZUY36379 – Martin Smith Mar 02 '18 at 19:54
ok - now your edit makes things clearer - this is easier - and for the record your question was not clear. I have edited it to clarify it based on the example data. – Martin Smith Mar 02 '18 at 20:06
@MartinSmith... what if we use of soundex() function that it would not find the correct result ?? – Yogesh Sharma Mar 05 '18 at 06:54
@YogeshSharma - I doubt that it would. The `SOUNDEX` function was designed for categorising American surnames in census data IIRC - not this kind of task. – Martin Smith Mar 05 '18 at 07:22
@MartinSmith... yes i can see The letters A, E, I, O, U, H, W, and Y are ignored – Yogesh Sharma Mar 05 '18 at 07:44
@MartinSmith Yes that's why i edited. I figured out that it wasnt clear enough – Andrés Castrillo Mar 05 '18 at 19:50
1

@MartinSmith Thank u so much! this works just fine! It is exactly what i was looking for. – Andrés Castrillo Mar 05 '18 at 19:56

Pawan Kumar · Answer 2 · 2018-03-03T07:11:16.863

Please use this. Set Based Approach

;WITH SingleDigits(Number) AS
(
    SELECT Number
    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8),
    (9), (0)) AS X(Number)
)
,Series AS
(
    SELECT (d1.Number+1) + (10*d2.Number) + (100*d3.Number) + (1000*d4.Number) Number
    from
    SingleDigits as d1,
    SingleDigits as d2,
    SingleDigits as d3,
    SingleDigits as d4
)
SELECT Id,Word
FROM
(
    SELECT Id,word,u,COUNT(*) OVER(PARTITION BY ID) u1
    FROM
    (
        SELECT Id,word,Charx,DENSE_RANK() OVER(ORDER BY Id,Word,Charx) u    
        FROM Wrds N
        CROSS APPLY
        (
            SELECT SUBSTRING(word,number,1) Charx , number
            FROM
            ( 
                SELECT DISTINCT number FROM
                Series WHERE number > 0 AND number <= DATALENGTH(N.word)
            )v  
        )t
    )z 
    GROUP BY Id,word,u
)x WHERE u1 = 1

OUTPUT

Id          Word
----------- ----------
3           aaaa
4           eee
5           aaaaaa
7           aa
10          oooooo

(5 rows affected)

T-SQL How to get all records where a specific char is repeated consecutively?

2 Answers2