Dieser Artikel ist ein Versuch, die Funktionen von SQL Cast und SQL Convert zu besprechen, als Fortsetzung früherer Artikel, in denen wir verschiedene SQL-Tipps wie SQL Date, SQL Coalesce, SQL Union, SQL Join, SQL Like, SQL String usw. besprochen haben.
Manchmal müssen wir Daten zwischen verschiedenen SQL-Datentypen konvertieren. Neben der Arbeit mit Daten gibt es einige eingebaute Funktionen, die zum Konvertieren der Daten verwendet werden können. Schauen wir uns also die SQL-Konvertierungsfunktionen SQL CAST und SQL CONVERT im Detail an.
Einführung
Um Operationen, Vergleiche oder Umwandlungen zwischen Daten in einer SQL Server-Datenbank durchzuführen, müssen die SQL-Datentypen dieser Werte übereinstimmen. Wenn die SQL-Datentypen unterschiedlich sind, durchlaufen sie einen Prozess, der Type-Casting genannt wird. Die Konvertierung der SQL-Datentypen kann bei diesem Prozess implizit oder explizit sein.
Die Datentypkonvertierungsfunktionen werden üblicherweise speziell dafür verwendet, die Datenstandards der Zielobjekte oder -systeme zu erfüllen. Wir haben es in der Welt mit heterogenen Daten zu tun.
Hinweis: Eine Übersicht über die High-Level-Konzepte der Datenverwaltung finden Sie in diesem Artikel SQL-Stringfunktionen für Data Munging (Wrangling).
Manchmal müssen die Datentypen der Daten für Berechnungen oder Umwandlungen oder Prozesse in andere Datentypen umgewandelt werden oder um den Zieldatenformaten zu entsprechen. Wenn wir z. B. Daten vom Typ „Dezimal“ mit einer ganzen Zahl multiplizieren, durchlaufen die Daten eine interne Transformation und konvertieren implizit eine ganze Zahl in einen dezimalen Datentyp, und das Ergebnis sind dezimale Daten.
Implizite und explizite SQL-Konvertierung
Wenn wir es mit zwei Werten zu tun haben, die von der Natur her gleich sind, aber unterschiedliche Datentypen haben, konvertiert die Datenbank-Engine hinter den Kulissen die Werte des niedrigeren Datentyps in den höheren Datentyp, bevor sie mit der Berechnung fortfahren kann. Diese Art wird als implizite Konvertierung bezeichnet. Auf der anderen Seite gibt es explizite Konvertierungen, bei denen Sie entweder eine SQL CAST- oder SQL CONVERT-Funktion aufrufen, um den Datentyp zu ändern. Beispiele finden Sie im Artikel Übersicht über SQL-Datumsformate; SQL-Funktion DateDiff, SQL-Funktion DateAdd und weitere.
Syntax:
|
1
2
3
4
|
CAST
CAST ( exp AS datatype )
CONVERT
CONVERT ( datatype , Ausdruck )
|
exp
Definiert das gültige Ausdrucksargument
datatype
Dies gibt die Details des Zieldatentyps an
len
Dies gibt die Länge des Zieldatentyps an. Dies ist standardmäßig ein optionaler Parameter, der Längenwert ist auf 30 gesetzt
style
Es ist ein ganzzahliger Wert, der mit der Funktion SQL CONVERT verwendet wird, um den Ausdruck in die gewünschte Ausgabe zu übersetzen
SQL Convert and Cast data conversion chart
Die folgende Konvertierungstabelle ist eine Kurzreferenz für die Datentypkonvertierung
Beispiel 1: SQL Implizite Konvertierung mit Zahlen
Im folgenden Beispiel werden zwei Zufallszahlen 5 und 212345 addiert und das Ergebnis angezeigt.
|
1
2
|
DECLARE @a int=5, @b bigint=212345
SELECT @a, @b, @a+@b
|
Wir sehen, dass die beiden Werte von unterschiedlichen Datentypen sind, die Datenbank-Engine hat den int-Wert implizit in einen bigint konvertiert, bevor sie die Addition durchgeführt hat. Und dann konnte sie die Additionsoperation verarbeiten, da sie denselben Datentyp haben.
Beispiel 2: SQL Implizite Konvertierung mit Zeichen
Im folgenden Beispiel werden wir zwei Zeichendaten-Typen addieren. Wir legen die Variablen @a mit dem Datentyp Zeichen an und weisen ihnen den Wert ‚5‘ und @b mit dem Wert ‚2‘ zu.
|
1
2
|
DECLARE @a char=’5′, @b char=’2′
SELECT @a, @b, @a+@b
|
In der Ausgabe erhalten wir erstens die Werte der Variablen ‚a‘ und ‚b‘, zweitens das Ergebnis der Berechnung, das beide Werte zusammen ist. Da die Zeichenwerte vorhanden sind, hat die Datenbank-Engine implizit entschieden, beide Werte zu verketten und 52 als Ausgabe der Berechnung anzuzeigen.
Beispiel 3: Durchführen einer expliziten Konvertierung mit SQL Cast
Im folgenden Beispiel sehen wir, wie man die Konvertierung von einem Datentyp in einen anderen erzwingt. Wir nehmen das obige Beispiel und konvertieren die Eingabewerte in den numerischen Typ und fügen diese Werte dann hinzu.
|
1
2
|
DECLARE @a char=’5′, @b char=’2′
SELECT @a,@b, cast(@a as int)+ cast(@b as int)
|
Die SQL-Funktion CAST nimmt zwei Argumente entgegen. Erstens, die Daten, die wir verarbeiten wollen, in diesem Fall sind die Daten ‚5‘ und ‚2‘ und sind von char-Feld, und dann, wie Sie sie verarbeiten wollen, oder wie wir sie CASTen wollen.
In der Ausgabe sehen Sie, dass die eingegebenen Zeichenwerte konvertiert und auch addiert wurden und das Ergebnis vom Typ Integer ist.
Beispiel 4: Erweiterte Fehlerbehandlung mit TRY_CAST
Im folgenden Beispiel werden wir die Werte von Datentypen mit der SQL Cast-Funktion und der SQL Try_cast-Funktion weiter zerlegen.
Im folgenden SQL wird der String-Wert ‚123‘ in BIGINT umgewandelt. Die Konvertierung war aufgrund der Beschaffenheit der Daten möglich. Schauen wir uns das zweite Beispiel an, der String-Wert ‚xyz‘ wird im Ausdruck für die SQL cast-Funktion verwendet. Wenn Sie dies tun, sehen Sie die Fehlermeldung „Fehler beim Konvertieren des Datentyps varchar in bigint“. Wenn Sie die Datenanalyse mit verschiedenen Datenquellen durchführen, erhalten wir normalerweise diese Art von Werten in den Quelldaten. Um diese Art von Szenarien zu behandeln, können Sie die SQL-Funktion try_cast verwenden. In der dritten Zeile von SQL übergeben wir die Zeichenkette als Eingabewert, nach der Ausführung erhalten wir keinen Fehler, sondern einen NULL-Wert als Ausgabe. Der NULL-Wert kann mit der SQL-Funktion ISNULL oder der SQL-Funktion coalesce weiter vereinfacht werden.
Hinweis: Weitere Informationen finden Sie im Artikel Verwenden der SQL Coalesce-Funktion in SQL Server.
|
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);
|
Beispiel 5: Erklärter Style-Code in CONVERT-Funktionen
Das folgende Beispiel konvertiert ein Hiredate-Feld in verschiedene verfügbare Styles der SQL-Convert-Funktion. Die SQL-Konvertierungsfunktion beginnt zunächst mit einem Datentyp und einem Komma, dann folgt ein Ausdruck. Bei Dates haben wir noch ein weiteres Argument, das wir angeben können, nämlich einen SQL-Style-Code. Eine Auflistung dieser können Sie im Artikel CAST und CONVERT (Transact-SQL) technische Dokumentation erhalten.
|
1
2
3
4
5
6
7
8
9
|
SELECT hiredate,
CONVERT(CHAR(50), angestelltes Datum),
CONVERT(CHAR(50), mietdatum, 1) ‚S-1‘,
CONVERT(CHAR(50), mietdatum, 101) ’s-101′,
CONVERT(CHAR(50), mietdatum, 102) ’s-102′,
CONVERT(CHAR(50), Einstellungsdatum, 103) ’s-103′,
CONVERT(CHAR(50), Einstellungsdatum, 104) ’s-104′,
CONVERT(CHAR(50), Einstellungsdatum, 105) ’s-105′
FROM HumanResources.Mitarbeiter;
|
Lassen Sie uns nun das oben erwähnte SQL ausführen. Jetzt können wir sehen, wie unterschiedlich die Ausgabe bei Verwendung von Stilparametern innerhalb der SQL-Konvertierungsfunktion ist. Sie können sehen, dass das Feld hiredate das Datumsfeld in verschiedene Stile konvertiert.
Beispiel 6: Die Unterschiede und Gemeinsamkeiten von CAST und CONVERT
Im folgenden Beispiel können Sie sehen, dass die Verwendung von CAST und CONVERT in der folgenden T-SQL die Ausführungszeiten vergleicht.
Lassen Sie uns die folgende T-.SQL, um die CAST-Funktion zu verwenden
|
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);
|
Nun, führen Sie die gleiche T-SQL mit der Funktion SQL CONVERT aus.
|
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);
|
Schnelle Tipps:
- CAST ist ein reiner ANSI-SQL-Standard. CONVERT ist jedoch eine SQL-Server-spezifische Funktion, ebenso wie to_char oder to_date in Oracle
- CAST ist wegen seiner Portabilität und Benutzerfreundlichkeit überwiegend in allen Datenbankprodukten verfügbar
- Es gibt keinen großen Unterschied in der Abfrageausführung zwischen den Funktionen SQL Cast und SQL Convert. Sie können einen leichten Unterschied in den Ausführungszeiten sehen, dies liegt an der internen Konvertierung von SQL CAST in die native SQL CONVERT-Funktion, aber die CONVERT-Funktion kommt mit einer Option „Style-Code“, um verschiedene Kombinationen von Datum und Uhrzeit, Dezimalzahlen und Geldwerten abzuleiten. In jedem Fall läuft die SQL CONVERT-Funktion etwas besser als die SQL CAST-Funktion
Beispiel 7: Style-Code in der Berechnung mit CONVERT
In diesem Beispiel wird das Feld „Rate“ aus der Tabelle „HumanResources.EmployeeHistory“ der Datenbank Adventureworks2016 aufgelistet. Die zweite Spalte im SQL verwendet die SQL-Funktion CONVERT mit dem Datentyp char(10) und dem Ausdruck rate data multipliziert sie mit 1000.
Im ersten SQL wird der Style Code nicht verwendet, aber für das zweite und dritte SQL wird der Style Code auf 1 gesetzt.
|
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;
|
In der Ausgabe sehen wir, dass die Abfrage mit dem Style-Code 1 eine Textausgabe mit einem Komma-Trennzeichen an der Tausenderstelle liefert. Die Abfrage mit dem Style-Code 0 ergibt die Textausgabe ohne Komma-Trennzeichen.
Wrap Up
So weit, haben wir über die Funktionen SQL cast, SQL try_cast und SQL convert gesprochen und darüber, wie Sie zwischen Strings, Integers und Datums- und Zeitwerten konvertieren können. Wir haben uns auch die SQL CONVERT-Funktion mit dem Style-Code angesehen. Wir sind einige Beispiele für die impliziten und expliziten SQL-Konvertierungsfunktionen durchgegangen. Und wir haben den Unterschied zwischen den Funktionen SQL CAST und SQL CONVERT verstanden. Sie sehen auch, dass ein bedingter Ausdruck mit SQL ISNULL zusammen mit der SQL try_cast-Funktion verwendet wird, und wiederum, wenn ich das ohne den try-Teil davon gemacht hätte, wäre es fehlgeschlagen, anstatt einen Wert zurückzugeben. So können Sie Werte casten, was ziemlich Standard ist, mit dieser zusätzlichen Ebene, und auch und Fehler anmutiger.
Das ist alles für jetzt… Ich hoffe, Sie hatten Spaß beim Lesen dieses Artikels. Fühlen Sie sich frei, unten zu kommentieren.
- Autor
- Recent Posts
Meine Spezialität ist das Design & und die Implementierung von Hochverfügbarkeitslösungen und plattformübergreifender DB-Migration. Die Technologien, an denen ich derzeit arbeite, sind SQL Server, PowerShell, Oracle und MongoDB.
Alle Beiträge von Prashanth Jayaram anzeigen
- Ein schneller Überblick über Datenbank-Audit in SQL – 28. Januar, 2021
- Wie Sie Azure Data Sync zwischen Azure SQL-Datenbanken und lokalem SQL Server einrichten – 20. Januar 2021
- Wie Sie Azure SQL-Datenbank-Import/Export-Vorgänge mit PowerShell durchführen – 14. Januar 2021