SQL: To find consecutive repetitive characters in a string

Question

I am trying to find 4 or more consecutive repetitive characters in a column using SQL server. Any help on this would be much appreciated.

My data:

CompanyName
HSBC Inc
Barcccclays
AAAAA
Testtttt
Tesco Plc

My output should be as follows:

CompanyName
Barcccclays
AAAAA
Testtttt

Thanks in advance!

Answer 1

Yes it is possible, but I would not do it in SQL Server without Regex function:

CREATE TABLE tabC(CompanyName VARCHAR(100));

INSERT INTO tabC(CompanyName) 
    SELECT 'HSBC Inc' UNION ALL
    SELECT 'Barcccclays' UNION ALL
    SELECT 'AAAAA' UNION ALL
    SELECT 'Testtttt' UNION ALL
    SELECT 'Tesco Plc';

WITH mul AS
(
   SELECT REPLICATE(CHAR(32 + N), 4) AS val
   FROM (select top 95 row_number() over(order by t1.number) as N
         from   master..spt_values t1)  AS s
)
SELECT *
FROM tabC c
WHERE LEN(CompanyName) > 4
  AND EXISTS (SELECT 1
              FROM mul
              WHERE CHARINDEX(mul.val,c.CompanyName) > 0)

RextesterDemo

How it works:

1) Generate replicated values like 'aaaa', 'bbbb', 'cccc' and so on

2) Check if your string contains it.

Warning!

This solution checks only ASCII characters from 32 to 126.

EDIT:

How can incorporate above code like this: select @flag = 1 from tabc where 1 = (WITH mul AS ( SELECT REPLICATE(CHAR(32 + N), 4) AS val FROM (select top 95 row_number() over(order by t1.number) as N from master..spt_values t1) AS s ) SELECT * FROM tabC c WHERE LEN(CompanyName) > 4 AND EXISTS (SELECT 1 FROM mul WHERE CHARINDEX(mul.val,c.CompanyName) > 0)).

I'm getting this error:If this statement is a common table expression, or a change tracking context clause, the previous statement must be terminated with a semicolon. pls help

If you need to use it in context where you cannot use CTE change it to subquery.

SELECT *
FROM tabC c
WHERE LEN(CompanyName) > 4
  AND EXISTS (SELECT 1
              FROM (SELECT REPLICATE(CHAR(32 + N), 4) AS val
                    FROM (select top 95 row_number() over(order by t1.number) as N
                          from master..spt_values t1)  AS s) mul
              WHERE CHARINDEX(mul.val,c.CompanyName) > 0)

Answer 2

I would say the simplest solution would be to enable SQL CLR and implement your condition as user-defined function in .NET. There you have the power of Regular expressions to use (ex: the Regex class).

The regex you would need would be along the lines of (.)\1{3}, which matches any character followed by the same charcter at least 3 times.

For example the UDF could be written in C# like this:

using Microsoft.SqlServer.Server;
using System;
using System.Data.SqlTypes;
using System.Text.RegularExpressions;

public partial class UserDefinedFunctions
{
    [SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true, IsPrecise = true)]
    public static SqlBoolean ContainsRepeatingCharacters(SqlString input)
    {
        const String pattern = @"(.)\1{3}";     
        Boolean hasMatch = Regex.IsMatch(input.ToString(), pattern);
        return new SqlBoolean(hasMatch);
    }
}

You could then use it like any other UDF:

select companyName
from companies
where dbo.ContainsRepeatingCharacters(companyName) = 1

Implementing the same condition in pure TSQL is by all means possible just it would probably be a mess to read and maintain. For example, consider the brute force solution in this post and think what happens when the desired condition should be changed in future even just a bit more complex.

Answer 3

If you have a copy of NGrams8K handy this is a piece of cake. Both solutions below are simple, require no CLR integration, and will blow the doors off of any regex alternative with respect to performance.

-- sample data
DECLARE @table table 
(
  CompanyName varchar(100) 
  unique /* included to simulate an index on this column */
);
INSERT @table values('HSBC Inc'),('Barcccclays'),('AAAAA'),('Testtttt'),('Tesco Plc');

-- solution #1
SELECT companyname
FROM @table
CROSS APPLY dbo.ngrams8K(CompanyName,1)
WHERE charindex(replicate(token,4), CompanyName) > 0
GROUP BY CompanyName;

-- solution #2 (if you want to return a boolean)
SELECT companyname, has4ConsecChars = max(sign(charindex(replicate(token,4), CompanyName)))
FROM @table
CROSS APPLY dbo.ngrams8K(CompanyName,1)
GROUP BY CompanyName;

Results

companyname
---------------
AAAAA
Barcccclays
Testtttt   

companyname     has4ConsecChars
--------------- ---------------
AAAAA           1
Barcccclays     1
HSBC Inc        0
Tesco Plc       0
Testtttt        1