SQL:查找字符串中连续的重复字符

Question

我正在尝试使用 SQL Server 在列中查找 4 个或更多连续的重复字符。任何对此的帮助将不胜感激。

我的数据:

CompanyName
HSBC Inc
Barcccclays
AAAAA
Testtttt
Tesco Plc

我的输出应该如下:

CompanyName
Barcccclays
AAAAA
Testtttt

提前致谢!

Answer 1

是的，这是可能的，但如果没有 Regex 函数，我不会在 SQL Server 中这样做:

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

它是如何工作的:

1) 生成复制值，如“aaaa”、“bbbb”、“cccc”等

2) 检查您的字符串是否包含它。

警告!

此解决方案仅检查 32 到 126 之间的 ASCII 字符。

编辑:

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

如果您需要在无法使用CTE的上下文中使用它，请将其更改为子查询。

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)