Przekształcenia ciągów znakowych (stringów) w SQL

Funkcje operujące na ciągach znaków, są jednymi z najczęściej stosowanych. Ich nazewnictwo jest intuicyjne i często bardzo podobne lub nawet identyczne, ze stosowanym w innych językach programowania.

W tym artykule przedstawiam najczęściej używane funkcje związane z przetwarzaniem wartości ciągów znakowych typu (’n)char/varchar.

LEFT( exp, n ) oraz RIGHT( exp, n ) – to jedne z najprostszych funkcji tekstowych. Zwracają n znaków od lewej lub prawej z wyrażenia exp na którym działa.

USE Northwind
GO

-- LEFT/RIGHT - n znaków z lewej / prawej strony

SELECT LEFT(FirstName,1) + '. ' + LastName as FullName,  
       RIGHT(HomePhone,8) as ShortPhone,
       FirstName, HomePhone 
FROM dbo.Employees

W tym przykładzie funkcja LEFT(FirstName,1) zwraca zawsze sensowną wartość – inicjał od imienia. Z kolei RIGHT (HomePhone,8) – zwróci zawsze tylko ostatnie 8 znaków. Jeśli byśmy chcieli wyciągnąć zmienną liczbę znaków np. od lewej (numer kierunkowy) – potrzebujemy wsparcia kolejnej funkcji, która nam namierzy ten zmienny punkt.

CHARINDEX ( exp1 , exp2 , start_location ) – szuka pierwszego wystąpienia ciągu znaków exp1 w wartości znakowej podanej jako argument exp2. Rozpoczyna poszukiwania od pozycji 1 (jeśli nie jest podana) lub innej określonej przez start_location. Zwraca numer znaku w którym rozpoczyna się pierwsze wystąpienie poszukiwanego stringu.

Połączmy więc wiedzę dotyczącą funkcji LEFT oraz CHARINDEX, do wyciągniecia informacji o numerze kierunkowym z kolumny HomePhone :

-- CHARINDEX namierza pierwsze wystąpienie znaku w stringu (zwraca numer)

SELECT CHARINDEX (' ', HomePhone) as FirstSpace,
       LEFT(HomePhone, CHARINDEX (' ', HomePhone)-1 ) as Direction,
       HomePhone
FROM dbo.Employees

W zagnieżdzaniu funkcji trzeba pamiętać o jednej zasadzie. Funkcja zagnieżdzona musi zwrócić odpowiedni typ danych dla argumentu funkcji zewnętrzenej. W tym przypadku funkcja CHARINDEX – zwróciła wartość typu integer, która jest oczekiwanym typem danych, drugiego argumentu funkcji LEFT (określającym liczbę znaków).

UPPER (string) oraz LOWER(string) zamienia wszystkie litery na duże lub małe.

LEN( exp ) – funkcja zwraca wartość typu integer, równej liczbie znaków (długości) wyrażenia będącego jej argumentem. Często stosowana z innymi funkcjami – np. SUBSTRING.

-- UPPER / LOWER  - WIELKIE/ male litery, LEN - długość stringu

SELECT ProductName, UPPER( ProductName ) as UpperName, 
	LOWER( ProductName ) as LowerName, 
        LEN( ProductName ) NameLength
FROM dbo.Products
WHERE LEN( ProductName ) < 10

Zauważ, że zastosowałem tutaj funkcję LEN m.in. w warunku filtracji WHERE. Trzeba być świadomym, że takie zastosowanie, spowoduje jej uruchomienie dla każdego zwróconego rekordu dwukrotnie. Po raz pierwszy, zostanie ona wykonana dla każdego wierszy tabeli dbo.Products (w WHERE). Drugi – tylko dla tych których nazwa jest krótsza niż 10 znaków (w SELECT).

Stosowanie funkcji w warunkach filtracji może mieć znaczący wpływ na wydajność zapytań (co nie oznacza, że nie możemy ich tu stosować) – więcej o tym w ostatnim rozdziale tego kursu.

SUBSTRING ( exp , start_location , n ) – zwraca fragment tekstu, liczbę n znaków z wyrażenia podanego w parametrze exp, startując od zadanego miejsca start_location. Napiszmy zapytanie wyciągające tylko nazwę domeny z pełnego adresu URL. W tym celu musimy wyciągnąć fragment tekstu z pominięciem http:// (od ósmego znaku) do pierwszego wystąpienia znaku / (licząc również od ósmej pozycji)

-- SUBSTRING - wyciąga konrketny framgent stringu  

SELECT  WebPage, 
	SUBSTRING(WebPage,8, CHARINDEX('/',webPage,8)-8 ) as OnlyDomain
FROM 
(
        -- potraktuj to jak tabelę z dwoma wierszami ;)
	SELECT 'http://www.sqlpedia.pl/pisanie-zapytan-w-jezyku-sql-kurs/' as WebPage
	UNION
	SELECT 'http://sqlpedia.pl/kurs-sql/' 

) as Pages

Łącząc ze sobą podstawowe funkcje, można osiągnąć całkiem skomplikowane przekształcenia.

RTRIM ( exp ) oraz LTRIM ( exp ) – obcięcie z prawej/lewej znaków spacji danego wyrażenia exp. Szczególnie często spotykane przy łączeniu stringów typu CHAR o stałej długości.

CREATE TABLE #tabelka
(
	opis char(10)
);

INSERT INTO #tabelka VALUES('jeden'),('dwa'),(' trzy'),(' cztery ');

-- LTRIM / RTRIM usuwa spacje z lewej / prawej strony

SELECT '!' + opis + '!' as Laczenie, 
       '!' + LTRIM( RTRIM(opis) ) + '!'  as Trimowane
FROM #tabelka;

DROP TABLE  #tabelka;

PATINDEX ( wzorzec, exp ) – wyszukiwanie pierwszego wystąpienia doapsowania maski – określonej wzorcem w danym wyrażeniu exp. Maska jest tworzona na tej samej zasadzie co porównywanie ciągów znakowych za pomocą operatora LIKE. Napiszmy zapytanie, wyciągające kod pocztowy ze stringu.

-- PATINDEX wyszukuje dopasowania wzorca w stringu 
-- i zwraca numer znaku, pierwszego wystąpienia

SELECT Adres, 
	   PATINDEX('%[0-9][0-9]-[0-9][0-9][0-9]%',Adres) as KodPocztowyStart,
	   SUBSTRING(Adres,PATINDEX('%[0-9][0-9]-[0-9][0-9][0-9]%',Adres), 6 ) as KodPocztowy
FROM
(
	-- potraktuj to jak regularną tabelę ;)
	SELECT '60-144 Poznań' as Adres 
	UNION
	SELECT 'Poznań, 60-186'
	UNION 
	SELECT 'Kod pocztowy 61-698, Poznań' 
	UNION 
	SELECT 'Poznań 61-698, Jana Pawła 16' 
 
) as Adresy

Pełną składnię tworzenia wzorców w LIKE, znajdziesz w artykule na temat filtrowania wartości znakowych w WHERE.

REPLACE ( exp , old_substring , new_substring ) – podmienia każde wystąpienie ciągu znaków old_substring na new_substring w przeszukiwanym wyrażeniu exp. Poniższy przykład, zamieni wszystkie odnalezione wystąpienia znaku „-„ na ciąg pusty czyli „”. Zauważ, że argumentem wejściowym funkcji REPLACE, będzie wynik działania RIGHT – czyli wartość kolumny HomePhone, obcięta do ośmiu znaków od prawej.

-- REPLACE podmienia wszystkie wystąpienia szukanego ciągu znaków, na inny

SELECT HomePhone , REPLACE ( RIGHT(HomePhone,8) , '-', '') as ShortPhone
FROM dbo.Employees

REVERSE ( exp ) – odbicie lustrzane stringu czyli ostatni będą pierwszymi.

-- REVERSE - lustro

SELECT REVERSE('aidepLQS') as Mirror

STUFF ( exp1, start, n, exp2 ) –usuwa n znaków wyrażenia exp1 zaczynając od pozycji określonej parametrem start. Następnie wrzuca określony ciąg znakowy exp2 do wyrażenia exp1, również od pozycji określonej w start.

-- STUFF kasuje wskazany fragment ciągu znakowego i wkleje zadany wewnątrz innego

SELECT STUFF('Agnieszka Maria Kwiatkowska', 3, 5, 'XXXXX');