Início / dba / Perguntas / 126127
Accepted
Stackoverflowuser
Asked: 2016-01-14 07:33:54 +0800 CST

Substituir caracteres especiais em uma coluna por espaço

  • 772

Estou tentando escrever uma consulta que substitui os caracteres especiais por espaço. O código abaixo ajuda a identificar as linhas. (caracteres alfanuméricos, vírgula e espaço são válidos):

SELECT columnA
FROM tableA
WHERE columnA like '%[^a-Z0-9, ]%'

Como posso integrar a função replace na instrução select para que todos os caracteres que não sejam alfanuméricos, vírgula e espaço no conjunto de resultados sejam substituídos por ' ' (espaço). Este não vai funcionar:

SELECT replace(columnA,'%[^a-Z0-9, ]%',' ')
FROM tableA
WHERE columnA like '%[^a-Z0-9, ]%'
sql-server sql-server-2008-r2

3 respostas

  1. Best Answer

    Se você tiver a garantia de usar apenas as 26 letras do alfabeto inglês dos EUA (versões maiúsculas e minúsculas), com certeza, você pode usar LIKEe/ou PATINDEXcom a notação de intervalo simples de [a-z](você não usaria precisa usar um "Z" maiúsculo ao usar um agrupamento que não diferencia maiúsculas de minúsculas).

    Mas, se você pode obter caracteres não encontrados no alfabeto en-US, mas disponíveis em várias páginas de código / agrupamentos para VARCHARdados (por exemplo Þ, maiúsculas latinas "Thorn" = SELECT CHAR(0xDE)), talvez seja necessário incluí-los na classe de caracteres: [a-z0-9, Þ]. Claro, o que esses caracteres extras seriam é por página de código.

    Além disso, esteja ciente de que tanto o tipo de agrupamento (SQL Server vs Windows) quanto as configurações de sensibilidade (maiúsculas, acento, etc. sensível vs insensível) afetarão quais caracteres são incluídos em um intervalo específico. Por exemplo, os agrupamentos do SQL Server classificam letras maiúsculas e minúsculas na ordem oposta aos agrupamentos do Windows. Ou seja, assumindo um Collation que diferencia maiúsculas de minúsculas para ambos os tipos de Collations, um fará AaBb...e o outro fará aAbB.... O efeito será que aestará dentro do alcance de A-Zpara um deles, mas não para o outro. E o intervalo de a-Znão corresponderá a nenhum caractere em um Collation binário (um que termine em _BINou _BIN2, mas não use _BIN), dado que o valor de Aé 65 eaé 97, portanto, é um intervalo inválido de 97 a 65 ;-). Existem muitas variações para dar exemplos aqui, então tentarei postar uma explicação detalhada no meu blog em breve (e atualizarei isso com o link para ele). No entanto, se você for rigoroso em aceitar apenas caracteres do inglês americano (mesmo que receba letras válidas de outros idiomas), sua melhor opção provavelmente será usar o seguinte padrão e agrupamento:

    LIKE '%[^A-Za-z0-9, ]%' COLLATE Latin1_General_100_BIN2

    Agora, se você estiver dando suporte NVARCHARa dados e puder obter caracteres de "palavra" de vários idiomas, o T-SQL não será de muita ajuda, pois não há uma maneira real de diferenciar essas coisas. Nesse caso, você deve usar uma Expressão Regular (RegEx) -- especificamente o Replacemétodo/função -- e elas só estão disponíveis através do SQLCLR. O seguinte mostra um exemplo de substituição de vários caracteres "especiais", mas deixando todos os que são letras válidas em pelo menos um idioma:

    DECLARE @Test NVARCHAR(500);
SET @Test = N'this$is%a<>TEST,;to}⌡↕strip╞╟╚══¶out_ç_ƒ▀ special-ij-೫-chars-舛-დ-א-B';
SELECT SQL#.RegEx_Replace4k(@Test, N'[\W\p{Pc}-[,]]', N' ', -1, 1, NULL);

    Devoluções:

    this is a  TEST, to   strip      out ç ƒ  special ij ೫ chars 舛 დ א B

    A expressão RegEx significa:

    • \W= um RegEx "escape" que significa "qualquer caractere que não seja de palavra"
    • \p{Pc}= uma "categoria" Unicode de "Pontuação, Conector" (isso é necessário para a correspondência apenas porque essa "categoria" é especificamente excluída pelo \Wescape)
    • -[,]= subtração de classe (isso é necessário para excluir vírgulas da correspondência como "especial", pois elas são incluídas no \Wescape)

    Você pode fazer uma atualização de uma tabela simplesmente emitindo:

    UPDATE tbl
SET    tbl.field = SQL#.RegEx_Replace4k(tbl.field, N'[\W\p{Pc}-[,]]', N' ', -1, 1, NULL)
FROM   tbl
WHERE  SQL#.RegEx_IsMatch4k(tbl.field, N'[\W\p{Pc}-[,]]', 1, NULL) = 1;

    Observe que, para esses exemplos, usei duas funções disponíveis na versão gratuita da biblioteca SQL# de funções SQLCLR, que criei (mas, novamente, elas são gratuitas). Observe também que usei as versões "4k" que são mais rápidas devido ao uso NVARCHAR(4000)em vez de NVARCHAR(MAX)tipos de parâmetro. Se seus dados estiverem usando NVARCHAR(MAX), basta remover o "4k" dos nomes das funções.

    Veja também:

    • 11

  2. Eu tenho um post aqui que faz algo semelhante .

    Basicamente, estou usando um CTE recursivo para fazer um loop repetidamente substituindo um caractere "ruim" de cada vez. Estou usando STUFF para retirar 1 caractere (embora você possa usá-lo para substituir por um espaço) e PATINDEX para encontrar a localização do caractere que quero remover. Você pode modificá-lo um pouco para fazer o que está procurando. No entanto, cria uma lista "boa", na verdade não atualiza a lista existente.

    DECLARE @Pattern varchar(50) = '%[^A-Za-z0-9, ]%';

WITH FixBadChars AS (SELECT StringToFix, StringToFix AS FixedString, 1 AS MyCounter, Id
                FROM BadStringList
                UNION ALL
                SELECT StringToFix, Stuff(FixedString, PatIndex(@Pattern, 
                    FixedString COLLATE Latin1_General_BIN2), 1, ' ') AS FixedString, 
                    MyCounter + 1, Id
                FROM FixBadChars
                WHERE FixedString COLLATE Latin1_General_BIN2 LIKE @Pattern)
SELECT StringToFix, FixedString, MyCounter, Id
FROM FixBadChars
WHERE MyCounter = 
        (SELECT MAX(MyCounter) 
        FROM FixBadChars Fixed
        WHERE Fixed.Id = FixBadChars.Id)
OPTION (MAXRECURSION 1000);

    Você deve poder modificar a parte inferior para fazer uma atualização em vez de apenas uma consulta, mas eu realmente não tentei. Tenho quase certeza que seria algo assim:

    UPDATE FixBadChars
SET StringToFix = FixedString
WHERE MyCounter = 
        (SELECT MAX(MyCounter) 
        FROM FixBadChars Fixed
        WHERE Fixed.Id = FixBadChars.Id)
OPTION (MAXRECURSION 1000);

    No que diz respeito à escalabilidade, retornei ~ 170k linhas limpas em menos de 30 segundos. Novamente, não tenho certeza sobre como fazer uma atualização, mas isso estava no meu laptop, que é bastante lento, com apenas 6 GB de RAM.

    • 5 
  3. Declare @String nchar(2000)='hg$%^AB,.:23ab-=+'

Declare @NewString VARCHAR(2000)=''
Declare @Lenght int=LEN(@String)
Declare @Index int=1

WHILE (@Index <= @Lenght)
BEGIN
    Declare @Letter nchar(1)=Substring(@String,@Index,1);
    Declare @ASCII int=ASCII(@Letter);
    If((@ASCII >= 48 and @ASCII <= 57) or (@ASCII >= 97 and @ASCII <= 122) or (@ASCII >= 65 and @ASCII <= 90))
    BEGIN
        SET @NewString += @Letter
    END
    ELSE
    BEGIN
        SET @NewString += ' '
    END
    SET @Index+=1

END
Select @NewString
    • 0

relate perguntas