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

19-Lut-2011

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!

Komentarze:

  1. Mobilo » Blog Archive » Parsowanie kolumny SQL za pomocą CTE napisał,

    […] Wyszukiwanie pierwszego i kolejnych wystąpień znaku w napisie Kategoria: Bez kategorii, SQL You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site. Odpowiedz Kliknij tutaj, aby anulować odpowiadanie. […]

  2. Jarek napisał,

    Witam

    Potrzebuję wyszukać rekordy które mają w polu opis tekst zawarty w więcej niż 13 liniach. Czyli taki który ma co najmniej 13 znaków końca linii. Jak to wyszukać?

Dodaj komentarz:

Autor: Rafał Kraik