Padrão Regex dentro da function SQL Replace?

SELECT REPLACE('100.00 GB', '%^(^-?\d*\.{0,1}\d+$)%', ''); 

Eu quero replace qualquer marcação entre duas partes do número com regex acima, mas parece não funcionar. Eu não tenho certeza se é syntax regex que está errado porque eu tentei um mais simples, como '%[^0-9]%' apenas para testar, mas não funcionou também. Alguém sabe como posso conseguir isso?

Você pode usar o PATINDEX para encontrar o primeiro índice da ocorrência do padrão (string). Em seguida, use o STUFF para inserir outra string no padrão (string) correspondente.

Faça um loop em cada linha. Substitua cada caractere ilegal pelo que você deseja. No seu caso, substitua não numérico por branco. O loop interno é se você tiver mais de um caractere ilegal em uma célula atual do loop.

 DECLARE @counter int SET @counter = 0 WHILE(@counter < (SELECT MAX(ID_COLUMN) FROM Table)) BEGIN WHILE 1 = 1 BEGIN DECLARE @RetVal varchar(50) SET @RetVal = (SELECT Column = STUFF(Column, PATINDEX('%[^0-9.]%', Column),1, '') FROM Table WHERE ID_COLUMN = @counter) IF(@RetVal IS NOT NULL) UPDATE Table SET Column = @RetVal WHERE ID_COLUMN = @counter ELSE break END SET @counter = @counter + 1 END 

Cuidado: Isso é lento embora! Ter uma coluna varchar pode causar impacto. Então, usando LTRIM RTRIM pode ajudar um pouco. Independentemente disso, é lento.

O crédito vai para essa resposta do StackOverFlow.

EDIT Credit também vai para @srutzky

Edit (por @Tmdean) Em vez de fazer uma linha de cada vez, esta resposta pode ser adaptada para uma solução mais baseada em configurações. Ele ainda itera o número máximo de caracteres não numéricos em uma única linha, por isso não é o ideal, mas acho que deve ser aceitável na maioria das situações.

 WHILE 1 = 1 BEGIN WITH q AS (SELECT ID_Column, PATINDEX('%[^0-9.]%', Column) AS n FROM Table) UPDATE Table SET Column = STUFF(Column, qn, 1, '') FROM q WHERE Table.ID_Column = q.ID_Column AND qn != 0; IF @@ROWCOUNT = 0 BREAK; END; 

Você também pode melhorar bastante a eficiência se mantiver uma coluna de bits na tabela que indica se o campo já foi limpo. (NULL representa "Desconhecido" no meu exemplo e deve ser o padrão da coluna.)

 DECLARE @done bit = 0; WHILE @done = 0 BEGIN WITH q AS (SELECT ID_Column, PATINDEX('%[^0-9.]%', Column) AS n FROM Table WHERE COALESCE(Scrubbed_Column, 0) = 0) UPDATE Table SET Column = STUFF(Column, qn, 1, ''), Scrubbed_Column = 0 FROM q WHERE Table.ID_Column = q.ID_Column AND qn != 0; IF @@ROWCOUNT = 0 SET @done = 1; -- if Scrubbed_Column is still NULL, then the PATINDEX -- must have given 0 UPDATE table SET Scrubbed_Column = CASE WHEN Scrubbed_Column IS NULL THEN 1 ELSE NULLIF(Scrubbed_Column, 0) END; END; 

Se você não quiser alterar seu esquema, isso é fácil de adaptar para armazenar resultados intermediários em uma variável com valor de tabela que é aplicada à tabela real no final.

Em um sentido geral, o SQL Server não oferece suporte a expressões regulares e você não pode usá-las no código T-SQL nativo.

Você poderia escrever uma function CLR para fazer isso. Veja aqui , por exemplo.

Em vez de retirar o personagem encontrado por sua única posição, usar Replace(Column, BadFoundCharacter, '') poderia ser substancialmente mais rápido. Além disso, em vez de apenas replace o caractere ruim encontrado a seguir em cada coluna, isso substitui todos os encontrados.

 WHILE 1 = 1 BEGIN UPDATE dbo.YourTable SET Column = Replace(Column, Substring(Column, PatIndex('%[^0-9.-]%', Column), 1), '') WHERE Column LIKE '%[^0-9.-]%' If @@RowCount = 0 BREAK; END; 

Estou convencido de que isso funcionará melhor do que a resposta aceita, pelo menos porque faz menos operações. Existem outras maneiras que também podem ser mais rápidas, mas não tenho tempo para explorá-las agora.

Colocar a solução dentro de uma function SQL pode ser útil se você quiser reutilizá-la. Eu até estou fazendo isso no nível da célula, é por isso que estou colocando isso como uma resposta diferente:

 CREATE FUNCTION [dbo].[fnReplaceInvalidChars] (@string VARCHAR(300)) RETURNS VARCHAR(300) BEGIN DECLARE @str VARCHAR(300) = @string; DECLARE @Pattern VARCHAR (20) = '%[^a-zA-Z0-9]%'; DECLARE @Len INT; SELECT @Len = LEN(@String); WHILE @Len > 0 BEGIN SET @Len = @Len - 1; IF (PATINDEX(@Pattern,@str) > 0) BEGIN SELECT @str = STUFF(@str, PATINDEX(@Pattern,@str),1,''); END ELSE BEGIN BREAK; END END RETURN @str END 

Se você estiver fazendo isso apenas para um parâmetro que entra em um procedimento armazenado, você pode usar o seguinte:

 while PatIndex('%[^0-9]%', @Param) > 0 select @Param = Replace(@Param, Substring(@Param, PatIndex('%[^0-9]%', @Param), 1), '') 

Aqui está uma function recursiva que escrevi para realizar isso com base nas respostas anteriores.

 CREATE FUNCTION dbo.RecursiveReplace ( @P_String VARCHAR(MAX), @P_Pattern VARCHAR(MAX), @P_ReplaceString VARCHAR(MAX), @P_ReplaceLength INT = 1 ) RETURNS VARCHAR(MAX) BEGIN DECLARE @Index INT; -- Get starting point of pattern SET @Index = PATINDEX(@P_Pattern, @P_String); IF @Index > 0 BEGIN -- Perform the replace SET @P_String = STUFF(@P_String, PATINDEX(@P_Pattern, @P_String), @P_ReplaceLength, @P_ReplaceString); -- Recurse SET @P_String = dbo.RecursiveReplace(@P_String, @P_Pattern, @P_ReplaceString, @P_ReplaceLength); END; RETURN @P_String; END; 

Essência

Eu tropecei neste post procurando por algo mais, mas pensei em mencionar uma solução que uso que é muito mais eficiente – e realmente deveria ser a implementação padrão de qualquer function quando usada com uma consulta baseada em conjunto – que é usar uma cruz aplicada function de tabela. Parece que o tópico ainda está ativo, então espero que isso seja útil para alguém.

Exemplo de tempo de execução em algumas das respostas baseadas na execução de consultas baseadas em conjuntos recursivos ou function escalar, com base no conjunto de testes de linhas de 1m removendo os caracteres de um ID random, varia de 34s a 2m05s para os exemplos de loop WHILE e de 1m3s para { forever} para os exemplos de function.

Usar uma function de tabela com cross-apply atinge o mesmo objective em 10s . Você pode precisar ajustá-lo para atender às suas necessidades, como o comprimento máximo que ele lida.

Função:

 CREATE FUNCTION [dbo].[RemoveChars](@InputUnit VARCHAR(40)) RETURNS TABLE AS RETURN ( WITH Numbers_prep(Number) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ) ,Numbers(Number) AS ( SELECT TOP (ISNULL(LEN(@InputUnit),0)) row_number() OVER (ORDER BY (SELECT NULL)) FROM Numbers_prep a CROSS JOIN Numbers_prep b ) SELECT OutputUnit FROM ( SELECT substring(@InputUnit,Number,1) FROM Numbers WHERE substring(@InputUnit,Number,1) like '%[0-9]%' ORDER BY Number FOR XML PATH('') ) Sub(OutputUnit) ) 

Uso:

 UPDATE t SET column = o.OutputUnit FROM ##tt CROSS APPLY [dbo].[RemoveChars](t.column) o 

Eu acho que uma abordagem mais simples e rápida é iterada por cada caractere do alfabeto:

 DECLARE @i int SET @i = 0 WHILE(@i < 256) BEGIN IF char(@i) NOT IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '.') UPDATE Table SET Column = replace(Column, char(@i), '') SET @i = @i + 1 END