Questo articolo è uno sforzo per discutere le funzioni SQL Cast e SQL Convert come seguito degli articoli precedenti, in cui abbiamo discusso diversi suggerimenti SQL come SQL Date, SQL Coalesce, SQL Union, SQL Join, SQL Like, SQL String etc.
A volte abbiamo bisogno di convertire i dati tra diversi tipi di dati SQL. Oltre a lavorare con i dati, ci sono alcune funzioni integrate che possono essere utilizzate per convertire i dati. Quindi diamo un’occhiata più da vicino alle funzioni di conversione SQL CAST e SQL CONVERT in dettaglio.
Introduzione
Per eseguire operazioni o confronti o trasformazioni tra dati in un database SQL Server, i tipi di dati SQL di quei valori devono corrispondere. Quando i tipi di dati SQL sono diversi, passano attraverso un processo chiamato type-casting. La conversione dei tipi di dati SQL, in questo processo, può essere implicita o esplicita.
Le funzioni di conversione del tipo di dati comunemente usate specialmente per soddisfare gli standard dei dati degli oggetti o dei sistemi di destinazione. Siamo nel mondo che tratta dati eterogenei.
Nota: Una panoramica dei concetti di alto livello della gestione dei dati è discussa in questo articolo SQL string functions for Data Munging (Wrangling).
A volte i tipi di dati devono essere convertiti in altri tipi di dati per calcoli o trasformazioni o processi o per soddisfare i formati di dati di destinazione. Per esempio, quando moltiplichiamo i dati di tipo decimale con un intero, i dati subiscono una trasformazione interna e implicitamente convertono un intero in un tipo di dati decimale e il risultato è un dato decimale.
Conversione SQL implicita ed esplicita
Quando abbiamo a che fare con due valori che sono uguali in natura ma di tipo diverso, dietro le quinte, il motore del database converte i valori di tipo di dati inferiore in un tipo di dati superiore prima di poter procedere con il calcolo. Questo tipo è conosciuto come una conversione implicita. D’altra parte, abbiamo conversioni esplicite in cui si chiama una funzione SQL CAST o SQL CONVERT per cambiare il tipo di dati. Potete fare riferimento all’articolo Panoramica sul formato di data SQL; funzione DateDiff SQL, funzione DateAdd SQL e altro per degli esempi.
Sintassi:
|
1
2
3
4
|
CAST
CAST ( exp AS datatype )
CONVERT
CONVERT ( datatype , expression )
|
exp
Definisce l’argomento dell’espressione valida
datatype
Questo fornisce i dettagli del tipo di dati di destinazione
len
Questo specifica la lunghezza del tipo di dati di destinazione. Questo è un parametro opzionale per impostazione predefinita, il valore della lunghezza è impostato a 30
style
È un valore intero usato con la funzione SQL CONVERT per tradurre l’espressione nell’output desiderato
SQL Convert e Cast data conversion chart
La seguente è un foglio di riferimento rapido per la conversione del tipo di dati
Esempio 1: Conversione SQL implicita con numeri
Nell’esempio seguente, due numeri casuali 5 e 212345 sono aggiunti e i risultati sono visualizzati.
|
1
2
|
DECLARE @a int=5, @b bigint=212345
SELECT @a, @b, @a+@b
|
Vediamo che i due valori sono di tipi di dati diversi, il motore del database ha implicitamente convertito il valore int in bigint prima di fare l’addizione. E poi ha potuto elaborare l’operazione di addizione perché sono diventati lo stesso tipo di dati.
Esempio 2: conversione implicita SQL con i caratteri
Nell’esempio seguente, aggiungeremo due tipi di dati carattere. Creiamo le variabili @a tipo di dati carattere e assegniamo un valore ‘5’ e @b con un valore ‘2’.
|
1
2
|
DECLARE @a char=’5′, @b char=’2′
SELECT @a, @b, @a+@b
|
Nell’output, prima, otteniamo i valori delle variabili ‘a’ e ‘b’, secondo e il risultato del calcolo che è entrambi i valori insieme. Con i valori dei caratteri, il motore del database ha implicitamente deciso di concatenare entrambi i valori e visualizzare 52 come output del calcolo.
Esempio 3: Come eseguire una conversione esplicita usando SQL Cast
Nel seguente esempio, vedremo come forzare la conversione da un tipo di dati a un altro. Prendiamo un esempio precedente e convertiamo i valori di input al tipo numerico e poi aggiungiamo questi valori.
|
1
2
|
DECLARE @a char=’5′, @b char=’2′
SELECT @a,@b, cast(@a come int)+ cast(@b come int)
|
La funzione SQL CAST prende due argomenti. In primo luogo, i dati che vogliamo elaborare, in questo caso, i dati sono ‘5’ e ‘2’ e sono di campo char, e poi come volete elaborarli, o come vogliamo CASTarli. In questo caso, volete CASTarlo come un campo intero.
Nell’output, notate che i valori di carattere in ingresso sono convertiti e anche aggiunti quei valori e il risultato è di tipo intero.
Esempio 4: Gestione avanzata degli errori usando TRY_CAST
Nel seguente esempio, sezioneremo ulteriormente i valori dei tipi di dati usando la funzione SQL cast e la funzione SQL try_cast.
Nel seguente SQL, il valore stringa ‘123’ è convertito in BIGINT. È stato in grado di convertire a causa della natura dei dati. Diamo un’occhiata al secondo esempio, il valore della stringa ‘xyz’ è usato nell’espressione per la funzione SQL cast. Quando state facendo questo, vedrete un errore che dice “Error Converting data type varchar to bigint”. Quando state facendo l’analisi dei dati con varie fonti di dati, di solito otteniamo questo tipo di valori nei dati di origine. Quindi, al fine di gestire questo tipo di scenari, è possibile utilizzare la funzione try_cast di SQL. Nella terza linea di SQL, passiamo la stringa come valore di input, dopo l’esecuzione, non abbiamo ricevuto un errore ma finiamo per ottenere un valore NULL come output. Il valore NULL può essere ulteriormente semplificato usando la funzione SQL ISNULL o la funzione SQL coalesce.
Nota: Potete fare riferimento all’articolo Usare la funzione SQL Coalesce in SQL Server per maggiori informazioni.
|
1
2
3
4
5
6
7
|
SELECT TRY_CAST(‘123’ AS BIGINT);
GO
SELECT CAST(‘xyz’ AS BIGINT);
GO
SELECT TRY_CAST(‘xyz’ AS BIGINT);
GO
SELECT ISNULL(TRY_CAST(‘xyz’ AS BIGINT), 99);
|
Esempio 5: Codice di stile spiegato nelle funzioni CONVERT
L’esempio seguente converte il campo hiredate in diversi stili disponibili della funzione SQL convert. La funzione SQL convert inizia prima con un tipo di dati, e una virgola, e poi seguita da un’espressione. Con Date, abbiamo anche un altro argomento che possiamo fornire, chiamato codice di stile SQL. Potete trovare un elenco di questi nell’articolo CAST e CONVERT (Transact-SQL) documentazione tecnica.
|
1
2
3
4
5
6
7
8
9
|
SELECT hiredate,
CONVERT(CHAR(50), hiredate),
CONVERT(CHAR(50), hiredate, 1) ‘S-1’,
CONVERT(CHAR(50), hiredate, 101) ‘s-101’,
CONVERT(CHAR(50), hiredate, 102) ‘s-102’,
CONVERT(CHAR(50), hiredate, 103) ‘s-103’,
CONVERT(CHAR(50), hiredate, 104) ‘s-104’,
CONVERT(CHAR(50), hiredate, 105) ‘s-105’
FROM HumanResources.Employee;
|
Andiamo avanti eseguendo il suddetto SQL. Ora possiamo vedere il diverso output che viene visualizzato utilizzando i parametri di stile all’interno della funzione di conversione SQL. Potete vedere che il campo hiredate converte il campo data in diversi stili.
Esempio 6: Le differenze e le somiglianze tra CAST e CONVERT
Nel seguente esempio, si può vedere che l’uso di CAST e CONVERT nel seguente T-SQL per confrontare i tempi di esecuzione.
Eseguiamo il seguente T-SQL per utilizzare la funzione CAST
|
1
2
3
4
5
6
7
8
9
10
11
|
SET STATISTICS TIME ON
WITH temp
AS (SELECT CAST(GETDATE() AS DATE) AS cast_date,
0 AS n
UNION ALL
SELECT CAST(GETDATE() + n AS DATE) AS cast_date,
n + 1
FROM temp
WHERE n < 1000000)
SELECT MAX(cast_date)
FROM temp OPTION(MAXRECURSION 0);
|
Ora, eseguite lo stesso T-SQL con la funzione SQL CONVERT al suo posto.
|
1
2
3
4
5
6
7
8
9
10
11
|
SET STATISTICS TIME ON
WITH temp
AS (SELECT CONVERT(DATE, GETDATE(), 20) AS convert_date,
0 AS n
UNION ALL
SELECT CONVERT(DATE, GETDATE() + n, 20) AS convert_date,
n + 1
FROM temp
WHERE n < 1000000)
SELECT MAX(convert_date)
FROM temp OPTION(MAXRECURSION 0);
|
Consigli veloci:
- CAST è puramente uno standard ANSI-SQL. Ma, CONVERT è una funzione specifica di SQL Server come abbiamo to_char o to_date in Oracle
- CAST è prevalentemente disponibile in tutti i prodotti di database a causa della sua portabilità e facilità d’uso
- Non ci sarà una grande differenza in termini di esecuzione della query tra le funzioni SQL Cast e SQL Convert. Si può notare una leggera differenza nei tempi di esecuzione questo è dovuto alla conversione interna di SQL CAST nella sua funzione nativa SQL CONVERT ma la funzione CONVERT è dotata di un’opzione “Style-code” per ricavare varie combinazioni di data e ora, decimali e valori monetari. In ogni caso, la funzione SQL CONVERT funziona leggermente meglio della funzione SQL CAST
Esempio 7: Codice di stile nel calcolo usando CONVERT
In questo esempio, si elencherà il campo tasso dalla tabella “HumanResources.EmployeeHistory” del database Adventureworks2016. La seconda colonna nell’SQL usa la funzione SQL CONVERT con il tipo di dati char(10) e l’espressione rate data la moltiplica per 1000.
Il primo SQL, il codice di stile non è usato ma per il secondo e il terzo SQL, il codice di stile è impostato a 1.
|
1
2
3
4
5
6
7
8
9
|
SELECT TOP 3 Rate,
CONVERT(CHAR(10), rate * 1000) AS TextPrice
FROM HumanResources.EmployeePayHistory;
SELECT TOP 3 Rate,
CONVERT(CHAR(10), rate * 1000, 0) AS TextPrice
FROM HumanResources.EmployeePayHistory;
SELECT TOP 3 Rate,
CONVERT(CHAR(10), rate * 1000, 1) AS TextPrice
FROM HumanResources.EmployeePayHistory;
|
Nell’output, possiamo vedere che la query con il codice di stile 1 restituisce un output di testo con un separatore di virgola nella posizione delle migliaia. La query con il codice di stile 0 restituisce un output di testo senza separatore di virgola.
Wrap Up
Finora, abbiamo parlato delle funzioni SQL cast, SQL try_cast e SQL convert e di come è possibile convertire tra stringhe, interi e valori di data e ora. Abbiamo anche esaminato la funzione SQL CONVERT con il codice di stile. Abbiamo fatto alcuni esempi di funzioni di conversione SQL implicite ed esplicite. E abbiamo capito la differenza tra le funzioni SQL CAST e SQL CONVERT. Vedete anche che viene usata un’espressione condizionale con SQL ISNULL insieme a SQL try_cast, e di nuovo, se l’avessi fatto senza usare la parte try, avrebbe fallito invece di restituire un valore. Questo è il modo in cui è possibile eseguire il cast dei valori, che è abbastanza standard, con questo livello aggiuntivo, e anche e gli errori con più grazia.
Questo è tutto per ora… Spero vi sia piaciuto leggere questo articolo. Sentitevi liberi di commentare qui sotto.
- Autore
- Post recenti
La mia specialità sta nel progettare & implementare soluzioni di alta disponibilità e migrazioni DB multipiattaforma. Le tecnologie su cui lavoro attualmente sono SQL Server, PowerShell, Oracle e MongoDB.
Vedi tutti i post di Prashanth Jayaram
- Una rapida panoramica del controllo dei database in SQL – 28 gennaio, 2021
- Come impostare Azure Data Sync tra i database Azure SQL e SQL Server on-premises – 20 gennaio 2021
- Come eseguire operazioni di importazione/esportazione di database Azure SQL utilizzando PowerShell – 14 gennaio 2021