SELECT Explanation, Example FROM Pro.Knowledge
FacebookRSS

Funkcje tekstowe w SQL

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

FN_Strings_01_LEFT
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

FN_Strings_02_charindex
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

FN_Strings_03_UPPER
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

FN_Strings_04_SUBSTRING
Łą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;

FN_Strings_05_TRIM


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

FN_Strings_06_PATINDEX
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

FN_Strings_07_REPLACE


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

-- REVERSE - lustro
 
SELECT REVERSE('aidepLQS') as Mirror

FN_Strings_08_REPLACE


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');

FN_Strings_09_STUFF

10 thoughts on “Funkcje tekstowe w SQL

  1. Ciekawszy przykład z funkcją stuff

    select stuff( FirstName + N' ' + LastName, 1, charindex( N' ', FirstName + N' ' + LastName ) - 1, replicate( N'X', len( FirstName ) ) ) as HiddenName
    from dbo.Employees

    a ciekawsze jeszcze jest wykorzystanie do klejenia stringów ;)

    Świetny kurs!!!

  2. Może to Pas jakoś wyjaśnić, dlaczego LEN(RTRIM) jest większe niż LEN(LTRIM)?

    DECLARE @pin varchar(100)

    SET @pin = ‚ 0123456789 ‚

    SELECT
    LEN(@pin) AS Len_pin,
    LEN(LTRIM(@pin)) AS Len_Ltrim,
    LEN(RTRIM(@pin)) AS Len_Rtrim,
    LEN(RTRIM(LTRIM(@pin))) As Len_LRtrim,
    LEN(LTRIM(RTRIM(@pin))) As Len_RLtrim

  3. Mam taki problem. W polu tabeli bazy jest sformatowany tekst np:
    cos tamcos tam dalej itp
    Jak się tego pozbyć, żeby te znaki formatowania po eksporcie do excela
    nie były widoczne ?
    Oprócz żmudnego REPLACE(tekst,”,”)

  4. Co tu jest źle ? Pole Tsk_Content zawiera tekst ze znakami formatowania. Chcę je usunąć, żeby na raporcie nie były widoczne.
    DECLARE @HTMLText varchar (max)
    DECLARE @Start INT
    DECLARE @End INT
    DECLARE @Length INT

    SELECT
    Usr_Name + ‚ ‚ + Usr_SurName Utworzyl,
    Cus_Name1 Firma,
    [Tsk_Created] DataUtworzenia,
    SET @HTMLText = Tsk_Content
    SET @Start = CHARINDEX(”,@HTMLText,CHARINDEX(‚ 0 AND @End > 0 AND @Length > 0
    BEGIN
    SET @HTMLText = STUFF(@HTMLText,@Start,@Length,”)
    SET @Start = CHARINDEX(”,@HTMLText,CHARINDEX(‚<',@HTMLText))
    SET @Length = (@End – @Start) + 1
    END
    LTRIM(RTRIM(@HTMLText)) as Tresc

    FROM [ERP].[TaskNote] WITH(NOLOCK)
    LEFT JOIN [ERP].NoteType WITH(NOLOCK) ON Nty_Id = Tsk_NoteTypeId
    JOIN [ORG].Customer WITH(NOLOCK) ON Cus_Id = Tsk_CustomerId
    JOIN [Aerkonf.Configuration].[Con].[User] WITH(NOLOCK) ON Usr_Id = Tsk_CreatedByUserId
    WHERE Tsk_NoteTypeId=26 AND Tsk_Created BETWEEN {Param1} AND {Param2}
    AND Tsk_CreatedByUserId={Param3}

    • Nie możesz mieszać w ten sposób logiki biznesowej z zapytaniem, SQL to nie jest język proceduralny. Spróbuj podejść do tego interacyjnie, pozagnieżdżaj te funkcje i rozwiązuj jeden problem na raz…

Leave a comment

Twój adres email nie zostanie opublikowany.

Uzupełnij równanie (SPAM protection) *