In questo articolo parleremo di tre utili funzioni di SQL Server disponibili dalla versione 2017 che possono rendere più semplice e immediata l’esperienza di sviluppo dei programmatori T-SQL.
Funzione TRIM
Fino alla versione 2016 di Sql Server, per eliminare gli spazi bianchi a sinistra e a destra di una stringa, occorreva combinare le due funzione LTRIM e RTRIM come in questo esempio:
SELECT LTRIM(RTRIM(' string '));
Questa sintassi risulta spesso “pesante”, soprattutto in quei contesti dove l’utilizzo di tali funzioni è piuttosto frequente (pensiamo ad esempio alle fasi di data quality e integration dei dati). Finalmente anche su Sql Server possiamo raggiungere il medesimo risultato con la sola funzione TRIM, analogamente a quanto già possibile su altri DBMS come ad esempio su Oracle.
SELECT TRIM(' string ' );
Oltre agli spazi bianchi, la funzione TRIM può essere utilizzata per eliminare anche dei caratteri particolari. Questo permette di risolvere agevolmente problematiche molto comuni come “eliminare gli zeri a sinistra della stringa”, come molto spesso succede nelle colonne che contengono indistintamente codici fiscali e partite iva. Vediamo uno script d’esempio
CREATE TABLE #test( codice VARCHAR(10)); INSERT INTO #test(codice) VALUES ('01'), ('02'), ('2'), ('0'), ('001'); SELECT Codice, TRIM('0' FROM codice) AS AfterTrim FROM #test;
Codice | AfterTrim |
01 | 1 |
02 | 2 |
2 | 2 |
0 | |
001 | 1 |
Funzione CONCAT_WS
Molto spesso si ha l’esigenza di concatenare più colonne separandole con una virgola oppure un altro separatore. Creiamo un semplice caso d’esempio:
CREATE TABLE #test2 ( nome VARCHAR(50), cognome VARCHAR(50), citta VARCHAR(50), indirizzo VARCHAR(50));
INSERT INTO #test2( nome, cognome, citta, indirizzo) VALUES ('Nicola', 'Iantomasi', 'Torino', NULL), ('Giovanni', 'Rossi', 'Milano', 'Via Rossi');
Per concatenare le quattro colonne dell’esempio, occorrerà ripetere il carattere separatore tre volte.
SELECT CONCAT(nome, ',', cognome, ',', citta, ',', indirizzo) AS AfterConcat FROM #test2;
Se dovessimo concatenare dieci colonne, occorrerebbe ripeterlo nove volte, eccetera. Tramite la funzione CONCAT_WS possiamo invece indicare il carattere separatore una sola volta, in questo modo:
SELECT CONCAT_WS(',', nome, cognome, citta, indirizzo) AS AfterConcatWs FROM #test2;
Molto importante è notare il diverso comportamento delle funzioni CONCAT e CONCAT_WS in presenza di null.
La funzione Concat_WS elimina il separatore nel caso in cui la colonna seguente contenga un null. Occorre essere consapevoli di questo comportamento: probabilmente non sarà quello che vogliamo se il nostro obiettivo è convertire in formato csv i dati di una tabella.
Tuttavia possiamo “porre rimedio” inglobando le colonne all’interno della funzione isnull
SELECT CONCAT_WS( ',', ISNULL(nome,''), ISNULL(cognome,''), ISNULL(citta,''), ISNULL(indirizzo'')) AS AfterConcatWs FROM #test2;
Al contrario, se volessimo creare una stringa indirizzo con esattamente questa caratteristica di “saltare” le colonne, l’implementazione con la funzione CONCAT risulterebbe molto più lunga e frustante.
Funzione STRING_AGG
In presenza di una clausola group by, spesso si ha la necessità di concatenare (eventualmente con un separatore) i valori di una colonna di tipo varchar relativi alla chiave di aggregazione.
Prima del 2017, per risolvere questo problema gli sviluppatori T-SQL hanno dato sfoggio della loro abilità e creatività utilizzando le più svariate tecniche: funzioni xml path, conversioni in json, variabili che si concatenano con esse stesse, eccetera…
Da SQL Server 2017 abbiamo a disposizione una nuova funzione di aggregazione utilissima: STRING_AGG.
Niente può essere più esplicativo di uno script d’esempio:
CREATE TABLE #test3( IdConto INT, IdCliente INT); INSERT INTO #test3( IdConto, IdCliente) VALUES (1,1), (1,2), (2,3), (3,4), (3,NULL); SELECT IdConto, STRING_AGG(IdCliente, ';') AS ClientiCointestatari FROM #test3 GROUP BY IdConto;
IdConto | ClientiCointestatari |
1 | 1;2 |
2 | 3 |
3 | 4 |
Notiamo inoltre che le colonne int sono convertite implicitamente in varchar e il comportamento in presenza di null.
Esercizio sulle funzioni STRING_AGG e CONCAT_WS
Combinando le due funzioni STRING_AGG e CONCAT_WS possiamo stampare una variabile che contenga la tabella relazionale in formato csv.
DECLARE @CSV VARCHAR(8000); SELECT @CSV = STRING_AGG( CONCAT_WS(';', ISNULL(Nome,''), ISNULL(Cognome,'')), CHAR(10) ) FROM Clienti; PRINT (@CSV);
Se la dimensione del risultato supera 8000 bytes otterremo un errore, inoltre non sono gestiti i casi in cui il carattere separatore è contenuto all’interno dei valori delle colonne.
Bonus: la nuova funzione APPROX_COUNT_DISTINCT
Nel caso di elevate quantità di dati, l’utilizzo di una COUNT(DISTINCT ) potrebbe essere deleterio in termini di performance.
APPROX_COUNT_DISTINCT fornisce un valore approssimato rispetto al COUNT(DISTINCT) con delle performance migliori. Per la precisione, la funzione ci restituirà al 97% un errore inferiore del 2%.
SELECT IdFattura, APPROX_COUNT_DISTINCT(IdCliente) AS NumeroApprossimatoDiClienti FROM Fatture GROUP BY IdFattura;
Tuttavia, occorre comunque fare massima attenzione alle performance come sottolineato da Pinal Dave in questo interessante articolo https://blog.sqlauthority.com/2019/12/11/sql-server-approx_count_distinct-not-always-efficient/.
Continua a imparare
- Mettiti alla prova con esercizi difficili su Sql Server
- Approfondiamo le problematiche dell’operatore SQL Not In
- Padroneggia tutti i costrutti avanzati del linguaggio T-SQL come le window function, il cross apply e l’outer apply o le clausole Grouping sets con il mio corso sul linguaggio T-SQL.