Jak w SQL Server rozdzielić tekst danym separatorem
Odpowiednik String.Split w SQL Server
Problem rozważany tym razem jest następujący. Powiedzmy, że mamy taki oto tekst: 1,2,5,7,9,22. Wiemy, że są to identyfikatory oddzielone przecinkiem i musimy je jakoś z tego tekstu wyłuskać. Pierwsza myśl, jaka mi przyszła do głowy to skorzystanie z kodu zarządzanego .NET. Jest tam dostępna metoda wymieniona w nagłówku, która służy właśnie takim celom. Możemy zatem stworzyć moduł .NET skompilowany do pliku DLL, wrzucić go do SQL Server i po sprawie. Chwila namysłu sprawiła, że moim oczom ukazała się inna możliwość, wystarczająco dobra, aby nadawała się do tradycyjnych zastosowań. I nie mam tu na myśli skryptu z pętlami, tylko w miarę prostą instrukcję (tak mi się przynajmniej wydaje, że prostą).
Użycie Common Table Expression do rozbicia łańcucha znaków
Zasada działania jest dość prosta i opiera się na moliwości rekurencyjnych wywołań CTE. Pierwsza pętla pobiera wartość z lewej strony tekstu, aż do separatora, zostawiając do dalszych wywołań rekurencyjnych tekst znajdujący się po prawej stronie separatora. Operacja wywoływana jest rekurencyjnie dla tekstu po prawej stronie. Znów z tego fragmentu brana jest część od lewej do separatora, reszta przekazywana do dalszych wywołań rekurencyjnych. Rekurencja kończy się wtedy, gdy braknie tekstu lub gdy nie ma już separatorów.
Przeanalizujmy tekst 1|2|6 i sposób działania algorytmu. Szukamy separatora, którym jest znak |. Pierwszy taki znak od lewej znajduje się na pozycji 2 (indeks liczony od 1). Zwracamy zatem część na lewo od separatora, czyli 1, a do dlaszej obróbki pozostawiamy prawą część separatora, czyli 2|6. W pozostałej części znów szukamy separatora, znów jest to pozycja numer 2. Do zwróconej wartości 1 dochodzi teraz wartość 2, natomiast do dalszej obróbki trafia 6. Tu już nie ma separatora, więc zwracana jest cała wartość. Zwrócone zostały więc pojedyncze wartości 1, 2 i 6.
Przyjrzyjmy się teraz przykładowej implementacji tego algorytmu zaprezentowanej poniżej:
DECLARE @s nchar(1)=',';
WITH Rec AS (
SELECT
CASE WHEN CHARINDEX(@s, @t)=0 THEN @t
ELSE LEFT(@t, CHARINDEX(@s, @t)-1) END L,
CASE WHEN CHARINDEX(@s, @t)=0 THEN NULL
ELSE RIGHT(@t, LEN(@t)-CHARINDEX(@s, @t)) END R
UNION ALL
SELECT
CASE WHEN CHARINDEX(@s, R)=0 THEN R
ELSE LEFT(R, CHARINDEX(@s, R)-1) END L,
CASE WHEN CHARINDEX(@s, R)=0 THEN NULL
ELSE RIGHT(R, LEN(R)-CHARINDEX(@s, R)) END R
FROM Rec WHERE R IS NOT NULL
)
SELECT L FROM Rec
Jeżeli ktoś chce sprawdzić, jak zachowuje się parametr przetwarzany w każdym kroku rekurencji może zmodyfikować ostatnią instrukcję na następującą:
Rozbicie wielu rekordów
Poprzedni przykład dotyczył rozbicia jednego łańcucha. Wydaje się, że wykonanie poprzedniej procedury wymaga użycia kursora lub zastosowania mechanizmu dla każdego z elementów oddzielnie. Nic bardziej mylnego! Podobny sposób można zastosować dla całego zbioru, co pokazuje poniższy przykład:
SELECT * INTO #Temp FROM (VALUES ('Książka', '5;4;6'), ('Zeszyt', '3;2;54;55;221'), ('Ołówek', '2')) T(Name, Value); WITH Processor AS ( SELECT Name,CASE WHEN CHARINDEX(';', Value)=0 THEN Value ELSE LEFT(Value, CHARINDEX(';', Value)-1) END L, CASE WHEN CHARINDEX(';', Value)=0 THEN NULL ELSE RIGHT(Value, LEN(Value)-CHARINDEX(';', Value)) END Value FROM #Temp UNION ALL SELECT Name, CASE WHEN CHARINDEX(';', Value)=0 THEN Value ELSE LEFT(Value, CHARINDEX(';', Value)-1) END L, CASE WHEN CHARINDEX(';', Value)=0 THEN NULL ELSE RIGHT(Value, LEN(Value)-CHARINDEX(';', Value)) END Value FROM Processor WHERE Value IS NOT NULL ) SELECT Name, L Value FROM Processor ORDER BY Name Po wykonaniu powyższego skryptu dostaniemy następujący rezultat:
Name Value Książka 5 Książka 4 Książka 6 Ołówek 2 Zeszyt 2 Zeszyt 54 Zeszyt 55 Zeszyt 221 Zeszyt 3 Funkcja wielokrotnego użytku
Poniżej przedstawiono różne możliwe implementacje funkcji Split.
Wersja 1, rozbicie po jednym znaku
Jeżeli operacja rozdzielania tekstu na pojedyncze wartości jest wykonywana często, warto rozbić sobie to zadanie na mniejsze klocki. Popatrzmy na przykład funkcji, która mogłaby realizować to zadanie:
CREATE FUNCTION Split(@param nvarchar(MAX), @separator varchar(1)) RETURNS TABLE AS RETURN ( WITH Processor AS (SELECT CASE WHEN CHARINDEX(@separator, @param)=0 THEN @param ELSE LEFT(@param, CHARINDEX(@separator, @param)-1) END L, CASE WHEN CHARINDEX(@separator, @param)=0 THEN NULL ELSE RIGHT(@param, LEN(@param)-CHARINDEX(@separator, @param)) END Value UNION ALL SELECT CASE WHEN CHARINDEX(@separator, Value)=0 THEN Value ELSE LEFT(Value, CHARINDEX(@separator, Value)-1) END L, CASE WHEN CHARINDEX(@separator, Value)=0 THEN NULL ELSE RIGHT(Value, LEN(Value)-CHARINDEX(@separator, Value)) END Value FROM Processor WHERE Value IS NOT NULL ) SELECT L Value, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) Num FROM Processor ) Po utworzeniu takiej funkcji rozdzielanie tekstu z wykorzystaniem separatora wydaje się już dziecinnie proste:
SELECT * INTO #temp FROM (VALUES ('Kredki', 'a,b,c'), ('Gumki', 'b,c'), ('Mazaki', 'a,b')) T(Name, Value); SELECT * FROM #temp T CROSS APPLY dbo.Split(Value, ',');W wyniku tej prostej instrukcji dostaniemy:
Name Value Value Num Kredki a,b,c a 1 Kredki a,b,c b 2 Kredki a,b,c c 3 Gumki b,c b 1 Gumki b,c c 2 Mazaki a,b a 1 Mazaki a,b b 2 Dodatkowa kolumna Num może się okazać bardzo przydatna jeżeli chcemy utrzymać porządek elementów lub łączyć wiele grup rozbijanych ciągów znaków. Trzeba pamiętać, że SQL Server nie gwarantuje kolejności zwracanych wyników jeżeli nie wskażemy tego jawnie operatorem ORDER BY.
Wersja 2, rozbicie po wielu znakach
Zdarza się, że separatorem jest podwójny znak, np. [przecinek][spacja]. W takim przypadku można bezkarnie skopiować poniższy kod i na jego podstawie utworzyć odpowiednią funkcję:
CREATE FUNCTION Split(@text nvarchar(MAX),@separator nvarchar(MAX)) RETURNS TABLE AS RETURN WITH Indexed AS ( SELECT 1 N, CAST(1 AS bigint) S, CHARINDEX(@separator, @text, 1) E WHERE @text IS NOT NULL UNION ALL SELECT N+1, E+LEN(@separator), CHARINDEX(@separator, @text, E+LEN(@separator)) FROM Indexed WHERE E>S ), Token AS ( SELECT N, SUBSTRING(@text, S, CASE WHEN E=0 THEN LEN(@text) ELSE E-S END) T FROM Indexed ) SELECT * FROM TokenSama fukcja może być wywoływana następująco:
WITH SampleSourceTable AS (SELECT * FROM (VALUES ('A--B--C--D'),('E--F,G')) T(Text)) SELECT * FROM SampleSourceTable CROSS APPLY Split(Text,'--')Wynik jest następujący (warto zwrócić uwagę na rozbicie po dwóch znakach):
Text N T A--B--C--D 1 A A--B--C--D 2 B A--B--C--D 3 C A--B--C--D 4 D E--F,G 1 E E--F,G 2 F,G Kategoria:Common Table ExpressionSQL Server
Paweł Dyl, 2013-12-20
Dodaj komentarzWyślijOstatnie artykułyOstatnie komentarzea nawet:wij := min(wij, wil + wlj)
;)Dzieki za rozjasnienie zagadnienia upsert. wlasnie sie ucze programowania :).Przydatne ;)Co się stanie gdy spróbuję wyszukać:
SELECT * FROM NV_Airport WHERE Code='SVO'
SELECT * FROM V_Airport WHERE Code=N'SVO'
(odwrotnie są te N-ki)
Będzie konwersja czy nie znajdzie żadnego rekordu?
Komentarze:
A jeśli chcemy wyświetlić np. tylko drugą, czwartą i piątą sekcję?