Wyszukanie pierwszego i kolejnych wystąpień znaku w napisie w T-SQL

Kursant zapytał mnie dziś: Jak wyświetlić w SQL pozycje wszystkich znaków ‘-‘ w polu tabeli. Czyli jeżeli mam napis ABC-DEF-GHIJ-KL, to chcielibyśmy znaleźć pozycję znaku ‘-‘  w danym napisie, a następnie znaleźć jego dalsze wsytąpienia: 4,8, 13.

Pierwsze próby opierały się na składaniu funkcji CHARINDEX:

SELECT

CHARINDEX(’-’,’tekst-z-myslnikami’, 1) AS x1,

CHARINDEX(’-’,’tekst-z-myslnikami’, CHARINDEX(’-’,’tekst-z-myslnikami’, 1)+1) AS x2

Pierwszy CHARINDEX szuka pierwszego wystąpienia znaku począwszy od początku napisu. Drugie złożenie również szuka pozycji znaku, ale rozpoczyna o jeden znak dalej niż ustalona pozycja pierwszego wystąpienia znaku. Im więcej będę chciał znaleźć tych znaków, tym więcej muszę napisać takich złożeń. Strach się bać.

Zadziałało. Ale… kosztuje to dużej ilości pisania, można się łatwo pomylić i na dodatek zadziała dla 2 wystąpień znaku, a my chcemy również dla napisów, które mają po 3,7,10 wystąpień.

Inne pomysły to napisać własną funkcję, która to robi. Dałoby się, oczywiście, ale… ten kursant nie miał uprawnień do pisania własnych funkcji na serwerze. Wchodzą więc w grę tylko te lżejsze mechanizmy.

Chwila badania sprawy i okazało się, że można to załatwić rekurencyjnie poprzez CTE (Common Table Expressions).

Oto rozwiązanie:

Najpierw tworzę testową tabelkę i wypełniam ją danymi:

CREATE TABLE Napisy

(

id int,

txt VARCHAR(100))

GO

 

INSERT INTO Napisy VALUES(1,’ABC-DEF-GHI’)

INSERT INTO Napisy VALUES(2,’ABCE-DEFG-GHIJ’)

INSERT INTO Napisy VALUES(3,’ABCE-DEFG-GHIJ-KLMN’)

INSERT INTO Napisy VALUES(4,’A-BC-EDE-FGHI-JKLMN’)

INSERT INTO Napisy VALUES(5,’—–’)

GO

Teraz buduję CTE. CTE będzie wyświetlać w pierwszej części (tej przed UNION) z tabeli  Napisy każdy id rekordu, napis oraz dwa zera. Pierwsze zero oznacza pozycję poszukiwanego znaku, a drugie zero –  poziom zagłębienia rekurencyjnego. Rzeczywiście za pierwszym razem rekurencji jeszcze nie ma, więc można wyświetlić 0. Po prawdzie na zerowej pozycji nie ma poszukiwanego znaku, więc Wyświetlenie Pozycja=0 jest małym oszustwem, ale od czegoś trzeba zacząć.

Druga część CTE (ta po UNION ALL) będzie bazować na danych wyświetlonych przez pierwszą część CTE.

W drugim kroku CTE sięga do już przed chwilą wyświetlonych danych i próbuje wygenerować dane dla kroku zagnieżdżonego o 1 poziom bardziej. W naszym przypadku będzie to id (może go pobrać z tabeli Napisy), a potem wyświetli resztę danych napisowych znajdujących się za poszukiwanym znakiem. Wyświetlamy też numer znaku z pierwszym znalezionym wystąpieniem tego znaku. W tym celu wystarczy przeszukać wyświetlone już wcześniej przez CTE napisy i po odnalezieniu funkcją CHARINDEX wyświetlić na której pozycji był ten znak i wyświetlić napis po skróceniu o początkowy fragment. Skomplikowane co? A jednak trochę podobne do zasady indukcji matematycznej.

Drugi krok będzie się powtarzał tak długo aż w coraz to bardziej skracającym się napisie nie będzie już poszukiwanego znaku. Wtedy to funkcja CHARINDEX zwróci 0.

Korzystając z CTE otrzymamy jeden rekord z poziomem 0 i całym napisem, w którym powinniśmy szukać ‘-‘, w drugim kroku zobaczysz informację o pozycji pierwszego znalezienia znaku oraz zobaczysz napis obcięty z przodu tak, by odciąć ten znaleziony znak.

Dzieki temu w trzecim kroku (zagłebienie rekurencji 2) zobaczysz pierwsze wystąpienie poszukiwanego znaku w napisie z 2-go kroku, czyli w napisie już okrojonym. To wydawałoby się pierwsze wystąpienie znaku jest więc w rzeczywistości już drugim  wystąpieniem znaku.

W czwartym kroku (zagłebienie rekurencji 3) zobaczysz pierwsze wystąpienie poszukiwanego znaku w napisie z 3-go kroku, czyli w napisie już okrojonym. To wydawałoby się pierwsze wystąpienie znaku jest więc w rzeczywistości już trzecim wystąpieniem znaku.

Itd.

Jeżeli nie chcesz zobaczyć w wyniku sztucznego rekordu z zerami, to odfiltruj ten rekord zwykłą klauzulą WHERE podczas wykorzystywania CTE.

WITH MyCTE AS

(

SELECT

      id AS 'id’,

      txt AS 'txt’,

      0 AS 'Pozycja’,

      0 AS 'Poziom’

FROM Napisy

UNION ALL

SELECT

      n.id,

SUBSTRING(m.txt,CHARINDEX(’-’,m.txt)+1,
LEN(m.txt)-CHARINDEX(’-’,m.txt)),

      m.pozycja+CHARINDEX(’-’,m.txt,1),

      m.Poziom+1

FROM Napisy n

INNER JOIN MyCTE m

      on m.id = n.id

WHERE

      CHARINDEX(’-’,m.txt,1)>0

)

 

SELECT id, Poziom, Pozycja FROM MyCTE

WHERE Pozycja>0

ORDER BY id, pozycja

Pozostaje życzyć owocnego przeszukiwania napisów!