Parsowanie kolumny SQL za pomocą CTE

Załóżmy, że w kolumnie przechowywana jest lista napisów np imion porozdzielana przecinkami. Chcielibyśmy jedną komendą SQL parsować ten napis i wyłuskać z niego imiona, zwracając je w postaci tabeli. W tym artykule o tym jak można to zrobić bez pisania procedur czy funkcji, ale z wykorzystaniem CTE.

Zacznijmy od utworzenia testowej tabeli i wypełnienia jej danymi:

Kolejnym krokiem będzie stworzenie wyrażenia Common Table Expression (CTE). Ale przyjrzyjmy się najpierw prostym zapytaniom

Wyrażenia SUBSTRING wycinają

• pierwsze imię z napisu
• resztę bez pierwszego imienia

No dobrze, ale wycięliśmy tylko jedno imię, a co z resztą!? Trzeba by było dobudować do naszego zapytania rekurencję, a tę możliwość daje nam CTE.

 Na uwagę zasługuje druga część zapytania po union all.

• Pobieram rekord z tabeli Napisy n łącząc go z odwołaniem do MyCTE, co daje mi właśnie rekurencję! Rekurencja odbywa się dla każdego rekordu oddzielnie, bo wymaga tego warunek m.id=n.id
• Wycinanie pierwszego imienia odbywa się na podstawie listy imion w „reszcie” imion z poprzedniego poziomu rekurencji
• Tekst pozostały po usunięciu imienia zapamiętuję w nowej reszcie
• Wykonuję tę rekurencję tak długo, jak długo reszta napisu jest niepusta
• Kolumna poziom informuje mnie jedynie o tym, na którym poziomie rekurencji powstał dany rekord.

Jeżeli popatrzysz na wynik CTE wycinającego kolejne części napisu rozdzielanego przecinkami, widać, że reszta jest z każdym poziomem coraz krótsza, a wycinane imię jest zawsze pierwszym imieniem z listy. Poziom informuje na którym etapie wykonaliśmy to wycięcie.

Gdyby teraz chodziło o utworzenie e-maili na podstawie takiej listy, to rozważ delikatną zmianę ostatniego zapytania:

CTE dlatego się chwali, że szybko niejeden problem rozwali!

Więcej o CTE na mobilo (mobilo24):

• Wyszukiwanie pierwszego i kolejnych wystąpień znaku w napisie