una - Cómo reemplazar múltiples caracteres en SQL?

sql eliminar caracteres de una cadena

Question

Esto se basa en una pregunta similar ¿ Cómo reemplazar varios caracteres en Access SQL?

Escribí esto porque SQL Server 2005 parece tener un límite en la función replace () para 19 reemplazos dentro de una cláusula where.

Tengo la siguiente tarea: Necesito realizar una coincidencia en una columna, y para mejorar las posibilidades de una coincidencia eliminando múltiples caracteres innecesarios usando la función de reemplazo ()

DECLARE @es NVarChar(1) SET @es = ''''
DECLARE @p0 NVarChar(1) SET @p0 = ''!''
DECLARE @p1 NVarChar(1) SET @p1 = ''@''
---etc...

SELECT *
FROM t1,t2 
WHERE  REPLACE(REPLACE(t1.stringkey,@p0, @es), @p1, @es) 
     = REPLACE(REPLACE(t2.stringkey,@p0, @es), @p1, @es)    
---etc 

Si hay> 19 REPLACE () en esa cláusula where, no funciona. Así que la solución que se me ocurrió es crear una función sql llamada trimChars en este ejemplo (disculpe que comienzan en @ 22

CREATE FUNCTION [trimChars] (
   @string varchar(max)
) 

RETURNS varchar(max) 
AS
BEGIN

DECLARE @es NVarChar(1) SET @es = ''''
DECLARE @p22 NVarChar(1) SET @p22 = ''^''
DECLARE @p23 NVarChar(1) SET @p23 = ''&''
DECLARE @p24 NVarChar(1) SET @p24 = ''*''
DECLARE @p25 NVarChar(1) SET @p25 = ''(''
DECLARE @p26 NVarChar(1) SET @p26 = ''_''
DECLARE @p27 NVarChar(1) SET @p27 = '')''
DECLARE @p28 NVarChar(1) SET @p28 = ''`''
DECLARE @p29 NVarChar(1) SET @p29 = ''~''
DECLARE @p30 NVarChar(1) SET @p30 = ''{''

DECLARE @p31 NVarChar(1) SET @p31 = ''}''
DECLARE @p32 NVarChar(1) SET @p32 = '' ''
DECLARE @p33 NVarChar(1) SET @p33 = ''[''
DECLARE @p34 NVarChar(1) SET @p34 = ''?''
DECLARE @p35 NVarChar(1) SET @p35 = '']''
DECLARE @p36 NVarChar(1) SET @p36 = ''/'
DECLARE @p37 NVarChar(1) SET @p37 = ''|''
DECLARE @p38 NVarChar(1) SET @p38 = ''<''
DECLARE @p39 NVarChar(1) SET @p39 = ''>''
DECLARE @p40 NVarChar(1) SET @p40 = ''@''
DECLARE @p41 NVarChar(1) SET @p41 = ''-''

return   REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
       @string, @p22, @es), @p23, @es), @p24, @es), @p25, @es), @p26, @es), @p27, @es), @p28, @es), @p29, @es), @p30, @es), @p31, @es), @p32, @es), @p33, @es), @p34, @es), @p35, @es), @p36, @es), @p37, @es), @p38, @es), @p39, @es), @p40, @es), @p41, @es)
END 

Esto se puede usar además de las otras cadenas de reemplazo

SELECT *
FROM t1,t2 
WHERE  trimChars(REPLACE(REPLACE(t1.stringkey,@p0, @es), @p1, @es) 
         = REPLACE(REPLACE(t2.stringkey,@p0, @es), @p1, @es))   

Creé algunas funciones más para hacer un reemplazo similar, como trimChars (trimMoreChars (

SELECT *
FROM t1,t2 
WHERE  trimChars(trimMoreChars(REPLACE(REPLACE(t1.stringkey,@p0, @es), @p1, @es) 
         = REPLACE(REPLACE(t2.stringkey,@p0, @es), @p1, @es)))

¿Puede alguien darme una mejor solución a este problema en términos de rendimiento y quizás una implementación más limpia?

Answer 1

¡Realmente me gusta la solución de @ Juliett! Solo usaría un CTE para obtener todos los caracteres no válidos:

DECLARE @badStrings VARCHAR(100)
DECLARE @teststring VARCHAR(100)

SET @badStrings = ''><()!?@''
SET @teststring = ''Juliet ro><0zs my s0x()rz!!?!one!@!@!@!''

;WITH CTE AS
(
  SELECT SUBSTRING(@badStrings, 1, 1) AS [String], 1 AS [Start], 1 AS [Counter]
  UNION ALL
  SELECT SUBSTRING(@badStrings, [Start] + 1, 1) AS [String], [Start] + 1, [Counter] + 1 
  FROM CTE 
  WHERE [Counter] < LEN(@badStrings)
)

SELECT @teststring = REPLACE(@teststring, CTE.[String], '''') FROM CTE

SELECT @teststring

Juliet ro0zs my s0xrzone

Answer 2

Aquí están los pasos

1. Crear una función CLR

Ver el siguiente código:

public partial class UserDefinedFunctions 
{

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString Replace2(SqlString inputtext, SqlString filter,SqlString      replacewith)
{

    string str = inputtext.ToString();
    try
    {
        string pattern = (string)filter;
        string replacement = (string)replacewith;
        Regex rgx = new Regex(pattern);
        string result = rgx.Replace(str, replacement);
        return (SqlString)result;

    }
    catch (Exception s)
    {
        return (SqlString)s.Message;
    }
}
}

1. Implemente su función CLR

2. Ahora pruébalo

Ver el siguiente código:

create table dbo.test(dummydata varchar(255))
Go
INSERT INTO dbo.test values(''P@ssw1rd''),(''This 12is @test'')
Go
Update dbo.test
set dummydata=dbo.Replace2(dummydata,''[0-9@]'','''')

select * from dbo.test
dummydata, Psswrd, This is test booom!!!!!!!!!!!!!

Answer 3

Consideraría seriamente crear una UDF de CLR en su instead y usar expresiones regulares (tanto la cadena como el patrón se pueden pasar como parámetros) para realizar una búsqueda completa y reemplazarla por un rango de caracteres. Debe superar fácilmente a este UDF de SQL.

Answer 4

Le sugiero que cree una función escalar definida por el usuario. Este es un ejemplo (lo siento de antemano, porque los nombres de las variables están en español):

CREATE FUNCTION [dbo].[Udf_ReplaceChars] (
  @cadena VARCHAR(500),  -- String to manipulate
  @caracteresElim VARCHAR(100),  -- String of characters to be replaced
  @caracteresReem VARCHAR(100)   -- String of characters for replacement
) 
RETURNS VARCHAR(500)
AS
BEGIN
  DECLARE @cadenaFinal VARCHAR(500), @longCad INT, @pos INT, @caracter CHAR(1), @posCarER INT;
  SELECT
    @cadenaFinal = '''',
    @longCad = LEN(@cadena),
    @pos = 1;

  IF LEN(@caracteresElim)<>LEN(@caracteresReem)
    BEGIN
      RETURN NULL;
    END

  WHILE @pos <= @longCad
    BEGIN
      SELECT
        @caracter = SUBSTRING(@cadena,@pos,1),
        @pos = @pos + 1,
        @posCarER = CHARINDEX(@caracter,@caracteresElim);

      IF @posCarER <= 0
        BEGIN
          SET @cadenaFinal = @cadenaFinal + @caracter;
        END
      ELSE
        BEGIN
          SET @cadenaFinal = @cadenaFinal + SUBSTRING(@caracteresReem,@posCarER,1)
        END
    END

  RETURN @cadenaFinal;
END

Aquí hay un ejemplo que usa esta función:

SELECT dbo.Udf_ReplaceChars(''This is a test.'',''sat'',''Z47'');

Y el resultado es: 7hiZ iZ 4 7eZ7.

Como puede ver, cada carácter del parámetro @caracteresElim se reemplaza por el carácter en la misma posición del parámetro @caracteresReem .

Answer 5

Mientras se hizo esta pregunta sobre SQL Server 2005, vale la pena señalar que a partir del SQL Server 2017, la solicitud se puede hacer con la nueva función TRANSLATE.

https://docs.microsoft.com/en-us/sql/t-sql/functions/translate-transact-sql

Espero que esta información ayude a las personas que acceden a esta página en el futuro.

Answer 6

No sé por qué Charles Bretana borró su respuesta, así que la vuelvo a agregar como una respuesta CW, pero una columna calculada persistente es una muy buena manera de manejar estos casos donde necesita datos limpiados o transformados casi todo el tiempo. , pero necesita preservar la basura original. Su sugerencia es relevante y apropiada INDEPENDIENTEMENTE de cómo decida limpiar sus datos.

Específicamente, en mi proyecto actual, tengo una columna calculada persistente que recorta todos los ceros iniciales (afortunadamente esto se maneja de manera muy fácil en T-SQL directo) de algunos identificadores numéricos particulares almacenados de manera incoherente con ceros a la izquierda. Esto se almacena en columnas calculadas persistentes en las tablas que lo necesitan y se indexa porque ese identificador conformado se usa a menudo en las uniones.

Answer 7

Un truco útil en SQL es la capacidad de usar @var = function(...) para asignar un valor. Si tiene varios registros en su conjunto de registros, su var se asigna múltiples veces con efectos secundarios:

declare @badStrings table (item varchar(50))

INSERT INTO @badStrings(item)
SELECT ''>'' UNION ALL
SELECT ''<'' UNION ALL
SELECT ''('' UNION ALL
SELECT '')'' UNION ALL
SELECT ''!'' UNION ALL
SELECT ''?'' UNION ALL
SELECT ''@''

declare @testString varchar(100), @newString varchar(100)

set @teststring = ''Juliet ro><0zs my s0x()rz!!?!one!@!@!@!''
set @newString = @testString

SELECT @newString = Replace(@newString, item, '''') FROM @badStrings

select @newString -- returns ''Juliet ro0zs my s0xrzone''

Answer 8

Una opción es usar una tabla de números / recuento para conducir un proceso iterativo a través de una consulta basada en pseudo-conjunto.

La idea general del reemplazo de caracteres se puede demostrar con un enfoque de tabla de mapas de caracteres simple:

create table charMap (srcChar char(1), replaceChar char(1))
insert charMap values (''a'', ''z'')
insert charMap values (''b'', ''y'')


create table testChar(srcChar char(1))
insert testChar values (''1'')
insert testChar values (''a'')
insert testChar values (''2'')
insert testChar values (''b'')

select 
coalesce(charMap.replaceChar, testChar.srcChar) as charData
from testChar left join charMap on testChar.srcChar = charMap.srcChar

Luego puede traer el método de tabla de conteo para hacer la búsqueda en cada posición de personaje en la cadena.

create table tally (i int)
declare @i int
set @i = 1
while @i <= 256 begin
    insert tally values (@i)
    set @i = @i + 1
end

create table testData (testString char(10))
insert testData values (''123a456'')
insert testData values (''123ab456'')
insert testData values (''123b456'')

select
    i,
    SUBSTRING(testString, i, 1) as srcChar,
    coalesce(charMap.replaceChar, SUBSTRING(testString, i, 1)) as charData
from testData cross join tally
    left join charMap on SUBSTRING(testString, i, 1) = charMap.srcChar
where i <= LEN(testString)

Answer 9

declare @testVal varchar(20)

set @testVal = ''?t/es?ti/n*g 1*2?3*''

select @testVal = REPLACE(@testVal, item, '''') from (select ''?'' item union select ''*'' union select ''/'') list

select @testVal;