wake-up-neo.com

So ersetzen Sie mehrere Zeichen in SQL

Dies basiert auf einer ähnlichen Frage Ersetzen mehrerer Zeichen in Access SQL?

Ich habe dies geschrieben, da SQL Server 2005 scheinbar eine Begrenzung auf die replace () - Funktion auf 19 Ersetzungen in einer where-Klausel hat.

Ich habe die folgende Aufgabe: Sie müssen eine Übereinstimmung mit einer Spalte durchführen und die Chancen verbessern, dass eine Übereinstimmung mehrere nicht benötigte Zeichen mit der replace () - Funktion entfernt

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 

Wenn REPLACE () in der where-Klausel> 19 ist, funktioniert es nicht. Die Lösung, die ich mir ausgedacht habe, ist, in diesem Beispiel eine SQL-Funktion namens trimChars zu erstellen (entschuldigen Sie sie ab @ 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 

Dies kann dann zusätzlich zu den anderen Ersetzungszeichenfolgen verwendet werden

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

Ich habe ein paar weitere Funktionen erstellt, um ähnliche Ersetzungen wie trimChars durchzuführen (trimMoreChars ()

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

Kann mir jemand eine bessere Lösung für dieses Problem in Bezug auf die Leistung und möglicherweise eine sauberere Implementierung geben?

33
kiev

Ich würde ernsthaft erwägen, eine CLR-UDF zu erstellenstattdessen und reguläre Ausdrücke (sowohl der String als auch das Muster können als Parameter übergeben werden), um eine vollständige Suche durchzuführen und einen Bereich von zu ersetzen Zeichen. Es sollte diese SQL-UDF leicht übertreffen.

22
Cade Roux

Ein nützlicher Trick in SQL ist die Möglichkeit, mithilfe von @var = function(...) einen Wert zuzuweisen. Wenn in Ihrem Datensatz mehrere Datensätze vorhanden sind, wird Ihr var mehrfach mit Nebeneffekten zugewiesen:

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()[email protected][email protected][email protected]!'
set @newString = @testString

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

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

Ich mag @ Julietts Lösung wirklich! Ich würde einfach einen CTE verwenden, um alle ungültigen Zeichen zu erhalten:

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

SET @badStrings = '><()[email protected]'
SET @teststring = 'Juliet ro><0zs my s0x()[email protected][email protected][email protected]!'

;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 meine s0xrzone

16
Duanne

Ich empfehle Ihnen, eine benutzerdefinierte benutzerdefinierte Funktion zu erstellen. Dies ist ein Beispiel (leider im Voraus, da die Variablennamen auf Spanisch sind):

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

Hier ist ein Beispiel, das diese Funktion verwendet:

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

Und das Ergebnis ist: 7Hz iZ 4 7eZ7.

Wie Sie sehen, wird jedes Zeichen des @caracteresElim-Parameters durch das Zeichen an derselben Stelle des @caracteresReem-Parameters ersetzt.

Ich hatte ein einmaliges Problem bei der Datenmigration, bei dem die Quelldaten einige ungewöhnliche/technische Zeichen sowie die allgegenwärtigen zusätzlichen Kommas in CSVs nicht korrekt ausgeben konnten.

Wir entschieden, dass für jedes dieser Zeichen der Quellenextrakt sie durch etwas ersetzen sollte, das sowohl für das Quellsystem als auch für den SQL Server, der sie lädt, erkennbar war, ansonsten aber nicht in den Daten enthalten wäre.

Es bedeutete jedoch, dass in verschiedenen Spalten in verschiedenen Tabellen diese Ersatzzeichen erscheinen würden und ich sie ersetzen müsste. Durch das Verschachteln mehrerer REPLACE-Funktionen wurde der Importcode unheimlich und fehleranfällig, da die Platzierung und die Anzahl der Klammern falsch eingeschätzt wurden. Deshalb habe ich die folgende Funktion geschrieben. Ich weiß, dass eine Spalte in einer Tabelle mit 3.000 Zeilen in weniger als einer Sekunde verarbeitet werden kann, obwohl ich nicht sicher bin, wie schnell sie auf mehrere Millionen Zeilentabellen skaliert werden kann.

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;

Es funktioniert, indem sowohl die auszuwertende Zeichenfolge als auch die zu ersetzenden Zeichen (@OriginalString) zusammen mit einer Zeichenfolge aus gepaarten Zeichen übergeben werden, wobei das erste Zeichen durch das zweite, das dritte durch das vierte, das fünfte durch das sechste und so weiter ersetzt werden soll on (@ReplaceTheseChars).

Hier ist die Zeichenfolge, die ich ersetzen musste, und ihre Ersetzungen ... ['] "~, {Ø} ° $ ± | ¼¦¼ª½¬½ ^ ¾ # ✓

eine öffnende eckige Klammer bezeichnet einen Apostroph, eine schließende ein Anführungszeichen. Sie können sehen, dass es dort vulgäre Brüche sowie Grad- und Durchmessersymbole gab.

Es gibt eine Standardeinstellung für @LengthOfReplacement, die als Ausgangspunkt verwendet wird, wenn längere Zeichenfolgen ersetzt werden müssen. Ich habe in meinem Projekt damit herumgespielt, aber der einzelne Zeichenersatz war die Hauptfunktion.

Die Bedingung der Fallbeschreibung ist wichtig. Es stellt sicher, dass es das Zeichen nur ersetzt, wenn es in Ihrer @ReplaceTheseChars-Variablen gefunden wird und dass das Zeichen an einer ungeraden Position gefunden werden muss (das Minus-1-Ergebnis von charindex stellt sicher, dass alles, was NICHT gefunden wird, einen negativen Modulo-Wert zurückgibt). Wenn Sie eine Tilde (~) an Position 5 finden, wird sie durch ein Komma ersetzt. Wenn Sie jedoch bei einem nachfolgenden Lauf das Komma an Position 6 finden, wird es nicht durch eine geschweifte Klammer ({) ersetzt.

Dies lässt sich am besten an einem Beispiel demonstrieren ...

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
                               );

Dies gibt sowohl den Wert nach dem ersten Durchlauf durch die Funktion als auch das zweite Mal wie folgt zurück ... abc, def'¼ "'123 abc, def'¼"' 123

Ein Tabellenupdate wäre eben

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

Schließlich (ich höre Sie sagen!), Obwohl ich keinen Zugriff auf die Übersetzungsfunktion hatte, glaube ich, dass diese Funktion das in der Dokumentation gezeigte Beispiel ziemlich leicht verarbeiten kann. Die TRANSLATE-Funktionsdemo ist

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

das 2 * (3 + 4)/(7-2) zurückgibt, obwohl ich verstehe, dass es möglicherweise nicht auf 2 * [3 + 4]/[7-2] funktioniert !!

Meine Funktion würde dies folgendermaßen angehen, indem sie jedes zu ersetzende Zeichen auflistet, gefolgt von dessen Ersetzung [-> (, {-> (usw.

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

das wird auch funktionieren für

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

Ich hoffe, jemand findet dies nützlich und wenn Sie die Leistung mit größeren Tabellen testen können, lassen Sie es uns auf die eine oder andere Weise wissen!

2
Chloe Williams
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;
1
Adil

Eine Option ist die Verwendung einer Zahlen-/Zählertabelle, um einen iterativen Prozess über eine auf Pseudosets basierende Abfrage zu steuern.

Die allgemeine Idee des Ersetzungszeichens von Zeichen kann mit einem einfachen Ansatz der Zeichentabelle veranschaulicht werden:

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

Dann können Sie den Tally-Table-Ansatz verwenden, um die Suche nach jeder Zeichenposition in der Zeichenfolge durchzuführen.

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)
0
ahains

Während diese Frage zu SQL Server 2005 gestellt wurde, ist es erwähnenswert, dass die Anforderung ab SQL Server 2017 mit der neuen TRANSLATE-Funktion ausgeführt werden kann.

https://docs.Microsoft.com/de-de/sql/t-sql/functions/translate-transact-sql

Ich hoffe, diese Informationen helfen Menschen, die in Zukunft auf diese Seite gelangen.

0
Ethan1701

Ich weiß nicht, warum Charles Bretana seine Antwort gelöscht hat, also füge ich sie als CW-Antwort hinzu, aber eine dauerhaft berechnete Spalte ist eine wirklich gute Möglichkeit, diese Fälle zu behandeln, in denen Sie fast immer bereinigte oder transformierte Daten benötigen , aber müssen den ursprünglichen Müll erhalten. Sein Vorschlag ist relevant und angemessen, egal wie Sie Ihre Daten bereinigen.

In meinem aktuellen Projekt habe ich eine persistente berechnete Spalte, die alle führenden Nullen trimmt (zum Glück ist dies in reiner T-SQL einfach zu handhaben) von bestimmten numerischen Bezeichnern, die inkonsistent mit führenden Nullen gespeichert wurden. Dies wird in persistenten berechneten Spalten in den Tabellen, die es benötigen, gespeichert und indiziert, da diese konforme Kennung häufig in Joins verwendet wird.

0
Cade Roux

Hier sind die Schritte 

  1. Erstellen Sie eine CLR-Funktion

Siehe folgenden Code:

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. Stellen Sie Ihre CLR-Funktion bereit

  2. Testen Sie es jetzt

Siehe folgenden Code:

create table dbo.test(dummydata varchar(255))
Go
INSERT INTO dbo.test values('[email protected]'),('This 12is @test')
Go
Update dbo.test
set dummydata=dbo.Replace2(dummydata,'[[email protected]]','')

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