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');
A jak sprawdzic zeby funkcja podawala dowolna ( taka jaka wystepuje ) ilosc znakow bialych i pogrupowanie ich np po spacji entarach itd .
Da sie cos takiego rozkminc ?
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…
Chodziło mi o znaki „„
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,”,”)
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
LEN jest funkcją która domyślnie obcina białe znaki po prawej stronie, porównaj z tym – DATALENGTH(@pin)
Dzięki, też już do tego doszedłem. Ciekawe ilu jest takich jak ja byłem, nieświadomych działania tej podstawowej funkcji?
Wszyscy się uczymy … cały czas 😉
Ciekawszy przykład z funkcją stuff
a ciekawsze jeszcze jest wykorzystanie do klejenia stringów 😉
Świetny kurs!!!
Fajne 🙂 !