Narcis Miclaus

7 min de citit

SQL Server, de la zero Lecția 14 / 40

Funcții de string: STRING_AGG, STRING_SPLIT, TRIM

Manipulare modernă a stringurilor în T-SQL. Concatenare, splitting, pattern matching, trimming, plus moartea funcțiilor CLR pentru lucrul cu stringuri.

Manipularea stringurilor în SQL Server a trecut de la „scrie-ți propria funcție CLR” la „există un built-in pentru asta” undeva între 2016 și 2022. Lecția de astăzi este state-of-the-art-ul actual: funcțiile pe care chiar le vei folosi în T-SQL modern pentru nume, adrese, coduri SKU, importuri CSV și celelalte patruzeci de locuri unde apar stringuri în datele Runehold.

Concatenare: CONCAT și CONCAT_WS

Operatorul + concatenează stringuri, dar are două capcane:

  1. NULL + orice este NULL. O singură valoare null anulează tot rezultatul.
  2. Amestecul de tipuri necesită cast-uri explicite: 'Order #' + CAST(OrderId AS NVARCHAR(20)).

CONCAT() rezolvă ambele:

-- NULL-safe, cast automat
SELECT CONCAT('Order #', o.OrderId, ' - €', o.Total, ' from ', c.Name) AS description
FROM Sales.Orders AS o
JOIN Sales.Customer AS c ON c.CustomerId = o.CustomerId;

CONCAT tratează NULL ca pe un string gol și convertește implicit argumentele care nu sunt stringuri. Mult mai sigur.

CONCAT_WS — With Separator — adaugă un delimitator între fiecare argument non-NULL:

SELECT CONCAT_WS(', ',
                 c.Name,
                 c.AddressLine1,
                 c.AddressLine2,    -- rândurile NULL sunt sărite în tăcere
                 c.PostalCode,
                 c.City,
                 c.Country) AS mailing_label
FROM Sales.Customer AS c;

Fără virgule la coadă, fără delimitatori dubli când un câmp este NULL. Etichete de adresă, rânduri CSV, breadcrumb-uri — CONCAT_WS este construit exact pentru astfel de lucruri.

STRING_AGG: agregă rânduri într-un string delimitat

Cealaltă jumătate a superputerii CSV. Combină mai multe rânduri ale unei coloane într-un singur string:

-- Listează toate produsele din fiecare comandă, separate prin virgulă
SELECT o.OrderId,
       STRING_AGG(p.Name, ', ') WITHIN GROUP (ORDER BY p.Name) AS products
FROM Sales.OrderLine AS ol
JOIN Sales.Orders    AS o ON o.OrderId = ol.OrderId
JOIN Catalog.Product AS p ON p.ProductId = ol.ProductId
GROUP BY o.OrderId
ORDER BY o.OrderId;

STRING_AGG(col, separator) WITHIN GROUP (ORDER BY col) — concatenează toate valorile non-NULL din fiecare grup, sortate cum vrei.

Înainte de STRING_AGG (adăugat în SQL Server 2017), asta cerea trucul infam FOR XML PATH — vreo 15 linii de cod ilizibil. STRING_AGG o face pe o linie. Dacă vezi FOR XML PATH('') în query-uri vechi, te uiți la o soluție pre-2017 care merită refactorizată.

STRING_SPLIT: transformă un string delimitat în rânduri

Operațiunea inversă: împarte un string CSV în rânduri individuale.

-- Split simplu
SELECT value FROM STRING_SPLIT('NL,IT,DE,FR,ES', ',');
-- Returnează 5 rânduri.

-- Cu ordinal (SQL Server 2022+, necesită enable_ordinal)
SELECT value, ordinal
FROM STRING_SPLIT('apple,banana,cherry', ',', 1);
-- Returnează rândurile cu poziția lor

Util pentru:

  • Spargerea listelor de tag-uri în tag-uri individuale.
  • Parsarea importurilor CSV.
  • Acceptarea unui parametru „listă” într-o procedură stocată.

Tipar clasic de parametru:

CREATE PROCEDURE Sales.GetOrdersByCountries
    @CountryCsv NVARCHAR(500)
AS
BEGIN
    SELECT o.OrderId, o.Total, o.CountryCode
    FROM Sales.Orders AS o
    WHERE o.CountryCode IN (SELECT value FROM STRING_SPLIT(@CountryCsv, ','));
END;

-- Apelat din aplicație:
EXEC Sales.GetOrdersByCountries @CountryCsv = 'NL,IT,DE';

O modalitate simplă de a transmite o listă de valori ca un singur parametru. Nu este singura — table-valued parameters sunt mai eficiente pentru liste mari — dar este curată pentru până la câteva zeci de valori.

TRIM și prietenii

SELECT LTRIM(RTRIM('   hello world   '));  -- 'hello world', stilul vechi
SELECT TRIM('   hello world   ');          -- 'hello world', din 2017
SELECT TRIM(',' FROM ',,,trimmed,,,');     -- 'trimmed', orice caracter (2022+)

TRIM lucrează implicit cu spații albe. Poți specifica și un set de caractere de tăiat:

SELECT TRIM(' ,.;' FROM '  , .Mr. Bloch;  ');
-- 'Mr. Bloch'

Util pentru curățarea datelor importate care au padding inconsistent, virgule rătăcite sau caractere BOM.

Pattern matching: LIKE, PATINDEX, CHARINDEX

LIKE

-- Email-uri care se termină în @gmail.com
WHERE Email LIKE '%@gmail.com';

-- Nume care încep cu 'Anne'
WHERE Name LIKE 'Anne%';

-- Exact 5 caractere
WHERE Code LIKE '_____';

-- Clase de caractere (un singur caracter)
WHERE Code LIKE '[A-Z]%';   -- începe cu literă mare
WHERE Code LIKE '[^0-9]%';  -- începe cu un caracter non-cifră

Wildcard-uri: % se potrivește oricărui string (inclusiv gol), _ se potrivește exact unui caracter, [abc] unuia dintre caracterele listate, [^abc] unui caracter care nu este în set.

Reamintire SARGability (lecția 7): LIKE 'prefix%' este SARGable (poate face seek pe un index). LIKE '%suffix' și LIKE '%middle%' nu sunt — forțează un scan. Dacă ai nevoie de full-text search, folosește feature-ul Full-Text Search din SQL Server.

PATINDEX și CHARINDEX

Găsește poziția unui pattern sau substring:

SELECT CHARINDEX('@', Email) AS at_position FROM Sales.Customer;
-- Poziția lui '@' în email.

SELECT PATINDEX('%[0-9]%', 'order 123') AS first_digit_pos;
-- 7. Pattern match precum LIKE, returnează poziția.

Returnează 0 dacă nu găsește. Util pentru spargerea manuală a stringurilor când STRING_SPLIT nu este de ajuns.

Operații comune cu stringuri

-- Schimbă litera mare/mică
SELECT UPPER(Name), LOWER(Email) FROM Sales.Customer;

-- Substring
SELECT SUBSTRING(Email, 1, CHARINDEX('@', Email) - 1) AS localpart,
       SUBSTRING(Email, CHARINDEX('@', Email) + 1, 4000) AS domain
FROM Sales.Customer;

-- Înlocuiește
SELECT REPLACE('Apples, oranges, bananas', ',', ';') AS csv_semi;

-- Lungime
SELECT LEN(Name) AS length_char,      -- numărul de caractere (taie spațiile finale)
       DATALENGTH(Name) AS length_bytes  -- numărul de octeți (2× pentru NVARCHAR)
FROM Sales.Customer;

-- Reverse (rar util, ocazional la îndemână)
SELECT REVERSE('goblin');  -- 'nilbog'

-- Padding
SELECT RIGHT(REPLICATE('0', 10) + CAST(OrderId AS NVARCHAR(10)), 10) AS padded_id
FROM Sales.Orders;
-- Padding cu zerouri la 10 caractere.

-- Sau FORMAT, mai lent dar mai curat
SELECT FORMAT(OrderId, '0000000000') AS padded_id
FROM Sales.Orders;

LEN vs DATALENGTH contează: LEN taie spațiile finale și returnează numărul de caractere; DATALENGTH returnează octeți (deci 2× pentru Unicode). Dacă îți pasă de stocare sau lucrezi cu VARBINARY, folosește DATALENGTH. În rest, LEN.

FORMAT este convenabil, dar cunoscut ca fiind lent — apelează framework-ul System.Globalization din .NET pentru fiecare rând. Pentru dashboard-uri și query-uri ușoare e ok; pentru query-uri fierbinți, folosește CONVERT + pattern-uri explicite.

Expresii regulate (vestea proastă)

SQL Server nu are regex nativ. LIKE este pattern matching limitat, nu regex.

Opțiunile tale:

  1. Simulează tipare comune cu LIKE. Funcționează pentru 80% dintre cazuri.
  2. Scrie o funcție CLR — posibil, rar, adaugă complexitate de deployment.
  3. SQL Server 2025+ are REGEXP_LIKE, REGEXP_REPLACE, REGEXP_INSTR, REGEXP_SUBSTR native. Dacă ești pe 2025, sărbătorește și folosește-le.
  4. Parsează în aplicație — pentru nevoi cu adevărat complexe de regex, codul aplicației este adesea locul potrivit.
-- SQL Server 2025+
SELECT Email FROM Sales.Customer WHERE REGEXP_LIKE(Email, '^[a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,}$');

Dacă moștenești o bază de date cu funcții CLR definite de utilizator numite RegexMatch, RegexReplace etc. — aceea era soluția pre-2025. Când migrezi la 2025, înlocuiește-le cu funcțiile native.

Collation și case sensitivity în stringuri

Amintește-ți de collation din lecția 4: afectează sortarea și comparațiile de egalitate pentru stringuri.

-- Verificare case-sensitive pe o coloană case-insensitive
SELECT Email FROM Sales.Customer
WHERE Email = 'Anne@Example.COM' COLLATE Latin1_General_CS_AS;
-- Asta se va potrivi exact cu 'Anne@Example.COM', nu cu 'anne@example.com'.

COLLATE <nume> suprascrie collation-ul pentru o singură comparație. Util pentru a forța case-sensitivity într-o bază de date altfel case-insensitive.

Util-dar-folosit-excesiv: ISJSON și extragerea JSON

T-SQL din 2016 are ISJSON, JSON_VALUE, JSON_QUERY, OPENJSON pentru parsarea JSON-ului stocat în coloane NVARCHAR. SQL Server 2025 adaugă un tip JSON nativ.

-- Atribute de produs stocate ca JSON
SELECT ProductId,
       JSON_VALUE(Attributes, '$.color')        AS color,
       JSON_VALUE(Attributes, '$.material')     AS material,
       JSON_VALUE(Attributes, '$.dimensions.cm') AS dim_cm
FROM Catalog.Product
WHERE JSON_VALUE(Attributes, '$.color') = 'black';

Util când schema ta are atribute flexibile. Avertisment: apelul JSON_VALUE nu este SARGable pe acea coloană decât dacă creezi o coloană calculată + index. Pentru query-uri orientate pe filtrare, extrage într-o coloană reală.

Rulează asta pe propriul tău server

USE Runehold;
GO

-- 1. CONCAT și CONCAT_WS
SELECT CustomerId,
       CONCAT_WS(' - ', Name, Country, Email) AS label
FROM Sales.Customer;

-- 2. STRING_AGG: lista de produse pe comandă
-- (necesită tabelele OrderLine și Product; aici e o versiune ușoară)
SELECT CustomerId,
       STRING_AGG(CAST(OrderId AS NVARCHAR(20)), ', ')
           WITHIN GROUP (ORDER BY OrderId) AS orders_csv,
       COUNT(*) AS order_count
FROM Sales.Orders
GROUP BY CustomerId
ORDER BY order_count DESC;

-- 3. STRING_SPLIT: filtrează după un parametru CSV
DECLARE @countries NVARCHAR(100) = 'NL,IT,DE';

SELECT OrderId, CountryCode, Total
FROM Sales.Orders
WHERE CountryCode IN (SELECT value FROM STRING_SPLIT(@countries, ','))
ORDER BY CountryCode;

-- 4. Extrage părți din email cu SUBSTRING + CHARINDEX
SELECT Email,
       SUBSTRING(Email, 1, CHARINDEX('@', Email) - 1) AS local_part,
       SUBSTRING(Email, CHARINDEX('@', Email) + 1, 4000) AS domain
FROM Sales.Customer
WHERE CHARINDEX('@', Email) > 0;

-- 5. Raport de frecvență a domeniilor
SELECT SUBSTRING(Email, CHARINDEX('@', Email) + 1, 4000) AS domain,
       COUNT(*) AS customers
FROM Sales.Customer
WHERE CHARINDEX('@', Email) > 0
GROUP BY SUBSTRING(Email, CHARINDEX('@', Email) + 1, 4000)
ORDER BY customers DESC;

-- 6. TRIM cu un set de caractere
SELECT TRIM(' ,.;' FROM '  ;Piotr Nowak,  ') AS cleaned;

Rulează fiecare. Uită-te la ieșiri. Observă cât de mult mai scurte și mai curate sunt acestea decât înainte de existența funcțiilor moderne.

Lecția următoare: date și ore. Mlaștina fusurilor orare. De ce există DATETIMEOFFSET, ce face AT TIME ZONE și bug-urile care apar din stocarea naivă a orei locale.

Caută