Detaillierte Erläuterung der Verwendung von SQL Cast und Convert
Zusammenfassung:
Das Folgende gilt nur für MySQL
SELECT {fn CONCAT(CONVERT( user_id ,CHAR),USER_NAME)} AS str FROM t_sys_user
Das Folgende gilt nur für sqlserver2008
SELECT {fn CONCAT(CONVERT(CHAR, user_id),USER_NAME)} AS str FROM t_sys_user
Der folgende SQL Server ist mit MySQL kompatibel
SELECT {fn CONCAT(CAST(user_id AS CHAR),USER_NAME)} AS str FROM t_sys_user
Kommentar: Cast ist eine Typkonvertierungsfunktion, die bei SQL Server und MySQL üblich ist.
Convert ist ebenfalls eine Typkonvertierung, die sowohl in sqlserver2008 als auch in mysql vorhanden ist, die Reihenfolge der Parameter ist jedoch umgekehrt.
Die concat-Funktion ist sowohl in sqlserver2008 als auch in mysql verfügbar, muss jedoch in sqlserver2008 wie {fn concat(arg0,arg1,...)} angewendet werden und ist nicht für die Verbindung mit int type und varchar geeignet Typ. In sqlserver2008 verwenden Zeichenfolgenverbindungen im Allgemeinen das „+“-Zeichen. Es scheint jedoch, dass die direkte Verwendung der Concat-Funktion in sqlserver2012 unterstützt wird.
Die Typbeschränkungen, in die MySQL konvertiert werden kann:
ist CAST (xxx AS-Typ), CONVERT (xxx, Typ).
Die Typen, die konvertiert werden können, sind begrenzt. Dieser Typ kann einer der folgenden Werte sein:
Binär, mit der Wirkung eines binären Präfixes: BINARY
Zeichentyp, kann Parameter annehmen: CHAR()
Datum: DATUM
Zeit: ZEIT
Datumszeittyp: DATETIME
Gleitkommazahl: DEZIMAL
Ganzzahl: VORZEICHNET
Ganzzahl ohne Vorzeichen: UNSIGNED
Der folgende Teil wird von mir reproduziert und der obige Teil zusammengefasst.
sqlserver
Konvertieren Sie einen Ausdruck eines Datentyps explizit in einen anderen Datentyp. CAST und CONVERT bieten ähnliche Funktionen.
Syntax
Verwenden Sie CAST:
CAST (expression AS data_type)
Verwenden Sie CONVERT:
CONVERT (data_type[(length) ], Ausdruck [, Stil])
Parameter
Ausdruck
ist jeder gültige Microsoft® SQL Server™-Ausdruck. Weitere Informationen finden Sie unter Ausdrücke.
data_type
Der vom Zielsystem bereitgestellte Datentyp, einschließlich bigint und sql_variant. Benutzerdefinierte Datentypen können nicht verwendet werden. Weitere Informationen zu den verfügbaren Datentypen finden Sie unter Datentypen.
Länge
Optionales Argument für den Datentyp nchar, nvarchar, char, varchar, binär oder varbinary.
Stil
Ein Datumsformatstil, der zum Konvertieren von Datetime- oder Smalldatetime-Daten in Zeichendaten (nchar, nvarchar, char, varchar, nchar oder nvarchar-Datentypen) oder einen String-Formatstil verwendet wird , wird zum Konvertieren von Float-, Real-, Money- oder Smallmoney-Daten in Zeichendaten (Datentyp nchar, nvarchar, char, varchar, nchar oder nvarchar) verwendet.
SQL Server unterstützt Datenformate im arabischen Stil mithilfe des kuwaitischen Algorithmus.
In der Tabelle stellen die beiden Spalten links Stilwerte dar, die datetime oder smalldatetime in Zeichendaten konvertieren. Addieren Sie 100 zum Stilwert, um eine vierstellige Jahreszahl (JJJJ) einschließlich der Jahrhundertziffer zu erhalten.
Ohne Jahrhundertziffer (yy) Mit Jahrhundertziffer (yyyy)
Standard
Eingabe/Ausgabe**
- 0 oder 100 (*) Standard Wert Mo TT JJJJ hh:miAM (oder PM)
1 101 US MM/TT/JJJJ
2 102 ANSI JJ.MM.TT
3 103 Vereinigtes Königreich/Frankreich tt/mm/jj
4 104 Deutschland tt.mm.jj
5 105 Italien tt-mm-jj
6 106 - tt mon jj
7 107 – Montag TT, JJ
8 108 – hh:mm:ss
- 9 oder 109 (*) Standard + Millisekunden Montag TT JJJJ hh:mi:ss:mmmAM (oder PM)
10 110 US mm-tt-jj
11 111 Japan jj/mm/tt
12 112 ISO jjmmtt
- 13 oder 113 (*) Europäischer Standardwert + Millisekunden dd mon yyyy hh:mm:ss:mmm(24h)
14 114 - hh:mi:ss:mmm(24h)
- 20 oder 120 ( *) ODBC-Spezifikation jjjj-mm-tt hh:mm:ss[.fff]
- 21 oder 121 (*) ODBC-Spezifikation (mit Millisekunden) jjjj-mm-tt hh:mm:ss [.fff ]
- 126(***) ISO8601 jjjj-mm-tt Thh:mm:ss:mmm (ohne Leerzeichen)
- 130* Kuwait dd mon yyyy hh: mi:ss: mmmAM
- 131* Kuwait
Der Standardwert (Stil 0 oder 100, 9 oder 109, 13 oder 113, 20 oder 120, 21 oder 121) gibt immer die Jahrhundertziffer (yyyy) zurück.
** Eingabe bei Konvertierung in Datum/Uhrzeit; Ausgabe bei Konvertierung in Zeichendaten.
*** Speziell für XML. Für die Konvertierung von datetime oder smalldatetime in Zeichendaten ist das Ausgabeformat in der Tabelle dargestellt. Für Konvertierungen von Float, Money oder Smallmoney in Zeichendaten entspricht die Ausgabe Stil 2. Bei Konvertierungen von realen Daten in Zeichendaten entspricht die Ausgabe Stil 1.
Wichtig Standardmäßig interpretiert SQL Server zweistellige Jahreszahlen basierend auf dem Stichtag 2049. Das heißt, eine zweistellige Jahreszahl 49 wird als 2049 interpretiert und eine zweistellige Jahreszahl 50 wird als 1950 interpretiert. Viele Clientanwendungen, beispielsweise solche, die auf OLE-Automatisierungsobjekten basieren, verwenden 2030 als Stichtag. SQL Server bietet eine Konfigurationsoption („Zweistelliges Stichtagjahr“), mit der Sie das von SQL Server verwendete Stichtagjahr ändern und Datumsangaben konsistent behandeln können. Der sicherste Ansatz ist jedoch die Angabe einer vierstelligen Jahreszahl.
Bei der Konvertierung von smalldatetime in Zeichendaten werden in Stilen, die Sekunden oder Millisekunden enthalten, an diesen Positionen Nullen angezeigt. Beim Konvertieren von datetime- oder smalldatetime-Werten können Sie unerwünschte Datumsteile abschneiden, indem Sie die entsprechende Länge des Datentyps char oder varchar verwenden.
Die folgende Tabelle zeigt Stilwerte bei der Konvertierung von Float- oder Real-Daten in Zeichendaten.
Wertausgabe
0 (Standard) Maximal 6 Ziffern. Verwenden Sie gegebenenfalls die wissenschaftliche Notation.
1 ist immer ein 8-Bit-Wert. Verwenden Sie immer die wissenschaftliche Schreibweise.
2 ist immer ein 16-Bit-Wert. Verwenden Sie immer die wissenschaftliche Schreibweise.
In der Tabelle unten stellt die linke Spalte den Stilwert bei der Konvertierung von Geld- oder Kleingeld- in Zeichendaten dar.
Wertausgabe
0 (Standardwert) Jeweils drei Ziffern auf der linken Seite des Dezimalpunkts werden nicht durch Kommas getrennt, und zwei Ziffern werden auf der rechten Seite des Dezimalpunkts verwendet. zum Beispiel 4235,98.
1 Jede dreistellige Zahl links vom Dezimalpunkt wird durch ein Komma getrennt, und die zweistellige Zahl rechts vom Dezimalpunkt wird verwendet, zum Beispiel 3.510,92.
2 Alle drei Ziffern auf der linken Seite des Dezimalpunkts werden nicht durch Kommas getrennt, und vier Ziffern werden auf der rechten Seite des Dezimalpunkts verwendet, zum Beispiel 4235,9819.
Rückgabetyp
Gibt denselben Wert wie Datentyp 0 zurück.
Hinweise
Implizite Konvertierungen sind solche, die keine CAST- oder CONVERT-Funktion angeben. Explizite Konvertierungen hingegen sind solche, für die die erforderliche CAST (CONVERT)-Funktion angegeben wurde. Das folgende Diagramm zeigt alle expliziten und impliziten Konvertierungen, die für die vom SQL Server-System bereitgestellten Datentypen verfügbar sind, einschließlich bigint und sql_variant.
Hinweis Da Unicode-Daten immer eine gerade Anzahl von Bytes verwenden, kommt es bei der Konvertierung zwischen binären oder varbinären Datentypen und von Unicode unterstützten Datentypen zu Fehlern Tipps. Anstatt beispielsweise den Hexadezimalwert 41 zurückzugeben, gibt diese Konvertierung den Hexadezimalwert 4100 zurück: SELECT CAST(CAST(0x41 AS nvarchar) AS varbinary)
Nein Unterstützt den automatischen Datentyp Konvertierung von Text- und Bilddatentypen. Sie können Textdaten explizit in Zeichendaten und Bilddaten in Binär- oder Varbinärdaten konvertieren, die maximale Länge beträgt jedoch 8000. SQL Server erzeugt eine Fehlermeldung, wenn eine falsche Konvertierung versucht wird (z. B. wird ein Zeichenausdruck, der Buchstaben enthält, in einen int-Ausdruck konvertiert).
Wenn die Ausgabe von CAST oder CONVERT eine Zeichenfolge ist und die Eingabe ebenfalls eine Zeichenfolge ist, hat die Ausgabe dieselbe Sortierung und Sortierbezeichnung wie die Eingabe. Wenn die Eingabe keine Zeichenfolge ist, verwendet die Ausgabe die Standardsortierung der Datenbank und die obligatorische Standardsortierungsbezeichnung. Weitere Informationen finden Sie unter Sortierpriorität.
Um der Ausgabe eine andere Sortierung zuzuweisen, wenden Sie die COLLATE-Klausel auf den Ergebnisausdruck der CAST- oder CONVERT-Funktion an. Zum Beispiel:
SELECT CAST(''abc'' AS varchar(5)) COLLATE French_CS_AS
Es gibt keine implizite Konvertierung vom Datentyp sql_variant hinsichtlich der Zuweisung, aber eine Konvertierung in sql_variant implizite Konvertierung.
Beim Konvertieren eines Zeichens oder Binärausdrucks (char, nchar, nvarchar, varchar, binär oder varbinär) in einen Ausdruck eines anderen Datentyps werden die Daten möglicherweise abgeschnitten, nur teilweise angezeigt oder das Ergebnis ist fehlerhaft ist zu kurz. Es kann kein Fehler angezeigt und zurückgegeben werden. Zusätzlich zu den in der folgenden Tabelle gezeigten Konvertierungen werden Konvertierungen in char, varchar, nchar, nvarchar, binär und varbinary abgeschnitten.
varchar *
nchar E
nvarchar E
money, smallmoney, numeric, decimal, float oder real char E
varchar E
nchar E
nvarchar E
* Die Ergebnislänge ist zu kurz für die Anzeige.
E Gibt einen Fehler zurück, da die Ergebnislänge zu kurz für die Anzeige ist.
Microsoft SQL Server garantiert nur, dass Roundtrip-Konvertierungen (d. h. Konvertierungen vom und zurück zum ursprünglichen Datentyp) in allen Versionen den gleichen Wert erzeugen. Das folgende Beispiel zeigt eine Roundtrip-Konvertierung:
DECLARE @myval decimal (5, 2)SET @myval = 193.57SELECT CAST(CAST(@myval AS varbinary(20)) AS decimal(10,5) )-- Oder mit CONVERTSELECT CONVERT(decimal(10,5), CONVERT(varbinary(20), @myval))
Versuchen Sie beispielsweise nicht, Binärwerte zu konstruieren und in Daten umzuwandeln Typen, die als numerische Datentypen klassifiziert sind. SQL Server garantiert nicht, dass das Ergebnis der Konvertierung eines dezimalen oder numerischen Datentyps in einen Binärtyp in allen Versionen von SQL Server gleich ist.
Das folgende Beispiel zeigt einen resultierenden Ausdruck, der zu kurz ist, um angezeigt zu werden.
USE pubsSELECT SUBSTRING(title, 1, 25) AS Title, CAST(ytd_sales AS char(2))FROM titlesWHERE type = ''trad_cook''
Das Folgende ist die Ergebnismenge:
Titel ----------Zwiebeln, Lauch und Knoblauch * Fünfzig Jahre in Buckingham * Sushi, irgendjemand *s) betroffen)
Beim Konvertieren von Datentypen mit unterschiedlichen Dezimalstellen wird der Wert auf die nächste Ziffer gekürzt. Beispielsweise gibt SELECT CAST(10.6496 AS int) 10 zurück.
Wenn bei der Konvertierung die Anzahl der Dezimalstellen des Zieldatentyps kleiner ist als die Anzahl der Dezimalstellen des Quelldatentyps, wird der umzuwandelnde Wert gerundet. Das Ergebnis von CAST(10,3496847 AS Geld) beträgt beispielsweise 10,3497 $.
SQL Server gibt eine Fehlermeldung zurück, wenn nicht numerische char-, nchar-, varchar- oder nvarchar-Daten in int, float, numerisch oder dezimal konvertiert werden. SQL Server gibt auch eine Fehlermeldung zurück, wenn eine leere Zeichenfolge („“) in eine numerische oder dezimale Zeichenfolge konvertiert wird.
Binärzeichenfolgendaten verwenden
Wenn Binär- oder Varbinärdaten in Zeichendaten konvertiert werden und nach x ein Wert mit einer ungeraden Anzahl von Ziffern angegeben wird, fügt SQL Server nach x 0 (Null) hinzu werden gerader Stellenwert.
Binärdaten enthalten Zeichen von 0 bis 9 und von A bis F (oder von a bis f) in Gruppen von zwei Zeichen. Binärzeichenfolgen müssen mit 0x beginnen. Um beispielsweise FF einzugeben, geben Sie 0xFF ein. Der Maximalwert ist ein 8000-Byte-Binärwert, wobei jedes Byte den Maximalwert FF hat. Der Datentyp Binär kann nicht für hexadezimale Daten, sondern für Bitmuster verwendet werden. Die Genauigkeit der Konvertierungs- und Berechnungsergebnisse von als Binärdaten gespeicherten Hexadezimalzahlen kann nicht garantiert werden.
Bei der Angabe der Länge des binären Datentyps werden alle zwei Zeichen als eine Einheitslänge gezählt. Eine Länge von 10 bedeutet, dass 10 Bigramme eingegeben werden.
Eine leere Binärzeichenfolge, dargestellt durch 0x, kann als Binärdaten gespeichert werden.
Beispiel
A. Verwendung von CAST und CONVERT
In jedem Beispiel werden Buchtitel abgerufen (die erste Ziffer der aktuellen Verkäufe dieser Bücher ist 3) und konvertiert ytd_sales dieser Bücher in char(20).
-- Verwenden Sie CAST.USE pubsGOSELECT SUBSTRING(title, 1, 30) AS Title, ytd_salesFROM titlesWHERE CAST(ytd_sales AS char(20)) LIKE ''3%''GO-- Verwenden Sie CONVERT.USE pubsGOSELECT SUBSTRING(title, 1, 30) AS Title, ytd_salesFROM titlesWHERE CONVERT(char(20), ytd_sales) LIKE ''3%''GO
Das Folgende ist die Ergebnismenge jeder Abfrage:
Titel ytd_sales ------------------------------ ----------- Kochen mit Computern: Surrep 3876 Computerphobisch UND Nicht-Phobisch 375 Emotionale Sicherheit: Ein neuer Algo 3336 Zwiebeln, Lauch und Knoblauch: Gurren 375 (4 Zeile(n) betroffen)
B. Verwenden Sie CAST mit arithmetischen Operatoren
Das folgende Beispiel führt eine separate Spaltenberechnung (Kopien) durch, indem die bisherigen Gesamtverkäufe (ytd_sales) durch den Preis jedes Buchs (Preis) dividiert werden. Dieses Ergebnis wird nach dem Runden auf die nächste Ganzzahl in den Datentyp int konvertiert.
USE pubsGOSELECT CAST(ROUND(ytd_sales/price, 0) AS int) AS ''Copies''FROM titlesGO
Das Folgende ist die Ergebnismenge:
Copies - -- --- 205 324 6262 205 102 7440 NULL 383 205 NULL 17 187 16 204 418 18 1263 273 (18 Zeile(n) betroffen)
C. Verwenden Sie CAST für die Verkettung
Beispiel Unten verwenden Sie die CAST-Datentypkonvertierungsfunktion, um Nicht-Zeichen- und Nicht-Binär-Ausdrücke zu verketten.
USE pubsGOSELECT ''The price is '' + CAST(price AS varchar(12))FROM titlesWHERE price > 10.00GO
Das Folgende ist die Ergebnismenge:
- ------------------ Der Preis beträgt 19,99 Der Preis beträgt 11,95 Der Preis beträgt 19,99 Der Preis beträgt 22,95 Der Preis beträgt 20,00 Der Preis beträgt 21,59 Ist 10,95 Der Preis beträgt 19,99 Der Preis beträgt 20,95. Der Preis beträgt 11,95. Der Preis beträgt 14,99 (12 Zeile(n) betroffen)
D. Verwenden Sie CAST, um besser lesbaren Text zu erhalten
Im folgenden Beispiel wird CAST in einer Auswahlliste verwendet, um die Titelspalte in eine char(50)-Spalte umzuwandeln, damit das Ergebnis besser lesbar ist.
USE pubsGOSELECT CAST(title AS char(50)), ytd_salesFROM titlesWHERE type = ''trad_cook''GO
Das Folgende ist die Ergebnismenge: ytd_sales---- ---- ------------------------------------------- ---- --- --Zwiebeln, Lauch und Knoblauch: Kochgeheimnisse der 375Fifty Years in Buckingham Palace Kitchens 15096Sushi, jemand? 4095(3 Zeile(n) betroffen)
Verwendung mit LIKE-Klausel CAST
Das folgende Beispiel konvertiert eine int-Spalte (ytd_sales-Spalte) in eine char(20)-Spalte zur Verwendung mit der LIKE-Klausel.
USE pubsGOSELECT title,
-
ytd_salesFROM titlesWHERE CAST(ytd_sales AS char(20)) LIKE ''15%'' AND type = ''trad_cook''GO
Das Folgende ist die Ergebnismenge:
Titel ytd_sales ----------------------- ------------------------------------
Mysql:
Die Funktionen CAST() und CONVERT() von MySQL können verwendet werden, um einen Wert eines Typs abzurufen und einen Wert eines anderen Typs zu generieren. Die spezifische Syntax der beiden lautet wie folgt:
1 CAST (Wert als Typ);
2 CONVERT (Wert, Typ);
ist CAST (xxx AS-Typ). ), CONVERT (xxx, Typ).
Die Typen, die konvertiert werden können, sind begrenzt. Dieser Typ kann einer der folgenden Werte sein:
Binär, mit der Wirkung eines binären Präfixes: BINARY
Zeichentyp, kann Parameter annehmen: CHAR()
Datum: DATUM
Zeit: ZEIT
Datumszeittyp: DATETIME
Gleitkommazahl: DEZIMAL
Ganzzahl: VORZEICHNET
Ganzzahl ohne Vorzeichen: UNSIGNED
Hier ein paar Beispiele:
Beispiel 1
1 mysql>
2 +--- ----+
3 |. CONVERT('23',SIGNED) |
4 +----- - -----------------+
5 |. 23 |
6 +------------- - -------+
7 1 Zeileneinschub
Beispiel 2
1 mysql> SELECTCAST('125e342.83'ASsigned);
2 +--------------------------------+
3 | ' Zugewiesen) |
4 +---------------------------------+
5 |. 125 |
6 +--------------------------------+
7 1 Zeileneinschub
Beispiel 3
1 mysql>
2 +---------- ------ --------+
3 |. CAST('3.35'ASsigned) |
4 +----------- ------ -------+
5 |
6 +------------------ ------ +
7 1 Zeileneinschub
Konvertieren Sie wie im obigen Beispiel varchar in int mit cast(a as signiert), wobei a eine Zeichenfolge vom Typ varchar ist.
Beispiel 4
In SQL Server demonstriert der folgende Code das hexadezimale Speicherergebnis der Datumsspeicherung, wenn die datetime-Variable nur ein einfaches Datum und eine einfache Uhrzeit enthält.
01 DECLARE @dt datetime
02
03 --Einfaches Datum
04 SET @dt='1900-1-2'
05 SELECT CAST(@dt asbinary(8))
06 -- Ergebnis: 0x0000000100000000
07
08 --Einfache Zeit
09 SET @dt='00:00:01'
10 SELECT CAST(@dt asbinary(8))
11 -- Ergebnis: 0x000000000000012C
MySQL Der Typ Die Konvertierung ist dieselbe wie die von SQL Server, die Typparameter unterscheiden sich jedoch etwas: CAST(xxx AS-Typ), CONVERT(xxx, Typ).
