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<>''
)
SELECT imie+'@mojapoczta.pl' FROM MyCTE
CTE dlatego się chwali, że szybko niejeden problem rozwali!
Więcej o CTE na mobilo (mobilo24):
