Spis treści:

Kategoria:Common Table ExpressionSQL Server


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 @t nvarchar(2048) = '1,2,4,5,6,7,11,18';
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 WHEREIS NOT NULL
)
SELECTFROM 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ą:

SELECT L ,R FROM Rec

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:

NameValue
Książka5
Książka4
Książka6
Ołówek2
Zeszyt2
Zeszyt54
Zeszyt55
Zeszyt221
Zeszyt3

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:

NameValueValueNum
Kredkia,b,ca1
Kredkia,b,cb2
Kredkia,b,cc3
Gumkib,cb1
Gumkib,cc2
Mazakia,ba1
Mazakia,bb2

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 Token

Sama 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):

TextNT
A--B--C--D1A
A--B--C--D2B
A--B--C--D3C
A--B--C--D4D
E--F,G1E
E--F,G2F,G

Kategoria:Common Table ExpressionSQL Server

, 2013-12-20

Komentarze:

Anonim (2013-05-17 15:17:20)
Witam.

A jeśli chcemy wyświetlić np. tylko drugą, czwartą i piątą sekcję?
Robert (2016-09-26 14:18:32)
Trzeba je ponumerować np. przez row_number i wybrać te sekcje w klauzuli Where
hubn (2019-03-12 15:19:20)
a z innej strony - co gdybym ciąg znaków chciał mieć rozbity nie na wiersze a na kolumny? Czyli ciąg ABCD: 1. kolumna: A, 2. kolumna: B, 3. kolumna: C, 4 kolumna: D?
Dodaj komentarz
Wyślij
Ostatnie komentarze
Dzieki za te informacje. były kluczowe
Dobrze wyjaśnione, dzięki !
a z innej strony - co gdybym ciąg znaków chciał mieć rozbity nie na wiersze a na kolumny? Czyli ciąg ABCD: 1. kolumna: A, 2. kolumna: B, 3. kolumna: C, 4 kolumna: D?