Glorf IT

Gestern kam mein Kollege Alexander mit einer ganz spannenden SQL-Fragestellung zu mir. weil die Anwendung auch noch den SQL-Server-2000 unterstützt, mussten wir etwas in die Trickkiste greifen. Ich habe die Fragestellung jetzt daheim noch mal unter dem SQL-Server-2005 nachvollzogen und konnte dabei ein paar der schicken, neuen Features nutzen…

Die Frage ist, wie man Strings, die über eine Legacy-Schnittstelle kommen und so in die Datenbank geschrieben wurden, aufteilen kann. Beispielsweise soll die Zeichenfolge "1234#12#bla#blub#132" mit SQL anhand des Trenners "#" in die Einzelbestandteile aufgeteilt und dann weiterverarbeitet werden.

So könnte die Tabelle aussehen:

Eine Lösung, die mir persönlich besonders gut gefällt, hat sich der große Itzik Ben Gan ausgedacht: Dabei wird eine Hilfstabelle benötigt, die nur eine Spalte hat, und für jede Zahl einen Datensatz enthält.

Dann kann man mit einem Cross-Join zwischen den Tabellen in der Where-Klausel diejenigen Sätze rausfischen, die an der n-ten Position ein "#" haben:

...
from LegacyInput
cross join hilfstabelle
where substring('#'+Zeichenkette+'#', n, 1)='#'
and n <= len(Zeichenkette)+2

Damit das erste Teilstück bei 1 anfängt, wird noch ein "#" vor den Anfang angefügt. Außerdem müssen nur die Datensätze untersucht werden, die kleiner/gleich der Länge der Zeichenkette sind. Das ist eigentlich doppelt gemoppelt und kann daher auch entfallen.

Im SELECT werden dann die Einzelbestandteile rausgelöst: von n bis zum nächsten "#", wobei "n" eine Position ist, an der der ein "#" gefunden wurde.

substring('#'+Zeichenkette, n+1, charindex('#',Zeichenkette+'#',n)-n)

Das gesamte Statement sieht dann so aus:

select lfdNr,
n-len(replace(substring('#'+Zeichenkette, 1, n), '#', '')) as "TeilNr",
substring('#'+Zeichenkette, n+1, charindex('#',Zeichenkette+'#',n)-n) as "Teilstück",
Zeichenkette
from LegacyInput
cross join hilfstabelle
where substring('#'+Zeichenkette, n, 1)='#'
and n <= len(Zeichenkette)+1

Und so das Ergebnis:

Das ist aus Datenbanksicht, aber nicht optimal. Wünschenswert wäre ein pivotiertes Ergebnis:

Das kann man dann ganz einfach mit dem PIVOT-Konstrukt erreichen:

SELECT *
FROM (select
LfdNr,
'attr'+convert(varchar,n-len(replace(substring('#'+Zeichenkette, 1, n), '#', ''))) as "Attribute",
substring('#'+Zeichenkette, n+1, charindex('#',Zeichenkette+'#',n)-n) as "Teilstück"
from LegacyInput
cross join hilfstabelle
where substring('#'+Zeichenkette, n, 1)='#'
and n <= len(Zeichenkette)+1 ) AS ATR PIVOT ( MAX("Teilstück") FOR attribute IN([attr1], [attr2], [attr3], [attr4], [attr5], [attr6], [attr7], [attr8], [attr9], [attr10]) ) AS PVT

Das komplette Beispiel steht auch zum Download bereit.