{"id":436,"date":"2007-01-30T22:32:41","date_gmt":"2007-01-30T21:32:41","guid":{"rendered":"http:\/\/www.glorf.it\/blog\/2007\/01\/30\/sql-talk\/sql-strings-zerhacken"},"modified":"2007-01-30T22:32:41","modified_gmt":"2007-01-30T21:32:41","slug":"sql-strings-zerhacken","status":"publish","type":"post","link":"http:\/\/www.glorf.it\/blog\/2007\/01\/30\/sql-talk\/sql-strings-zerhacken","title":{"rendered":"SQL: Strings zerhacken"},"content":{"rendered":"

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…<\/p>\n

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.<\/p>\n

So könnte die Tabelle aussehen:<\/p>\n\n\n\n\n\n\n\n
LfdNr<\/strong><\/td>\nZeichenkette<\/strong><\/td>\n<\/tr>\n
1<\/td>\n13242#22#blabla#233242#blubblub#12321#1312#1131##13123<\/td>\n<\/tr>\n
2<\/td>\n13332#12#blibla#233242#blubblub#12321#1312#1131##13123<\/td>\n<\/tr>\n
3<\/td>\n13353#21#blabli#233242#blubblub#12321#1312#1131##13123<\/td>\n<\/tr>\n
4<\/td>\n13242#12#blibli#233242#blubblub#12321#1312#1131##13123<\/td>\n<\/tr>\n
…<\/td>\n…<\/td>\n<\/tr>\n<\/table>\n

Eine Lösung, die mir persönlich besonders gut gefällt, hat sich der große Itzik Ben Gan<\/a> ausgedacht: Dabei wird eine Hilfstabelle benötigt, die nur eine Spalte hat, und für jede Zahl einen Datensatz enthält.<\/p>\n\n\n\n\n\n\n\n
n<\/strong><\/td>\n<\/tr>\n
1<\/td>\n<\/tr>\n
2<\/td>\n<\/tr>\n
3<\/td>\n<\/tr>\n
4<\/td>\n<\/tr>\n
…<\/td>\n<\/tr>\n<\/table>\n

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:<\/p>\n

...
\n\tfrom LegacyInput
\n\tcross join hilfstabelle
\n\twhere substring('#'+Zeichenkette+'#', n, 1)='#'
\n\t and n <= len(Zeichenkette)+2<\/code><\/p>\n

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.<\/p>\n

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.<\/p>\n

substring('#'+Zeichenkette, n+1, charindex('#',Zeichenkette+'#',n)-n)<\/code><\/p>\n

Das gesamte Statement sieht dann so aus:<\/p>\n

select\tlfdNr,
\n\t\tn-len(replace(substring('#'+Zeichenkette, 1, n), '#', '')) as "TeilNr",
\n\t\tsubstring('#'+Zeichenkette, n+1, charindex('#',Zeichenkette+'#',n)-n) as "Teilstück",
\n\t\tZeichenkette
\n\tfrom LegacyInput
\n\tcross join hilfstabelle
\n\twhere substring('#'+Zeichenkette, n, 1)='#'
\n\t and n <= len(Zeichenkette)+1<\/code><\/p>\n

Und so das Ergebnis:<\/p>\n\n\n\n\n\n\n\n
LfdNr<\/strong><\/td>\nTeilNr<\/strong><\/td>\nTeilstück<\/strong><\/td>\n<\/tr>\n
1<\/td>\n1<\/td>\n13242<\/td>\n<\/tr>\n
1<\/td>\n2<\/td>\n12<\/td>\n<\/tr>\n
1<\/td>\n3<\/td>\nblabli<\/td>\n<\/tr>\n
1<\/td>\n4<\/td>\n233242<\/td>\n<\/tr>\n
…<\/td>\n…<\/td>\n…<\/td>\n<\/tr>\n<\/table>\n

Das ist aus Datenbanksicht, aber nicht optimal. Wünschenswert wäre ein pivotiertes Ergebnis:<\/p>\n\n\n\n\n\n\n\n
LfdNr<\/strong><\/td>\nattr1<\/strong><\/td>\nattr2<\/strong><\/td>\nattr3<\/strong><\/td>\nattr4<\/strong><\/td>\nattr5<\/strong><\/td>\nattr6<\/strong><\/td>\nattr7<\/strong><\/td>\nattr8<\/strong><\/td>\nattr9<\/strong><\/td>\nattr10<\/strong><\/td>\n<\/tr>\n
1<\/td>\n13242<\/td>\n22<\/td>\nblabla<\/td>\n233242<\/td>\nblubblub<\/td>\n12321<\/td>\n1312<\/td>\n1131<\/td>\n<\/td>\n13123<\/td>\n<\/tr>\n
2<\/td>\n13332<\/td>\n12<\/td>\nblibla<\/td>\n233242<\/td>\nblubblub<\/td>\n12321<\/td>\n1312<\/td>\n1131<\/td>\n<\/td>\n13123<\/td>\n<\/tr>\n
3<\/td>\n13353<\/td>\n21<\/td>\nblabli<\/td>\n233242<\/td>\nblubblub<\/td>\n12321<\/td>\n1312<\/td>\n1131<\/td>\n<\/td>\n13123<\/td>\n<\/tr>\n
4<\/td>\n13242<\/td>\n12<\/td>\nblibli<\/td>\n233242<\/td>\nblubblub<\/td>\n12321<\/td>\n1312<\/td>\n1131<\/td>\n<\/td>\n13123<\/td>\n<\/tr>\n
…<\/td>\n…<\/td>\n…<\/td>\n…<\/td>\n…<\/td>\n…<\/td>\n…<\/td>\n…<\/td>\n…<\/td>\n…<\/td>\n…<\/td>\n<\/tr>\n<\/table>\n

Das kann man dann ganz einfach mit dem PIVOT-Konstrukt erreichen:<\/p>\n

SELECT *
\nFROM (select
\n\t\t\tLfdNr,
\n\t\t\t'attr'+convert(varchar,n-len(replace(substring('#'+Zeichenkette, 1, n), '#', ''))) as "Attribute",
\n\t\t\tsubstring('#'+Zeichenkette, n+1, charindex('#',Zeichenkette+'#',n)-n) as "Teilstück"
\n\t\tfrom LegacyInput
\n\t\tcross join hilfstabelle
\n\t\twhere substring('#'+Zeichenkette, n, 1)='#'
\n\t\t and n <= len(Zeichenkette)+1\n\t ) AS ATR\n PIVOT\n (\n MAX(\"Teilstück\")\n FOR attribute IN([attr1], [attr2], [attr3], [attr4], [attr5], [attr6], [attr7], [attr8], [attr9], [attr10])\n ) AS PVT<\/code><\/p>\n

Das komplette Beispiel steht auch zum Download<\/a> bereit. <\/p>\n","protected":false},"excerpt":{"rendered":"

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, […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[3],"tags":[],"_links":{"self":[{"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/posts\/436"}],"collection":[{"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/comments?post=436"}],"version-history":[{"count":0,"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/posts\/436\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/media?parent=436"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/categories?post=436"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.glorf.it\/blog\/wp-json\/wp\/v2\/tags?post=436"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}