Gelegentlich steht man vor dem Problem aus einem großen Textfeld, das eine komplette Adresse enthält, einzelne Elemente, wie beispielsweise die Postleitzahl extrahieren zu müssen.
Eine mögliche Variante liegt natürlich darin, eine .NET-Funktion schreiben, die auf Basis eines regulären Ausdrucks den richtigen Bestandteil aus dem Adressfeld herausschneidet und diese über die .NET CLR-Integration in SQL Server einzubinden. Dies erfordert natürlich, dass die .NET CLR-Integration auf dem Server aktiviert ist, was in vielen Umgebungen aus Sicherheitsgründen nicht gegeben ist.
Eleganter wäre es ohnehin, eine Lösung zu finden, die komplett in T-SQL implementiert ist.
Hierfür möchte ich nun verschiedene Ansätze vorstellen, die jedoch alle ihre eigenen Vor- und Nachteile haben.
Der Einfachheit halber nutze ich als Quelle für die Adresse erstmal keine Tabelle, sondern eine Variable, in der eine komplette Adresse gespeichert ist:
DECLARE @Adresse AS nvarchar(800) SET @Adresse = 'Michael Mustermann, Musterstrasse 12, 65432 Musterstadt'
Mit Hilfe der PATINDEX-Funktion lässt sich nun die erst Position finden, an der eine fünfstellige Zahl steht, die dann mit der SUBSTRING-Funktion aus der Variablen ausgeschnitten wird:
SELECT SUBSTRING(@Adresse,PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', @Adresse),5)
Dies bringt jedoch mehrere Nachteile mit sich:
1. Bei keiner fünfstelligen Zahl gibt die PATINDEX-Funktion den Wert 0 zurück, so dass die ersten fünf Zeichen der Adresse als PLZ interpretiert werden, auch wenn diese Buchstaben sind.
2. Bei mehreren mindestens fünfstelligen Zahlen (z.B. bei 5-stelliger Hausnummer) wird der erste Wert (also die Hausnummer) verwendet.
3. Bei einer Postleitzahl mit mehr als fünf Stellen werden die ersten fünf Stellen verwendet und der Rest ignoriert.
Das erste Problem lässt sich leicht durch eine Zusatzbedingung lösen:
DECLARE @Adresse AS nvarchar(800)
SET @Adresse = 'Michael Mustermann, Musterstrasse 12, 65 Musterstadt'
SELECT SUBSTRING(@Adresse,PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', @Adresse),5)
WHERE PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', @Adresse)>0
Ist nun keine fünfstellige Zahl in der Adresse enthalten, wird nun auch kein Ergebnis mehr zurückgeliefert.
Für das zweite Problem dreht man (unter der Annahme, dass die PLZ wahrscheinlich die letzte fünfstellige Zahl in der Adresse ist) die Zeichenreihenfolge in der Adresse einfach um, selektiert die erste fünfstellige Zahl und dreht in dieser die Ziffern wieder in die ursprüngliche Reihenfolge:
DECLARE @Adresse AS nvarchar(800)
SET @Adresse = 'Michael Mustermann, Musterstrasse 12345, 65432 Musterstadt'
SELECT REVERSE(SUBSTRING(REVERSE(@Adresse),PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', REVERSE(@Adresse)),5))
WHERE PATINDEX('%[0-9][0-9][0-9][0-9][0-9]%', @Adresse)>0
Bleibt noch das dritte Problem, das sich lösen lässt, wenn man in der Suchmusterzeichenfolge vor und nach den 5 numerischen Ziffern ein Leerzeichen einfügt, wodurch sich natürlich auch die Position um ein Zeichen (daher das zusätzliche „+1“) verschiebt.
DECLARE @Adresse AS nvarchar(800)
SET @Adresse = 'Michael Mustermann, Musterstrasse 12, 65432 Musterstadt'
SELECT REVERSE(SUBSTRING(REVERSE(@Adresse),PATINDEX('% [0-9][0-9][0-9][0-9][0-9] %', REVERSE(@Adresse))+1,5))
WHERE PATINDEX('% [0-9][0-9][0-9][0-9][0-9] %', @Adresse)>0
Dies setzt nun allerdings voraus, dass auch wirklich vor und nach der Postleitzahl ein Leerzeichen steht. Während man danach meist ein solches finden wird, kann davor auch direkt das Komma oder ein anderes Trennzeichen oder beispielsweise auch ein Länderkennzeichen gefolgt von einem Bindestrich stehen. Dies lässt sich lösen, indem man diese Sonderzeichen vorher per REPLACE durch ein Leerzeichen ersetzt.
DECLARE @Adresse AS nvarchar(800)
SET @Adresse = 'Michael Mustermann, Musterstrasse 12, D-65432 Musterstadt'
SELECT REVERSE(SUBSTRING(REVERSE(@Adresse),PATINDEX('% [0-9][0-9][0-9][0-9][0-9] %', REVERSE(REPLACE(REPLACE(REPLACE(@Adresse, ',', ' '), ';', ' '), '-', ' ')))+1,5))
WHERE PATINDEX('% [0-9][0-9][0-9][0-9][0-9] %', REPLACE(REPLACE(REPLACE(@Adresse, ',', ' '), ';', ' '), '-', ' '))>0
Ab SQL Server 2017 kann man statt der geschachtelten REPLACE-Aufrufe die TRANSLATE-Funktion verwenden, was die Abfrage wieder deutlich übersichtlicher macht:
DECLARE @Adresse AS nvarchar(800)
SET @Adresse = 'Michael Mustermann, Musterstrasse 12, D-65432 Musterstadt'
SELECT REVERSE(SUBSTRING(REVERSE(@Adresse),PATINDEX('% [0-9][0-9][0-9][0-9][0-9] %', REVERSE(TRANSLATE(@Adresse, ',;-', ' ')))+1,5))
WHERE PATINDEX('% [0-9][0-9][0-9][0-9][0-9] %', TRANSLATE(@Adresse, ',;-', ' '))>0
Ein anderer Ansatz das Problem zu lösen liegt darin, die Zeichenkette über die STRING_SPLIT-Funktion (ab SQL Server 2016 verfügbar) zuerst in einzelne Tokens zu zerlegen und anschließend das letzte Token zu suchen, das fünfstellig und numerisch ist. Damit die Zerlegung funktioniert, sollten jedoch auch hier vorher alle anderen Trennzeichen durch ein Leerzeichen ersetzt werden. Schauen wir uns zunächst die Zerlegung in einzelne Token an:
DECLARE @Adresse AS nvarchar(800) SET @Adresse = 'Michael Mustermann, Musterstrasse 12, D-65432 Musterstadt' SELECT value FROM STRING_SPLIT(REPLACE(REPLACE(REPLACE(@Adresse, ';', ' '), '-', ' '), ',', ' '), ' ') Ab SQL Server 2017: SELECT value FROM STRING_SPLIT(TRANSLATE(@Adresse, ',;-', ' '), ' ')
Durch eine Filterung auf Einträge die rein numerisch sind (ISNUMERIC(value)=1) und deren Länge 5 Zeichen beträgt, bleiben gültige Postleitzahlenkandidaten übrig. Wenn man nun per ORDER BY value DESC die Reihenfolge umdreht und dann den ersten Wert selektiert, hat man damit die letzte fünfstellige Ziffer in der ursprünglichen Zeichenfolge ermittelt:
SELECT TOP 1 value AS PLZ FROM STRING_SPLIT(REPLACE(REPLACE(REPLACE(@Adresse, ';', ' '), '-', ' '), ',', ' '), ' ') WHERE ISNUMERIC(value)=1 AND LEN(RTRIM(value))=5 ORDER BY value DESC
Ab SQL Server 2017:
SELECT TOP 1 value AS PLZ FROM STRING_SPLIT(TRANSLATE(@Adresse, ',;-', ' '), ' ') WHERE ISNUMERIC(value)=1 AND LEN(RTRIM(value))=5 ORDER BY value DESC
Eingebaut in eine Abfrage auf eine Tabelle namens Quelltabelle mit einer Spalte Adresse, in der die kompletten Adressen gespeichert sind, sieht dies dann wie folgt aus:
SELECT Adresse, (SELECT TOP 1 value AS PLZ FROM STRING_SPLIT(TRANSLATE(@Adresse, ',;-', ' '), ' ') WHERE ISNUMERIC(value) = 1 AND LEN(RTRIM(value)) = 5 ORDER BY value DESC) AS PLZ FROM Quelltabelle
Oder für die weiter oben dargestellte Variante auf Basis der PATINDEX-Funktion:
SELECT Adresse,
(SELECT REVERSE(SUBSTRING(REVERSE(Adresse),PATINDEX('% [0-9][0-9][0-9][0-9][0-9] %', REVERSE(TRANSLATE(Adresse, ',;-', ' ')))+1,5))
WHERE PATINDEX('% [0-9][0-9][0-9][0-9][0-9] %', TRANSLATE(Adresse, ',;-', ' '))>0) AS PLZ
FROM Quelltabelle
Es gibt also verschiedene Varianten, das Problem zu lösen. Während die Variante mit STRING_SPLIT die elegantere mit besser lesbarem Code ist, ist diese Funktion erst ab SQL Server 2016 verfügbar. Die Variante mit PATINDEX funktioniert bereits ab SQL Server 2008 und dürfte sogar die etwas performantere sein, da darin nicht pro Datensatz eine Tokenliste aufgebaut werden muss. Da diese Operationen aber ohnehin im Hauptspeicher stattfinden, dürfte dies nicht allzu sehr ins Gewicht fallen.
Wieder einmal sind aus zuverlässiger Quelle Informationen über ein bahnbrechendes neues Feature der nächsten Version von SQL Server durchgesickert. Und zwar geht es um die Integration einer weiteren Programmiersprache in SQL Server. Während neben T-SQL in den letzten Versionen die Programmiersprachen R und Python ihren Weg in Microsoft SQL Server gefunden haben, wird aktuell unter dem Codenamen „Tortuga“ an der Integration der Turtle Graphik Programmiersprache Logo gearbeitet. Mit dieser Sprache, die bereits in den späten 1960er Jahren entwickelt wurde, lassen sich dann Objekte der Datentypen geometry und geography einfacher beschreiben, indem statt der Definition von Punkten und Polygonen einfache Turtle Graphik Anweisungen wie „move forward 100“ und „turn right 45 degrees“ an eine Spalte oder Variable des Datentyps geometry übergeben werden.
Derzeit wird noch geprüft, inwiefern sich die Turtle Graphik Kommandos eventuell sogar nutzen lassen, um gezielt einzelne Positionen in einem SQL Server Analysis Service Cube anzusprechen. Auch die Verwendung im Zusammenhang mit Graph-Datenbanken ist vorstellbar, so dass die Turtle sich quasi auf den Kanten von Knoten zu Knoten bewegt. Die Bewegungsrichtung der Turtle definiert dabei die Richtung der gerichteten Kanten. Lediglich die Schrittweite verliert in diesem Einsatzszenario an Bedeutung, da für die Kanten eine einheitliche Länge angenommen wird.
Mit diesem neuen Feature könnte im Idealfall auch eine neue Zielgruppe von Anwendern für SQL Server erschlossen werden, der die Datenbankprogrammierung bisher zu abstrakt war.
Vom 26.-28. Februar 2017 findet im Kongresscenter darmstadtium wieder die wichtigste und größte deutsche SQL Server Konferenz statt. Ich freue mich sehr, auch diesmal wieder mit einem eigenen Vortrag vertreten zu sein.
Die Konferenz selbst beginnt wie gewohnt mit einem Workshoptag, dem zwei Tage mit parallel stattfindenden Tracks zu verschiedenen Themen aus den Bereichen Business Intelligence, Information Management, Azure Data Platform, Administration, Development-Operations und Big Data folgen. Als Referenten sind ca. 50 Speaker aus dem In- und Ausland im Einsatz. Workshoptag und Hauptkonferenz sind auch separat buchbar.
Mein eigener Vortrag mit dem Titel „InMemory und Columnstore im Praxiseinsatz für OLTP-Systeme“ wird anhand von realen Projekterfahrungen darstellen, mit welchen Stolpersteinen man bei der Einführung dieser Technologien im Einsatz für OLTP-Systeme rechnen muss und wie diese – sofern möglich – umgangen werden können. Dazu wird natürlich auch rückblickend der Nutzen betrachtet, den diese Technologien OLTP-Umfeld gebracht haben.
Weitere Informationen zur Konferenz gibt es auf der offiziellen Veranstaltungswebsite: http://sqlkonferenz.de
Nachtrag: Mittlerweile ist die Konferenz vorbei. Wer an den Slides meiner Session interessiert ist, kann diese hier herunterladen: InMemoryColumnstoreOLTP_Panther
Auch in diesem Jahr werde ich wieder bei den Frankfurter IT-Tagen mit einem Vortrag vertreten sein. Unter der Überschrift Gutes SQL – schlechtes SQL (Tipps & Tricks aus 20 Jahren Erfahrung mit dem Microsoft SQL Server) werde ich anhand von zahlreichen Praxisbeispielen zeigen, wie man besser lesbaren, schnelleren, robusteren oder einfach besseren T-SQL Code schreibt.
Details zum Vortrag sowie der genauen Termin sind hier zu finden:
https://www.ittage.informatik-aktuell.de/programm/2017/gutes-sql-schlechtes-SQL/
Diese Session findet im Rahmen der IT-Tage, der Jahreskonferenz des Fachmagazins „Informatik Aktuell“ im Kongresshaus der Messe Frankfurt statt.
Das komplette Programm der IT-Tage 2017 gibt es hier:
https://www.ittage.informatik-aktuell.de/programm/
Vom 25. bis 29. September feiert die BASTA! in Mainz ihr 20-jähriges Jubiläum. Da dies ziemlich genau auch der Zeit entspricht, die ich mich mit dem Microsoft SQL Server befasse, werde ich passend dazu am Donnerstag, dem 28.09.2017 von 17:00 bis 18:00 Uhr dort einen Vortrag zum Thema „Gutes SQL – schlechtes SQL (Tipps & Tricks aus 20 Jahren Erfahrung mit dem Microsoft SQL Server)“ halten.
Dabei werde ich anhand von zahlreichen Praxisbeispielen zeigen, wie man besser lesbaren, schnelleren, robusteren oder einfach besseren T-SQL Code schreibt.
Details zum Vortrag: https://basta.net/dataaccess-storage/gutes-sql-schlechtes-sql-tipps-tricks-aus-20-jahren-erfahrung-mit-dem-microsoft-sql-Server
Die BASTA! läuft wie gewohnt ganze 5 Tage wovon am Pre-Conference-Day (Montag) und Post-Conference-Day (Freitag) ganztägige Workshops angeboten werden, während der Rest der Konferenz mit kürzeren Vorträgen aus verschiedenen Bereichen rund um .NET, Windows und Javascript gefüllt ist. Neu ist dabei die Dauer der Vorträge, die diesmal von bisher 75 auf 60 Minuten gekürzt wurde, damit noch mehr interessante Themen in den 3 Tagen der Hauptkonferenz untergebracht werden können.
Weitere Infos zur Veranstaltung gibt es auf der offiziellen Veranstaltungs-Website: http://www.basta.net
Seit kurzem ist der erste Release Candidate für SQL Server 2017 verfügbar.
Die meisten der neuen Features wie beispielsweise die Unterstützung von Graphendatenbanken oder Python für Datenanalysen sind sehr speziell, so dass sie sicherlich nur einen kleinen Teil der Nutzer betreffen. Und auch die Möglichkeit, SQL Server jetzt auf Linux laufen zu lassen ist sicherlich nichts für jedermann.
Von den neuen Optimierungen, die unter der Überschrift „Adaptive Query Processing“ zusammengefasst sind, dürfte jedoch ziemlich jeder profitieren und das ohne Änderung einer einzigen Codezeile. Lediglich durch Setzen des Kompatibilitätsmodus der Datenbank auf 140 (= SQL 2017) wird die neue Engine aktiv, die effizientere Ausführungspläne für Abfragen erstellen soll. Das geschieht unter anderem dadurch, dass die Speicherzuteilung für bereits gespeicherte Pläne bei wiederholter Verwendung weiter optimiert wird. Weiterhin gibt es den neuen Adaptive Join-Operator, der Aufgrund der gelieferten Zeilenanzahl zur Laufzeit entscheidet, ob ein Hash oder Nested Loop Join effektiver ist. Wie groß der damit erzielte Performance-Gewinn in der Praxis tatsächlich sein wird, bleibt sicherlich abzuwarten, aber der Ansatz klingt soweit schon mal sehr vielversprechend.
Weitere Infos und Download:
Microsoft erhöht offensichtlich weiterhin die Schlagzahl bei der Veröffentlichung von neuen SQL Server Releases. Mittlerweile steht für die nächste Version wohl auch der Name fest: SQL Server 2017 (Ein gewagter Zug, wenn man bedenkt, dass wir schon mitten im genannten Jahr stecken.)
Generell stellt sich die Frage, ob diese neue Version die Anwender nicht sogar eher verunsichert. Schließlich haben die meisten Unternehmen noch nicht auf SQL Server 2016 umgestellt und schon wird die nächste Version angekündigt.
Wenn man die Features ansieht, scheinen sich die Neuerung aber vor allem auf alternative Plattformen (Linux, Mac, Docker) und weitere Programmiersprachen zur Analyse (neben R wird nun auch Python unterstützt) zu beziehen. Grundlegende Änderung an der Engine selbst werden nicht genannt, so dass das Update für die zahlreichen Unternehmen, die einen SQL Server auf Basis von Windows Server nutzen, bisher scheinbar nicht viel Neues bringt. (Je nachdem wann das finale Produkt erscheint, kann sich das natürlich auch noch ändern.)
Weitere Infos sowie die Möglichkeit, eine Preview-Version herunterzuladen gibt es unter dem folgenden Link: https://www.microsoft.com/en-us/sql-server/sql-server-2017
