Comment faire pour Remplacer Plusieurs Caractères dans SQL?

Je voudrais envisager sérieusement faire un CLR UDF au lieu de cela et en utilisant des expressions régulières (à la fois la chaîne et le modèle peuvent être passés en paramètres) pour faire une recherche complète et de le remplacer pour une plage de caractères. Il devrait facilement surpasser cette SQL UDF.

J'aime vraiment @Juliett la solution! Je voudrais juste utiliser une expression de table commune pour obtenir tous les caractères non valides:

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

J'ai eu une migration des données d'un problème où la source de données ne pouvait pas sortie correctement certains insolite/technique de caractères plus l'omniprésent supplémentaire des virgules dans CSVs.

Nous avons décidé que pour chaque caractère de la source de l'extrait doit remplacer par quelque chose qui était reconnaissable à la fois le système source et le Serveur SQL qui était en train de charger, mais qui ne serait pas dans les données.

Cela ne signifie pas cependant que dans les différentes colonnes à travers les différents tableaux de ces caractères de remplacement semble et je voudrais avoir à les remplacer. Imbrication de plusieurs de REMPLACER des fonctions mis le code d'importation sembler effrayant et sujettes à des erreurs de jugement erroné le placement et le nombre de supports de sorte que j'ai écrit la fonction suivante. Je sais qu'il peut traiter une colonne dans une table de 3 000 lignes en moins d'une seconde si je ne suis pas sûr de savoir comment rapidement il va évoluer jusqu'à plusieurs millions de ligne de tables.

create function [dbo].[udf_ReplaceMultipleChars]
(
@OriginalString nvarchar(4000)
, @ReplaceTheseChars nvarchar(100)
, @LengthOfReplacement int = 1
)
returns nvarchar(4000)
begin
declare @RevisedString nvarchar(4000) = N'';
declare @lengthofinput int =
(
select len(@OriginalString)
);
with AllNumbers
as (select 1 as  Number
union all
select Number + 1
from AllNumbers
where Number < @lengthofinput)
select @RevisedString += case
when (charindex(substring(@OriginalString, Number, 1), @ReplaceTheseChars, 1) - 1) % 2
= 0 then
substring(
@ReplaceTheseChars
, charindex(
substring(@OriginalString, Number, 1)
, @ReplaceTheseChars
, 1
) + 1
, @LengthOfReplacement
)
else
substring(@OriginalString, Number, 1)
end
from AllNumbers
option (maxrecursion 4000);
return (@RevisedString);
end;

Il fonctionne en soumettant à la fois la chaîne pour être évalués et ont des caractères de remplacement (@OriginalString) avec une chaîne de paires de caractères où le premier caractère doit être remplacé par le second, le troisième, le quatrième, cinquième, sixième, et ainsi de suite (@ReplaceTheseChars).

Ici est la chaîne de caractères que j'avais besoin de remplacer et de leurs remplaçants... [']"~,{Ø}°$±|¼¦¼ª½½^¾#✓

c'est à dire Un crochet d'ouverture dénote une apostrophe, une fermeture d'une double cotation. Vous pouvez voir qu'il y avait vulgaire fractions ainsi que les degrés et le diamètre des symboles de là.

Il y a un défaut @LengthOfReplacement qui est inclus comme un point de départ si quelqu'un avait besoin de remplacer des chaînes plus longues. J'ai joué un peu avec le fait que dans mon projet, mais que le seul caractère de remplacement est la fonction principale.

La condition de l'instruction de cas est important. Il assure qu'il ne remplace le personnage si il est trouvé dans votre @ReplaceTheseChars variable et que le personnage se retrouve dans une drôle de position numérotée (au moins 1 de charindex résultat assure que rien ne trouve PAS de retours négatifs modulo la valeur). j'.e si vous trouvez un tilde (~) dans la position 5-il va le remplacer par une virgule, mais si à la suite d'exécuter trouvé la virgule dans la position 6-il ne serait pas le remplacer par une accolade ({).

Cela peut être démontré par un exemple...

declare @ProductDescription nvarchar(20) = N'abc~def[¦][123';
select @ProductDescription
= dbo.udf_ReplaceMultipleChars(
@ProductDescription
/* NB the doubling up of the apostrophe is necessary in the string but resolves to a single apostrophe when passed to the function */
,'['']"~,{Ø}°$±|¼¦¼ª½¬½^¾#✓' 
, default
);
select @ProductDescription
, dbo.udf_ReplaceMultipleChars(
@ProductDescription
,'['']"~,{Ø}°$±|¼¦¼ª½¬½^¾#✓'
/* if you didn't know how to type those peculiar chars in then you can build a string like  this... '[' + nchar(0x0027) + ']"~,{' + nchar(0x00D8) + '}' + nchar(0x00B0) etc */
,
default
);

Ce sera le retour à la fois de la valeur après la première passe par le biais de la fonction et la deuxième fois, comme suit...
abc,def '¼ "'123 abc,def '¼ "'123

Une mise à jour de table serait juste

update a
set a.Col1 = udf.ReplaceMultipleChars(a.Col1,'~,]"',1)
from TestTable a

Enfin, me direz-vous!), bien que je n'ai pas eu accès à la fonction de traduction, je crois que cette fonction peut traiter l'exemple présenté dans la documentation assez facilement. La fonction de traduction de démo est

SELECT TRANSLATE('2*[3+4]/{7-2}', '[]{}', '()()');

qui renvoie 2*(3+4)/(7-2) même si je comprends que ça ne fonctionne pas sur 2*[3+4]/[7-2] !!

Ma fonction d'approche comme suit cette liste chaque char à être remplacé, suivie par son remplacement [ --> (, { --> ( etc.

select dbo.udf_ReplaceMultipleChars('2*[3+4]/{7-2}', '[({(])})', 1);

qui fonctionne également pour

select dbo.udf_ReplaceMultipleChars('2*[3+4]/[7-2]', '[({(])})', 1);

J'espère que quelqu'un le trouve utile et si vous arrivez à tester ses performances contre les grandes tables faites-nous savoir d'une manière ou d'une autre!

Alors que cette question a été posée sur SQL Server 2005, il est intéressant de noter qu'à partir de Sql Server 2017, la demande peut être faite avec la nouvelle fonction de traduction.

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.

Je ne sais pas pourquoi Charles Bretana supprimé de sa réponse, je suis donc de le rajouter en tant que CW réponse, mais une colonne calculée persistante est une très bonne façon de gérer ces situations où vous avez besoin purifiés ou des données transformées presque tout le temps, mais besoin de conserver l'original des ordures. 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 rogne tous les zéros (heureusement que c'est realtively faciles à manipuler des droites T-SQL) de certains identificateurs numériques stockées d'une manière incompatible avec les zéros non significatifs. Ce sont stockées dans des colonnes calculées persistantes dans les tableaux qui en ont besoin et indexés en raison conforme identifiant est souvent utilisé dans les jointures.