Comment remplacer plusieurs caractères dans SQL?

un truc utile en SQL est la capacité d'utiliser @var = function(...) pour assigner une valeur. Si vous avez plusieurs enregistrements dans votre jeu de disques, votre var est assigné plusieurs fois avec des effets secondaires:

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'

j'aime vraiment la solution de @Juliett! Je voudrais juste utiliser un CTE pour obtenir tous les caractères invalides:

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

Juliette ro0zs mon s0xrzone

je vous suggère de créer une fonction scalaire définie par l'utilisateur. Ceci est un exemple (désolé à l'avance, parce que les noms de variables sont en espagnol):

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

Voici un exemple d'utilisation de cette fonction:

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

et le résultat est: 7hiZ iZ 4 7eZ7.

comme vous pouvez le voir, chaque caractère du paramètre @caracteresElim est remplacé par le caractère dans la même position du @caracteresReem paramètre.

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;

une option est d'utiliser une table de nombres/décompte pour piloter un processus itératif via une requête basée sur un pseudo-jeu.

l'idée générale du remplacement de l'omble peut être démontrée à l'aide d'une table de correspondance 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

alors vous pouvez introduire l'approche de la table de comptage pour faire la recherche sur chaque position de caractère dans la chaîne.

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)

Je ne sais pas pourquoi Charles Bretana a supprimé sa réponse, donc je l'ajoute de nouveau comme une réponse CW, mais une colonne persisted computed est un très bon moyen de gérer ces cas où vous avez besoin de données nettoyées ou transformées presque tout le temps, mais besoin de préserver les déchets d'origine. Sa suggestion est pertinente et appropriée quelle que soit la façon dont vous décidez de nettoyer vos données.

plus Précisément, dans mon projet actuel, j'ai une colonne calculée persistante qui garnitures tous les zéros de tête (heureusement, cela est en réalité facilement manipulé en T-SQL droit) à partir de certains identificateurs numériques particuliers stockés de manière incohérente avec des zéros de tête. Ceci est stocké dans les colonnes calculées persistées dans les tables qui en ont besoin et indexées parce que cet identifiant conformé est souvent utilisé dans les jointures.

Voici les étapes

1. créer une fonction CLR

voir code suivant:

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. déployez votre fonction CLR

2. maintenant tester

voir code suivant:

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!!!!!!!!!!!!!

bien que cette question ait été posée à propos de SQL Server 2005, il est intéressant de noter qu'à partir de Sql Server 2017, la requête peut être faite avec la nouvelle fonction TRANSLATE.

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

j'espère que cette information aide les gens qui arrivent à cette page à l'avenir.