Verschachtelte REPLACE-Funktion gesucht

[chgs2013]

Hallo,

ich habe eine Tabelle mit Kundendaten, hierbei wurde die Schreibweise nicht beachtet, es müssen daher mehrere Korrekturen erfolgen.

Angenommen es soll die Angabe der Straße korrigiert werden, dann müssen viele Konstellationen beachten werden.

Wir kann ich eine solche REPLACE-Funktion schöner / übersichtlicher verschachteln?

Momentan sieht das böse aus:

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(cStrasse, 'straße', 'str.'), 'Straße', 'Str.'), 'strasse', 'str.'), 'Strasse', 'Str.'), ' str.', ' Str.'), 'str ', 'str. '), 'Str ', 'Str. '), ' .', '.'), '..', '.'), '.1', '. 1'), '.2', '. 2'), '.3', '. 3'), '.4', '. 4'), '.5', '. 5'), '.6', '. 6'), '.7', '. 7'), '.8', '. 8'), '.9', '. 9')

 

[ukulele]

Du könntest noch mit einer CASE-Schleife verschiedene Fälle unterschiedlich behandeln, da aber sicherlich viele Schreibweisen in Kombination auftreten können macht das vermutlich keinen Sinn.

Auch kannst du dir eine eigene Funktion schreiben und dort eine Variable abarbeiten in dem du die replace() Befehle nacheinander anwendest, so läßt es sich gut dokumentieren. Theoretisch kannst du dann auch eine Tabelle mit Ausdrücken füllen und per dynamischem SQL darauf die replace() Funktion anwenden, das wird dann aber etwas mehr arbeit. Das läßt sich dann auch besser sortieren wenn die Reihenfolge eine Rolle spielt.

 

[Distrilec]

Wo bei ich hier fast schon geneigt wäre Reguläre Ausdrücke zu empfehlen
Anstatt nach ".1" bis ".9" zu suchen gäbe es da dann die Möglichkeit des ".[:digit:]" was dir schoneinmal 50% der Replace's ersparen würde (Ist allerdings vergleichweise sehr CPU-Kosten intensiv)

 

[chgs2013]

mmhhh... hauptsache es wird übersichtlicher ...

wüsste aber nicht wie ...

 

[Tommi]

Hallo,

wenn du es etwas übersichtlicher haben möchtest, ist die Lösung von Ukulele hier eine Ersetzungs-Funktion zu schreiben die übersichtlichste Methode.
Das müsstest du auch dann machen, wenn die Ersetzungen in einer eigenen Tabelle verwaltet werden.

Ich würde allerdings aus Performance-Gründen die Ersetzungen fest in der Funktion eintragen und auf eine zusätzliche Tabelle für Ersetzungen verzichten.
Letztendlich muss ja jede Zeile in der die Ersetzung vorgenommen werden muss, die Funktion aufrufen und durchlaufen.
Wenn darin noch eine weitere Tabelle aufgerufen wird, verbrät das sehr viel Zeit.

Das sähe dann so aus:

CREATE FUNCTION dbo.fct_StrasseErsetzung(@Strasse as varchar(500)) RETURNS varchar(500)
AS
BEGIN
    SET @Strasse = REPLACE(@Strasse, 'straße', 'str.')
    SET @Strasse = REPLACE(@Strasse, 'Straße', 'Str.')
    SET @Strasse = REPLACE(@Strasse, 'strasse', 'str.')
    .
    .
    .
   
    RETURN @Strasse
END

Viele Grüße,
Tommi

 

[Tommi]

achja,

der Aufruf in dem Select wäre dann nur noch

 SELECT dbo.fct_StrasseErsetzung(cStrasse) as cStrasse ...

Viele Grüße,
Tommi

 

[chgs2013]

Danke dir Tommi!

Ich habe mit Funktionen leider noch nie gearbeitet, wie könnte sowas fertig aussehen, an einem Beispiel?

So? Es werden hier später immer ALLE Datensätze überarbeitet, nicht wie unten im Beispiel nun die ID=100.

CREATE FUNCTION dbo.fct_StrasseErsetzung(@Strasse as varchar(500)) RETURNS varchar(500)
AS
BEGIN
SET @Strasse = REPLACE(@Strasse, 'straße', 'str.')
SET @Strasse = REPLACE(@Strasse, 'Straße', 'Str.')
SET @Strasse = REPLACE(@Strasse, 'strasse', 'str.')
RETURN @Strasse
END

SELECT dbo.fct_StrasseErsetzung(cStrasse) as cStrasse FROM tCUSTOMER WHERE kCUSTOMERID = '100';

 

[Tommi]

Hi,

genau so wird's gemacht. Bei der angelegten Funktion handelt es sich um eine sogenannte "Skalarwertfunktion".
Näheres azuerfährst du hier:
https://msdn.microsoft.com/de-de/library/ms191320.aspx

Diese "Skalarwertfunktion" kann im SQL Server Management Studio auch wieder als Skript aufgerufen werden.
Angezeigt werden diese Funktionen im Pfad der Datenbank > Programmierbarkeit > Funktionen > Skalarwertfunktionen

Das Änderungs-Skript kann man wie gewohnt über das Kontext-Menü aufrufen, wenn man mit der rechten Maustaste auf den Namen der Funktion klickt:
Skript für Funktion als > Alter in > Neues Abfrage-Editor-Fenster

Das Skript oben musst du noch erweitern für jedes Replace in deinem geschachtelten Ausdruck.

Noch ein Tipp: immer darauf achten, in welcher Datenbank eine solche Funktion angelegt wird!

Viele Grüße,
Tommi

 

[chgs2013]

CREATE FUNCTION dbo.fct_StrasseErsetzung(@Strasse as varchar(500)) RETURNS varchar(500)
AS
BEGIN
SET @Strasse = REPLACE(@Strasse, 'straße', 'str.')
SET @Strasse = REPLACE(@Strasse, 'Straße', 'Str.')
SET @Strasse = REPLACE(@Strasse, 'strasse', 'str.')
RETURN @Strasse
END

SELECT dbo.fct_StrasseErsetzung(cStrasse) as cStrasse FROM tCUSTOMER WHERE kCUSTOMERID = '100';

Das will nicht so klappen ... ist SELECT überhaupt zum überarbeiten richtig? Sollte doch eine UPDATE Funktion sein.

 

[ukulele]

Eigentlich sollte dir der Select die bereinigte Straße anzeigen. Wenn du den Wert in der Spalte dauerhaft verändern willst musst du natürlich mit UPDATE arbeiten, zum testen der Funktion oder zum Ausgeben reicht aber auch der Aufruf mit SELECT.

 

[chgs2013]

Jo dachte ich mir, dann passt das

Ich bastel mir halt ein Tool, sprich per Batch wird ein SQL-File angetriggert, das meine Änderungen / Funktionen beinhalten soll.

Ich denke das sollte ja kein Problem darstellen, alles in die SQL zu packen, oder?

 

[ukulele]

Nein das müsste gehen. Alternativ kannst du auch mit einem Trigger auf der Tabelle arbeiten und bei einem neuen oder veränderten Datensatz entweder die Funtkion aufrufen oder die Funktion im Trigger selbst abbilden. Dann den Datensatz nachträglich korrigieren oder mit einem Instead of Trigger schon vor dem Schreiben in die Tabelle anpassen.

 

[chgs2013]

Habe gerade bemerkt, dass ich in MS SQL Management Studio keine FUNCTION im Editor testen kann.
Ich müsste diese unter der jeweiligen DB unter Funktionen einbetten.

Kann ich dies auch ohne Einbettung einer Funktion in der DB realisieren?

Hintergrund ist, ich möchte via Batch-File ja ein SQL-File anstoßen, das ohne DB-Anpassungen dann läuft.

Ich vermute aber, das geht nicht?

 

[ukulele]

Du kannst den gesammten Inhalt deiner Funktion auch als SQL Script abbilden. Wenn du dann mehrere Datensätze durchlaufen willst musst du aber eine Schleife / Cursor schreiben.

 

[chgs2013]

umpf danke für die Info ... bekomme das wohl aber so nicht hin, wenn ich ein Beispiel als Grundgerüst habe schon eher :-(