Início / dba / Perguntas / 156542
Accepted
GWR
Asked: 2016-11-29 06:13:58 +0800 CST

Cláusula WHERE com correspondência de padrão para localizar linhas com quaisquer caracteres que NÃO estejam em uma lista

  • 772

Estou em um ambiente SQL Server 2008.

Estou tentando usar a correspondência de padrões na WHEREcláusula para localizar linhas em que o valor de uma determinada coluna contém caracteres que não são alfanuméricos, sublinhado, traço, ponto ou espaço.

Este é o meu código e dados de amostra, mas não estou obtendo os resultados esperados.

Nos dados de exemplo, quero retornar as linhas 7, 8, 9 e 12, mas estou obtendo as linhas 5 e 6.

Se esta não for a melhor maneira de atingir o objetivo, estou aberto a ouvir outros métodos.

Não estou em um ambiente em que possa implementar regex, portanto, minha solução está limitada à funcionalidade pronta para uso.

CREATE TABLE PATTERN_TEST
(
ID INT NOT NULL,
STRING NVARCHAR(40) NOT NULL
)

INSERT INTO PATTERN_TEST
SELECT 1, 'string' UNION 
SELECT 2, 'STRING' UNION 
SELECT 3, 'string space' UNION 
SELECT 4, 'STRING SPACE' UNION 
SELECT 5, 'string-dash' UNION 
SELECT 6, 'string-dash space' UNION 
SELECT 7, 'string "otherchar"' UNION 
SELECT 8, 'string "other char"' UNION 
SELECT 9, '"string"' UNION 
SELECT 10, 'string_underscore' UNION 
SELECT 11, 'string_underscore space' UNION
SELECT 12, '"'
;

SELECT * FROM PATTERN_TEST WHERE STRING LIKE '%[^a-zA-Z0-9_ -.]%';
sql-server sql-server-2008

2 respostas

  1. Best Answer

    Isso também funcionará:

    SELECT * 
FROM PATTERN_TEST 
WHERE STRING LIKE '%[^a-zA-Z0-9_ .-]%' ;

    Testado em rextester.com

    A única diferença é que o traço ( -) é colocado no final do [...]padrão. Tem a ver com o significado especial do traço ( A-Zé interpretado como "qualquer caractere de Aa Z" ).

    Em seu padrão, os últimos três caracteres (espaço-traço-ponto: -.) são interpretados como "qualquer caractere de espaço ( ) a ponto ( .)" , e os resultados não são os desejados/esperados.

    Você também pode escapar do traço:

    WHERE STRING LIKE '%[^a-zA-Z0-9_ $-.]%' ESCAPE '$' ;

    Observe, porém, que o uso ESCAPE pode afetar adversamente a estimativa de cardinalidade.

    Veja a documentação do MSDN para alguns detalhes: LIKE:

    Se não houver nenhum caractere após um caractere de escape no padrão LIKE, o padrão não é válido e o LIKE retorna FALSE. Se o caractere após um caractere de escape não for um caractere curinga, o caractere de escape será descartado e o caractere após o escape será tratado como um caractere regular no padrão. Isso inclui os caracteres curinga de sinal de porcentagem (%), sublinhado (_) e colchete esquerdo ([) quando eles estão entre colchetes duplos ([ ]). Além disso, dentro dos caracteres de colchetes duplos ([ ]), caracteres de escape podem ser usados ​​e o cursor (^), hífen (-) e colchete direito (]) podem ser escapados.

    Para ser claro: caracteres especiais precisam escapar quando são usados ​​como não especiais. Dentro de [], os três (^, -, ]) são especiais. Colocar o traço no final é mais como um hack.

    • 9

  2. Sempre é uma pena estar em uma situação em que você não consegue aproveitar o conjunto completo de recursos da ferramenta que você precisa usar. CLRs são absolutamente a maneira de fazer isso corretamente.

    Se bem entendi, acho que você deve ser capaz de escrever uma função para retornar um conjunto nulo quando a string passada contiver um de seus caracteres inválidos e usá CROSS APPLY-la para remover resultados inválidos.

    CREATE FUNCTION dbo.RemoveInvalidString
(
    @String         NVARCHAR( 4000 )
) RETURNS TABLE AS RETURN
(
    WITH cte_InvalidCharList ( InvalidChar ) AS (
            SELECT  '"'
    UNION   SELECT  '-' )
    SELECT  IsClean = 1 
    FROM (  SELECT  1 ) dummyRecord ( dummy )
    WHERE   NOT EXISTS (SELECT  1
                        FROM    cte_InvalidCharList rt
                        WHERE   @String LIKE '%' + rt.InvalidChar + '%' )
);
GO

SELECT  pt.ID, pt.STRING
FROM    dbo.PATTERN_TEST pt
CROSS APPLY dbo.RemoveInvalidString ( pt.STRING );
    • 0

relate perguntas