right - stuff sql

SQL convierte cualquier char a cero 0

Question

Así que estoy explorando una tabla donde encontré un par de campos que deberían ser numéricos, sin embargo, como la fuente es un archivo de Excel, las personas ya han mezclado letras, símbolos y números.

Estoy tratando de limpiar estos campos, pero puedo encontrar demasiados tipos de símbolos, así que actualmente realicé la limpieza con un par de casos como:

SELECT
    CASE 
       WHEN Montant_Devise LIKE ''%Free%'' THEN ''0''
       WHEN Montant_Devise LIKE '' '' THEN ''0''
       WHEN Montant_Devise = '''' THEN ''0'' 
       WHEN Montant_Devise = ''-'' THEN ''0'' 
       ELSE CAST(COALESCE(RTRIM(LTRIM(REPLACE(REPLACE(REPLACE(Montant_Devise, ''€'', ''''), '' '', ''''), '','', ''.''))), ''0'') AS DECIMAL(20,2))
    END
FROM
    [dbo].[table_BI]

Entonces, el trato aquí es que probablemente pueda encontrar nuevos símbolos en el futuro que evitar incluir los nuevos símbolos en mi caso. Me gustaría saber cómo podría reemplazar, no importa qué símbolo o letra sea el predeterminado 0 cero.

¿Sabes chicos cómo podría hacer esto?

Gracias

Answer 1

Empezar con

CASE WHEN patindex(Montant_Devise  ''%[^0-9]%'' > 0 ''0''

Luego haz la cadena vacía. Entonces haz tu otro.

Answer 2

No veo una manera de hacer esto sin una función, que va a matar en el rendimiento. Dicho esto, hay muchos por ahí. Aqui hay uno.

CREATE Function [fnRemoveNonNumericCharacters](@strText VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
    WHILE PATINDEX(''%[^0-9]%'', @strText) > 0
    BEGIN
        SET @strText = STUFF(@strText, PATINDEX(''%[^0-9]%'', @strText), 1, ''0'')
    END
    RETURN @strText
END

La ejecución

declare @table table (x varchar(64) )
insert into @table
values
(''332148790832473487...DFSKDJFH&#@!@#''),
(''0X0C0V1234'')

update t
set x = dbo.RemoveNonNumericCharacters(t.x)
from @table t

select * from @table

DEVOLUCIONES

x
33214879083247348700000000000000000
0000001234

Alternativamente, en la función puede simplemente eliminar lo no numérico cambiando

STUFF(@strText, PATINDEX(''%[^0-9]%'', @strText), 1, ''0'')

a...

STUFF(@strText, PATINDEX(''%[^0-9]%'', @strText), 1, '''')