Блог

Моя база данных-SQL Server. У меня есть поле с номером телефона. Я должен вернуть НЕДОПУСТИМОЕ значение, если номер телефона содержит одну и ту же цифру, повторяющуюся более 7 раз? Например (401) 2510897 —> Допустимо. (401) 4444444 —-> Недействительный.

ВРОДЕ бы справляется со своей работой…

 SELECT
    Telephone,
    CASE
        WHEN Telephone LIKE '%0%0%0%0%0%0%0%' THEN 'Invalid'
        WHEN Telephone LIKE '%1%1%1%1%1%1%1%' THEN 'Invalid'
        WHEN Telephone LIKE '%2%2%2%2%2%2%2%' THEN 'Invalid'
        WHEN Telephone LIKE '%3%3%3%3%3%3%3%' THEN 'Invalid'
        WHEN Telephone LIKE '%4%4%4%4%4%4%4%' THEN 'Invalid'
        WHEN Telephone LIKE '%5%5%5%5%5%5%5%' THEN 'Invalid'
        WHEN Telephone LIKE '%6%6%6%6%6%6%6%' THEN 'Invalid'
        WHEN Telephone LIKE '%7%7%7%7%7%7%7%' THEN 'Invalid'
        WHEN Telephone LIKE '%8%8%8%8%8%8%8%' THEN 'Invalid'
        WHEN Telephone LIKE '%9%9%9%9%9%9%9%' THEN 'Invalid'
        ELSE 'Valid'
    END
FROM
    MyTable
 

или используйте REPLICATE, чтобы было понятнее, что должно быть менее 7 совпадений…

 SELECT
    Telephone,
    CASE
        WHEN Telephone LIKE '%'   REPLICATE('0%', 7) THEN 'Invalid'
        WHEN Telephone LIKE '%'   REPLICATE('1%', 7) THEN 'Invalid'
        WHEN Telephone LIKE '%'   REPLICATE('2%', 7) THEN 'Invalid'
        WHEN Telephone LIKE '%'   REPLICATE('3%', 7) THEN 'Invalid'
        WHEN Telephone LIKE '%'   REPLICATE('4%', 7) THEN 'Invalid'
        WHEN Telephone LIKE '%'   REPLICATE('5%', 7) THEN 'Invalid'
        WHEN Telephone LIKE '%'   REPLICATE('6%', 7) THEN 'Invalid'
        WHEN Telephone LIKE '%'   REPLICATE('7%', 7) THEN 'Invalid'
        WHEN Telephone LIKE '%'   REPLICATE('8%', 7) THEN 'Invalid'
        WHEN Telephone LIKE '%'   REPLICATE('9%', 7) THEN 'Invalid'
        ELSE 'Valid'
    END
FROM
    MyTable
 

Настоятельно рекомендуется включить это требование в программу для работы с clr или использовать его. Если вам необходимо использовать t-sql, вы можете сделать это следующим образом:

 -- Auxiliary function, this function is very commonly used and can be left in the database
CREATE FUNCTION [dbo].[SplitByTrunk](@str varchar(max), @len int = 1)
RETURNS TABLE
AS
RETURN
(
    with c0 as(
    select @str as s
    ), c1 as(
    select number 1 as n from master.dbo.spt_values where type = 'P' and number >= 0 and number%@len = 0
    )
    select ROW_NUMBER() over(order by c1.n) as pos
           , SUBSTRING(c0.s, c1.n, @len) as item
    from c0
    inner join c1 on c1.n <= LEN(c0.s)
)

-- Then
create table #temp(num varchar(32))
insert into #temp(num) values('(401) 2510897'), ('(401) 4444444')

select num
       , (case when (select MAX(cnt)
                     from(select COUNT(*) as cnt from [dbo].[SplitByTrunk](num, 1) group by item) as d) < 7
               then 'Valid.'
          else 'Invalid.'
          end) as isvalid
from #temp
 

Значения master.dbo.spt_ в функции SplitByTrunk, если числовая последовательность недостаточно длинная, вместо этого вы можете использовать следующую функцию (эта функция также очень часто используется и может быть сохранена в базе данных)

 CREATE FUNCTION [dbo].[GetNums](@low AS BIGINT, @high AS BIGINT) RETURNS TABLE
AS
RETURN
    WITH
        L0   AS (SELECT c FROM (VALUES(1),(1)) AS D(c)),
        L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
        L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
        L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
        L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
        L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
        Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum
                 FROM L5)
SELECT @low   rownum - 1 AS n
FROM Nums
ORDER BY rownum
OFFSET 0 ROWS FETCH FIRST @high - @low   1 ROWS ONLY;
 

Вы можете решить эту проблему с помощью некоторых оконных функций и рекурсивного cte, но — в зависимости от количества записей — производительность, я думаю, будет не такой хорошей:

 DECLARE @t TABLE(
  TelID int,
  TelNo VARCHAR(50)
)

INSERT INTO @t VALUES
  (1, '(401) 4444444')
 ,(2, '(401) 4444344')

;WITH cteTelPos AS(
SELECT TelID, TelNo, 1 id, LEFT(TelNo, 1) AS TelPos, RIGHT(TelNo, LEN(TelNo)-1) AS tel
  FROM @t
UNION ALL
SELECT TelID, TelNo, id 1, LEFT(tel, 1), RIGHT(tel, LEN(tel)-1)
  FROM cteTelPos
  WHERE LEN(tel) >= 1
),
cteFilterGrp AS(
SELECT TelID, TelNo, id, TelPos
      ,(id - DENSE_RANK() OVER (PARTITION BY TelPos ORDER BY id)) * RANK() OVER (ORDER BY TelPos) AS grp
  FROM cteTelPos
),
cteInval AS(
SELECT TelID, TelNo, TelPos, Grp, COUNT(*) cnt
  FROM cteFilterGrp
  GROUP BY TelID, TelNo, TelPos, Grp
  HAVING COUNT(*) >= 7
),
cteInvalSrt AS(
SELECT TelID, TelNo, TelPos, Grp, cnt, ROW_NUMBER() OVER (ORDER BY cnt DESC) AS rn
  FROM cteInval
)
SELECT t.*, CASE WHEN s.cnt IS NULL THEN N'Valid' ELSE N'Invalid' END AS TelStatus
  FROM @t AS t
  LEFT JOIN cteInvalSrt s ON s.TelID = t.TelID AND s.rn = 1