Parsowanie kolumny SQL za pomocą CTE

2-kwi-2011

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:

CREATE TABLE Napisy
(
id int,
txt VARCHAR(100))
GO

INSERT INTO Napisy VALUES(1,'Janek,Zosia,Gosia')
INSERT INTO Napisy VALUES(2,'Zbyszek,Agata,Lukasz,Maciek')

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

SELECT * FROM Napisy

SELECT
 id AS 'id',
 txt AS 'txt',
 SUBSTRING(txt,1,charindex(',',txt+',')-1) AS 'imie',
 SUBSTRING(txt,charindex(',',txt+',')+1, len(txt)) AS reszta,
 0 AS Poziom
FROM Napisy

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.

 WITH MyCTE AS
(
SELECT
 id AS 'id',
 txt AS 'txt',
 SUBSTRING(txt,1,charindex(',',txt+',')-1) AS 'imie',
 SUBSTRING(txt,charindex(',',txt+',')+1, len(txt)) AS reszta,
 0 AS Poziom
FROM Napisy
UNION ALL
SELECT
 n.id,
 m.reszta,
 SUBSTRING(m.reszta,1,charindex(',',m.reszta+',')-1),
 SUBSTRING(m.reszta,charindex(',',m.reszta+',')+1, len(m.reszta)) ,
 m.Poziom+1
FROM Napisy n
INNER JOIN MyCTE m
 on m.id = n.id
WHERE
m.reszta<>''
)

SELECT * FROM MyCTE
order by id,poziom

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:

WITH MyCTE AS
(
SELECT
 id AS 'id',
 txt AS 'txt',
 SUBSTRING(txt,1,charindex(',',txt+',')-1) AS 'imie',
 SUBSTRING(txt,charindex(',',txt+',')+1, len(txt)) AS reszta,
 0 AS Poziom
FROM Napisy
UNION ALL
SELECT
 n.id,
 m.reszta,
 SUBSTRING(m.reszta,1,charindex(',',m.reszta+',')-1),
 SUBSTRING(m.reszta,charindex(',',m.reszta+',')+1, len(m.reszta)) ,
 m.Poziom+1
FROM Napisy n
INNER JOIN MyCTE m
 on m.id = n.id
WHERE
m.reszta<>''
)