Data: 14.12.2024

Funkcje tekstowe w SQL Server

Spis treści

• Funkcje tekstowe w SQL Server
  • Do czego służą funkcje tekstowe w SQL Server?
  • Funkcje łączenia i agregacji tekstu
    • Funkcja CONCAT()
    • Funkcja CONCAT_WS()
    • Funkcja STRING_AGG()
    • Operator plus (+)
  • Funkcje wyodrębniania fragmentów tekstu
    • Funkcja SUBSTRING()
    • Funkcja LEFT()
    • Funkcja RIGHT()
  • Funkcje analizy i wyszukiwania w tekście
    • Funkcja CHARINDEX()
    • Funkcja PATINDEX()
    • Funkcje LEN() i DATALENGTH()
  • Funkcje modyfikacji tekstu
    • Funkcja REPLACE()
    • Funkcja TRANSLATE()
    • Funkcja STUFF()
    • Funkcja REPLICATE()
    • Funkcje LTRIM(), RTRIM() i TRIM()
    • Funkcje UPPER() i LOWER()

W tym artykule przyjrzymy się najważniejszym funkcjom tekstowym w SQL Server, które pomogą Ci w pracy z danymi tekstowymi — ich czyszczeniu, transformacji oraz analizie.

Do czego służą funkcje tekstowe w SQL Server?

Funkcje tekstowe w SQL Server służą do przetwarzania, analizy i modyfikacji danych znakowych przechowywanych w kolumnach typu CHAR, VARCHAR, NCHAR oraz NVARCHAR.

Umożliwiają m.in.:

• łączenie ciągów znaków
• wyodrębnianie fragmentów tekstu
• wyszukiwanie określonych sekwencji w tekście
• zamianę znaków
• zmianę wielkości liter
• usuwanie zbędnych spacji

Funkcje te są kluczowe w procesach czyszczenia danych, standaryzacji formatów, budowy raportów oraz przygotowywania danych do dalszej analizy lub integracji z innymi systemami.

Funkcje łączenia i agregacji tekstu

Funkcje łączenia i agregacji tekstu umożliwiają łączenie wielu ciągów znaków w jeden ciąg.

Funkcja CONCAT()

Funkcja CONCAT() służy do łączenia (konkatenacji) wielu wartości tekstowych w jeden ciąg znaków.

Najważniejsze cechy:

• przyjmuje dowolną liczbę argumentów
• automatycznie konwertuje typy nieznakowe np. INT, DATE na typ znakowy VARCHAR
• wartości NULL traktuje jako pusty ciąg (”), a nie jako wartość specjalną NULL

Składnia: CONCAT(expression, expression, ...)

W miejsce argumentu expression podajemy tekst.

W przykładzie poniżej tworzymy etykietę produktu:

• odczytujemy kolumny Name i ProductNumber
• za pomocą funkcji CONCAT() łączymy następujące wartości tekstowe: dane z kolumny Name plus napis ” [Ref: ” plus dane z kolumny ProductNumer plus nawias kwadratowy „]”

  SELECT
      Name,
      ProductNumber,
      CONCAT(Name, ' [Ref: ', ProductNumber, ']') AS ProductLabel
  FROM Production.Product;

  Wynik:

  Name	ProductNumber	ProductLabel
  Adjustable Race	AR-5381	Adjustable Race [Ref: AR-5381]
  Bearing Ball	BA-8327	Bearing Ball [Ref: BA-8327]
  BB Ball Bearing	BE-2349	BB Ball Bearing [Ref: BE-2349]
  Headset Ball Bearings	BE-2908	Headset Ball Bearings [Ref: BE-2908]

  Funkcja CONCAT_WS()

  Funkcja CONCAT_WS() służy do łączenia wielu wartości tekstowych w jeden ciąg znaków z automatycznym wstawieniem separatora pomiędzy kolejnymi elementami. Funkcja ignoruje wartość specjalną NULL.

  Została wprowadzona od wersji SQL Server 2017.

  Składnia: CONCAT_WS(separator, expression, expression, ...)

  W argumentach:

  • w miejsce „separator” wstawiamy separator, którym mają być rozdzielone teksty np. spacja
  • w miejsce „expression” podajemy teksty, które chcemy połączyć

  W przykładzie poniżej łączymy teksty z kolumn CustomerID, ShipCountry, ShipCity i rozdzielamy je separatorem „|”.

  SELECT
      CONCAT_WS(' | ',
          CustomerID,
          ShipCountry,
          ShipCity
      ) AS ShippingLabel
  FROM Orders;

  Wynik:

  Name	ProductNumber	ProductLabel
  VINET	France	VINET [Ref: France
  TOMSP	Germany	TOMSP [Ref: Germany
  HANAR	Brazil	HANAR [Ref: Brazil
  VICTE	France	VICTE [Ref: France

  Funkcja STRING_AGG()

  Funkcja STRING_AGG() służy do agregowania wielu wartości tekstowych w jeden ciąg znaków, z możliwością określenia separatora pomiędzy elementami.

  Została wprowadzona w SQL Server 2017 jako rozwiązanie problemu konkatenacji wierszy w ramach grupy.

  Składnia funkcji:

  STRING_AGG ( expression, separator )
  [ WITHIN GROUP ( ORDER BY order_expression [ ASC | DESC ] ) ]

  Parametry:

  • expression – tekstowa wartość z kolumny lub wyrażenie zawierające tekst
  • separator – znak lub ciąg znaków oddzielający elementy
  • WITHIN GROUP (ORDER BY …) – opcjonalne określenie kolejności łączenia elementów

  W przykładzie poniżej:

  • grupujemy dane według kolumny ProductID
  • dla każdego id produktu tworzymy listę nazw półek z kolumny Shelf

  SELECT
      ProductID,
      STRING_AGG(Shelf, ', ') AS Półki
  FROM Production.ProductInventory
  GROUP BY ProductID;

  Wynik:

  ProductID	Półki
  1	A, B, A
  2	A, B, A
  3	A, B, A
  4	A, B, A

  W przykładzie poniżej:

  • klauzula GROUP BY grupuje wszystkie rekordy z tabeli według nazwy kraju (Country). Każdy kraj stanie się docelowo jednym wierszem w raporcie
  • Funkcja STRING_AGG(City, ', ') – funkcja agregująca, która „skleja” nazwy miast z danej grupy w jeden ciąg tekstowy, wstawiając między nie wybrany separator (przecinek i spacja)
  • kod WITHIN GROUP (ORDER BY City) sortuje elementy wewnątrz sklejonego tekstu.

  SELECT
      Country,
      STRING_AGG(City, ', ') WITHIN GROUP (ORDER BY City) AS Miasta
  FROM Suppliers
  GROUP BY Country;

  Wynik:

  Country	Miasta
  Australia	Melbourne, Sydney
  Brazil	Sao Paulo
  Canada	Montréal, Ste-Hyacinthe
  Denmark	Lyngby

  Operator plus (+)

  W SQL Server operator plus (+) służy nie tylko do dodawania liczb, ale również do konkatenacji, czyli łączenia dwóch lub więcej tekstów (ciągów znaków) w jeden.

  W przykładzie łączymy imię i nazwisko i wstawiamy między nie spację

  SELECT
      FirstName,
      LastName,
      FirstName + ' ' + LastName as ImNazw
  FROM Person.Person;

  Wynik:

  FirstName	LastName	ImNazw
  Syed	Abbas	Syed Abbas
  Catherine	Abel	Catherine Abel
  Kim	Abercrombie	Kim Abercrombie

  W SQL Server operator konkatenacji + zwraca wartość NULL, jeśli którykolwiek z łączonych elementów ma wartość NULL, co może prowadzić do nieoczekiwanej utraty całego wyniku tekstowego. Aby temu zapobiec, możemy stosować funkcję ISNULL() lub COALESCE() lub używać zamiast plus (+) funkcji CONCAT(), która automatycznie traktuje NULL jako pusty ciąg znaków.

  W przykładzie poniżej:

  • w kolumnie Imiona1 otrzymujemy w wyniku NULL dla wszystkich rekordów, w których w kolumnie FirstName lub w kolumnie Middlename była wartość specjalna NULL
  • w kolumnie Imiona2 otrzymujemy prawidłowe dane, gdyż w przypadku pojawienia się wartości NULL w kolumnie FirstName lub w kolumnie LastName, funkcja ISNULL zamienia NULL na pusty ciąg

  SELECT
      FirstName,
      MiddleName,
      FirstName + ' ' + MiddleName as Imiona1,
      ISNULL(FirstName, '') + ISNULL(' ' + MiddleName, '') AS Imiona2
   FROM Person.Person;

  Wynik:

  FirstName	MiddleName	Imiona1	Imiona2
  Syed	E	Syed E	Syed E
  Catherine	R.	Catherine R.	Catherine R.
  Kim	NULL	NULL	Kim

  Funkcje wyodrębniania fragmentów tekstu

  Funkcje wyodrębniania fragmentów tekstu umożliwiają pobieranie określonych części ciągu znaków na podstawie pozycji, długości lub wzorca.

  Funkcja SUBSTRING()

  Funkcja SUBSTRING służy do wyodrębniania fragmentu tekstu (ciągu znaków) z dłuższej wartości znakowej.

  Składnia funkcji:

  SUBSTRING (expression , start , length)

  Parametry:

  • expression – kolumna lub wyrażenie tekstowe, z którego wyodrębniamy tekst
  • starting_position – pozycja początkowa (liczona od 1) ciągu znaków, które chcemy wyodrębnić
  • length – liczba znaków do wyodrębnienia

  W przykładzie zaczynając od piątego znaku wyodrębniamy dwa znaki:

  SELECT
      Name,
      ProductNumber,
      SUBSTRING(ProductNumber, 5, 2) AS Seria
  FROM Production.Product

  Wynik:

  Name	ProductNumber	Seria
  Adjustable Race	AR-5381	38
  Bearing Ball	BA-8327	32
  BB Ball Bearing	BE-2349	34
  Headset Ball Bearings	BE-2908	90

  Funkcja LEFT()

  Funkcja LEFT() służy do wyodrębniania określonej liczby znaków z początku łańcucha tekstowego (z lewej strony).

  Składnia:

  LEFT(expression, number_of_chars )

  W argumentach podajemy:

  • expression – tekst, z którego chcemy wyodrębnić znaki
  • liczba znaków, które chcemy wyodrębnić

  W przykładzie wyodrębniamy dwa znaki od lewej:

  SELECT
      Name,
      ProductNumber,
      LEFT(ProductNumber, 2) AS CategoryCode
  FROM Production.Product;

  Wynik:

  Name	ProductNumber	CategoryCode
  Adjustable Race	AR-5381	AR
  Bearing Ball	BA-8327	BA
  BB Ball Bearing	BE-2349	BE
  Headset Ball Bearings	BE-2908	BE

  Funkcja RIGHT()

  Funkcja RIGHT() służy do zwracania określonej liczby znaków z prawej strony łańcucha tekstowego.

  Składnia:

  RIGHT(expression, expression )

  W argumentach podajemy:

  • expression pierwszy argument – tekst, z którego chcemy wyodrębnić znaki
  • expression drugi argument – liczba znaków, które chcemy wyodrębnić

  W przykładzie wyodrębniamy dwa znaki od prawej:

  SELECT
      CardNumber,
      RIGHT(CardNumber, 4) AS Last4Digits
  FROM Sales.CreditCard;

  Wynik:

  CardNumber	Last4Digits
  11111000471254	1254
  11111002034157	4157
  11111005230447	0447
  11111007955171	5171

  Funkcje analizy i wyszukiwania w tekście

  Funkcja CHARINDEX()

  Funkcja CHARINDEX() służy do wyszukiwania pozycji (indeksu) podciągu znaków w innym ciągu tekstowym. Zwraca informację, na której pozycji rozpoczyna się szukany ciąg znaków.

  W przykładzie sprawdzamy, czy w nazwach produktów fraza „Bike” występuje na początku nazwy (branding główny), czy w dalszej części (cecha dodatkowa).

  SELECT
      Name,
      CHARINDEX('Bike', Name) AS PositionInName
  FROM Production.Product
  WHERE CHARINDEX('Bike', Name) > 0;

  Wynik:

  Name	PositionInName
  All-Purpose Bike Stand	13
  Bike Wash – Dissolver	1
  Hitch Rack – 4-Bike	16
  Mountain Bike Socks, L	10
  Mountain Bike Socks, M	10

  Więcej informacji na temat funkcji CHARINDEX() znajdziesz w artykule MS SQL funkcja CHARINDEX()

  Funkcja PATINDEX()

  Funkcja PATINDEX() służy do wyszukiwania wzorca tekstowego w ciągu znaków i zwraca pozycję (indeks) pierwszego dopasowania.

  W przykładzie wyszukujemy te produkty, w przypadku których ostatni znak numeru produktu dowolna litera.

  SELECT ProductNumber,
         Name,
         PATINDEX('%[A-Z]', ProductNumber) as CzyLitera
  FROM Production.Product
  WHERE PATINDEX('%[A-Z]', ProductNumber) > 0;

  Wynik:

  ProductNumber	Name	CzyLitera
  PA-187B	Paint – Black	7
  PA-361R	Paint – Red	7
  PA-529S	Paint – Silver	7
  PA-632U	Paint – Blue	7
  PA-823Y	Paint – Yellow	7
  HL-U509-R	Sport-100 Helmet, Red	9
  SO-B909-M	Mountain Bike Socks, M	9

  Więcej informacji na temat funkcji PATINDEX() znajdziesz w artykule MS SQL funkcja PATINDEX()

  Funkcje LEN() i DATALENGTH()

  Funkcja LEN() służy do zwracania liczby znaków w wyrażeniu tekstowym. Spacje końcowe nie są zliczane. Aby zliczyć również spacje końcowe należy użyć funkcji DATALENGTH().

  Składnia funkcji LEN():

  LEN(expression)

  W miejsce expression wstawiamy ciąg tekstowy, w którym chcemy policzyć znaki.

  W przykładzie zliczamy ile znaków ma hasło:

  SELECT PasswordSalt, LEN(PasswordSalt) as DłHasła
  FROM Person.Password;

  Wynik:

  PasswordSalt	DłHasła
  bE3XiWw=	8
  EjJaC3U=	8
  wbPZqMw=	8
  PwSunQU=	8

  Funkcje modyfikacji tekstu

  Funkcja REPLACE()

  Funkcja REPLACE() służy do zamiany wszystkich wystąpień określonego ciągu znaków na inny ciąg znaków w obrębie wyrażenia tekstowego.

  Składnia funkcji:

  REPLACE (expression_to_be_searched, search_expression, replacement_expression)

  Parametry:

  • expression_to_be_searched – tekst, w którym ma nastąpić zamiana
  • search_expression – fragment tekstu, który ma zostać zastąpiony
  • replacement_expression – tekst, który zastąpi znaleziony fragment

  W przykładzie w numerze produktu zastępujemy litery „AR” literami „BA”.

  SELECT
      Name,
      ProductNumber AS OryginalnyKod,
      REPLACE(ProductNumber, 'AR', 'BA') AS ZmienionyKod
  FROM Production.Product

  Funkcja TRANSLATE()

  Funkcja TRANSLATE() służy do zamiany pojedynczych znaków w tekście na inne znaki, zgodnie z mapowaniem pozycyjnym. Jest to operacja znak-po-znaku, a nie zamiana fragmentów tekstu.

  Składnia funkcji: TRANSLATE(inputString, characters, translations)

  Parametry:

  • input_string – tekst, w których chcemy zrobić zmianę
  • characters – zestaw znaków do podmiany
  • translations – znaki zastępujące (pozycja odpowiada pozycji w characters)

  W argumentach characters i translations musi być tyle samo znaków.

  W przykładzie zamieniamy znaki narodowe na ich podstawowe odpowiedniki.

  SELECT
      ContactName,
      TRANSLATE(ContactName, 'éãöüä', 'eaoua') AS Name
  FROM Customers
  WHERE ContactName LIKE '%[éãöüä]%';

  Wynik:

  ContactName	BezZnakowNar
  Frédérique Citeaux	Frederique Citeaux
  Martine Rancé	Martine Rance
  José Pedro Freyre	Jose Pedro Freyre
  André Fonseca	Andre Fonseca
  Sergio Gutiérrez	Sergio Gutierrez
  Rita Müller	Rita Muller

  Funkcja STUFF()

  Funkcja STUFF() służy do modyfikowania ciągów znaków poprzez usunięcie określonej liczby znaków i wstawienie w ich miejsce nowego tekstu.

  Składnia funkcji:

  STUFF(expression_to_be_searched, starting_position, number_of_chars, replacement_expression)

  Parametry:

  • expression_to_be_searched – tekst, w którym chcemy zrobić zmianę
  • starting_position – pozycja, od której zaczynamy modyfikację
  • number_of_chars – liczba znaków do usunięcia
  • replacement_expression – tekst wstawiany w miejsce usuniętych znaków

  W przykładzie w numerze produktu dwuznakowy prefix zamieniamy na słowo „PROD”.

  SELECT
      ProductNumber,
      STUFF(ProductNumber, 1, 2, 'PROD') AS NewCode
  FROM Production.Product;

  Wynik:

  ProductNumber	NewCode
  AR-5381	PROD-5381
  BA-8327	PROD-8327
  BB-7421	PROD-7421
  BB-8107	PROD-8107

  Funkcja REPLICATE()

  Funkcja REPLICATE() służy do wielokrotnego powielania określonego ciągu znaków.

  Składnia:

  REPLICATE(expression, expression)

  Parametry:

  • expression pierwszy parametr – podajemy tekst, który chcemy powielić
  • expression drugi parametr – podajemy ile razy tekst ma zostać powielony

  W przykładzie powielamy gwiazdkę tyle razy, jaka jest wartość w kolumnie Rating

  SELECT
      ReviewerName,
      Rating,
      REPLICATE('*', Rating) AS Stars
  FROM Production.ProductReview;

  Wynik:

  ReviewerName	Rating	Stars
  John Smith	5	*****
  David	4	****
  Jill	2	**
  Laura Norman	5	*****

  Funkcje LTRIM(), RTRIM() i TRIM()

  Funkcje LTRIM(), RTRIM() i TRIM() służą do usuwania zbędnych znaków (najczęściej spacji) z początku i/lub końca tekstu:

  • funkcja LTRIM() usuwa znaki z lewej strony tekstu
  • funkcja RTRIM() usuwa znaki z prawej strony tekstu
  • funkcja TRIM() usuwa znaki z prawej i z lewej strony tekstu. Została wprowadzona od wersji SQL Server 2017

  W parametrze każdej z tych funkcji podajemy tekst, z którego chcemy usunąć znaki.

  W przykładzie podajemy tekst ze spacjami ” Laura ”

  SELECT
    '  Laura   ' as ZeSpac,
     LTRIM('  Laura   ') as SpLewa,
     RTRIM('  Laura   ') as SpPrawa,
     TRIM('  Laura   ') as SpObydwie

  ZeSpac	SpLewa	SpPrawa	SpObydwie
  Laura	Laura	Laura	Laura

  Funkcje UPPER() i LOWER()

  Funkcje UPPER() i LOWER() służą do zmiany wielkości liter w tekście.

  • za pomocą funkcji UPPER() zmieniamy litery na wielkie
  • za pomocą funkcji LOWER() zmieniamy litery na małe

  SELECT FirstName,
         UPPER(FirstName) as wielkie,
         LOWER(FirstName) as małe
  FROM Person.Person;

  Wynik:

  FirstName	wielkie	małe
  Syed	SYED	syed
  Catherine	CATHERINE	catherine
  Kim	KIM	kim

  Opisywane funkcje prezentujemy również w ramach naszych kursów SQL.