Início / coding / Perguntas / 79556216
Accepted
SK-USA
Asked: 2025-04-05 04:41:57 +0800 CST

SQL Server RegEx [duplicado]

  • 772

Preciso corresponder a uma string contendo pelo menos 6 dígitos. Estou tendo um problema com isso.

Este exemplo ajudará:

IF OBJECT_iD('tempdb..#Sample') IS NOT NULL DROP TABLE #Sample
CREATE TABLE #Sample(Field VARCHAR(50))
GO

INSERT INTO #Sample (Field) VALUES ('ABC123 ')
INSERT INTO #Sample (Field) VALUES ('ABC1')
INSERT INTO #Sample (Field) VALUES ('ABC1')
INSERT INTO #Sample (Field) VALUES ('ABC123.')
INSERT INTO #Sample (Field) VALUES ('ABC123&')
INSERT INTO #Sample (Field) VALUES ('ABC123&')
INSERT INTO #Sample (Field) VALUES ('ABC1234567&')
INSERT INTO #Sample (Field) VALUES ('ABC12345&')
INSERT INTO #Sample (Field) VALUES ('DataMigrationLog_Bak3')

DECLARE @PAT VARCHAR(500) 

SELECT '%[^a-z0-9 .]%' PAT, * FROM #Sample WHERE Field LIKE '%[^a-z0-9 .]%'

SELECT @PAT = '%[(\d{6})]%'
SELECT @PAT PATVAR, * FROM #Sample WHERE Field LIKE @PAT

SELECT '%[(\d{3})]%' PAT, * FROM #Sample WHERE Field LIKE '%[(\d{3})]%'

Ao usar este padrão - %[(\d{6})]%Ele dá duas linhas

ABC1234567&
DataMigrationLog_Bak3

O que estou perdendo aqui?

sql-server

1 respostas

  1. Best Answer

    Verifique a seguinte solução com base na TRANSLATE()função.

    Somente o SQL Server 2025 começará a oferecer suporte ao RegEx real.

    SQL

    -- DDL and sample data population, start
DECLARE @tbl TABLE (col VARCHAR(100));
INSERT @tbl (col) VALUES
('ABC123 '),
('ABC1'),
('ABC1'),
('ABC123.'),
('ABC123&'),
('ABC123&'),
('ABC1234567&'),
('ABC12345&'),
('DataMigrationLog_Bak3');
-- DDL and sample data population, end

-- SQL Server 2017 onwards
;WITH cte AS 
(
   SELECT col
      , REPLACE(TRANSLATE(col, '0123456789', SPACE(10)),' ','') AS JunkCharacters
   FROM @tbl
)
SELECT col, JunkCharacters
   , REPLACE(TRANSLATE(col, TRIM(JunkCharacters), SPACE(LEN(TRIM(JunkCharacters)))),' ','') AS CleansedDigits
   , DATALENGTH(REPLACE(TRANSLATE(col, TRIM(JunkCharacters), SPACE(LEN(TRIM(JunkCharacters)))),' ','')) AS Digit_Counter
FROM cte;

    Saída

    cor Personagens Junk Dígitos Limpos Contador_de_dígitos
    ABC123 abc 123 3
    ABC1 abc 1 1
    ABC1 abc 1 1
    ABC123. ABC. 123 3
    ABC123& ABC& 123 3
    ABC123& ABC& 123 3
    ABC1234567& ABC& 1234567 7
    ABC12345& ABC& 12345 5
    Log de migração de dados_Bak3 Log de migração de dados_Bak 3 1
    • 2

relate perguntas