TRY_CONVERT // Konvertierung nach decimal, numeric

marcus

SQL Server stellt für die Speicherung von exakten Dezimalzahlen die folgen­den Datentypen zur Verfügung:

  • decimal(p, s)
  • numeric(p, s)

Die Datentypen decimal und numeric sind exakte Dezimalzahlen, für die sowohl die Anzahl aller Dezimalstellen (Parameter p) als auch die Anzahl der Dezimalstellen nach dem Komma (Parameter s) angegeben werden kann. Es gibt keinen funktionalen Unterschied zwischen diesen beiden Datentypen. Dieser Artikel beschränkt sich daher auf die Verwendung des Datentyps decimal.

Wann immer möglich, ist die benötigte Genauigkeit einer Dezimalzahl anzugeben. So erfolgt die Angabe eines Preises üblicherweise mit zwei Nachkomma­stellen.

Die Funktion TRY_CONVERT rundet Zahlen, die als typisierte Zahl oder auch als Text übergeben werden, bei einer Konvertierung in den Datentyp decimal immer auf die letzte Nachkommastelle. Mehr hierzu findet sich in der Microsoft Online Dokumentation unter CAST and CONVERT. Die Konver­tie­rung eines Zahl­wertes in den Zieldatentyp decimal kann damit den nominalen Ausgangswert ändern.

Das Rückgabeergebnis hängt davon ab, in welchem Datentyp eine Zahl übergeben wird. Zu unterscheiden ist hier die Übergabe eines Parameters expression als Wert vom Typ Text und als Wert vom Typ Kein Text. Bei der Untersuchung, wie eine sichere Typ-Konvertierung erfolgen kann, berücksichtigt dieser Artikel nur sinnvolle Datentypen, die wir tatsächlich in einen Wert vom Typ decimal konvertieren würden. Das sind neben ganzen und Dezimalzahlen auch Texte. Letzteres ist immer dann der Fall, wenn zu Beispiel Werte aus einer Text-Datei (CSV, JSON oder XML) zu konvertieren sind.

Der Artikel unterscheidet der Einfachheit halber zwischen den Datentypen Text und Kein Text und untersucht das Verhalten der Funktion TRY_CONVERT bei der Konvertierung von Werten der beiden allgemeinen Datentypen. Bei dem allgemeinen Datentyp Kein Text werden nur Datentypen berücksichtigt, die wir gegebenenfalls auch in einen Wert vom Typ int konvertieren würden. Für beide allgemeinen Datentypen wird ein Ausdruck für eine sichere Typ-Konvertierung vorgestellt.

Inhalt

Text

Wird ein Wert vom Typ Text an die Funktion TRY_CONVERT übergeben, so muss der übergebene Wert eine Zahl darstellen. Als Dezimaltrennzeichen wird immer ein Punkt erwartet. Tausendertrennzeichen sind nicht zulässig. Enthält der Parameter ein Komma, so kann der Wert nicht konvertiert werden und die Funktion TRY_CONVERT liefert NULL zurück. Führende und folgende Leer­zeichen werden von der Funktion TRY_CONVERT abge­schnitten und verhin­dern nicht die Konvertierung. Ein leerer String wird als NULL übersetzt.

Das folgende Code-Beispiel zeigt die Ergebnisse der TRY_CONVERT Funktion für verschiedene gültige und ungültige Dezimalzahlen, die als Text vom Typ nvarchar übergeben und in eine Zahl vom Typ decimal(5, 2) konvertiert werden:

 1: SELECT TRY_CONVERT(decimal(5,2), NULL        ) -- > NULL
 2: SELECT TRY_CONVERT(decimal(5,2), N'123'      ) -- > 123.00
 3: SELECT TRY_CONVERT(decimal(5,2), N'123,456'  ) -- > NULL
 4: SELECT TRY_CONVERT(decimal(5,2), N'123.456'  ) -- > 123.46
 5: SELECT TRY_CONVERT(decimal(5,2), N''         ) -- > NULL
 6: SELECT TRY_CONVERT(decimal(5,2), N' '        ) -- > NULL
 7: SELECT TRY_CONVERT(decimal(5,2), N' 123.456' ) -- > 123.46
 8: SELECT TRY_CONVERT(decimal(5,2), N'123.456 ' ) -- > 123.46
 9: SELECT TRY_CONVERT(decimal(5,2), N'1234.56 ' ) -- > NULL
10: SELECT TRY_CONVERT(decimal(5,2), N'123456E-3') -- > NULL

Zu beachten ist hier, dass der Eingangswert in Zeile 3 ein ungültiges Dezimal­trennzeichen enthält und nicht konvertiert werden kann. In Zeile 9 kann die Zahl 1234.56 nicht konvertiert werden, da diese zu viele Vorkommastellen aufweist. Maximal zulässig sind 5 – 2 = 3 Vorkommastellen. Eine Dezimalzahl in wissen­schaftlicher Notation – wie in Zeile 10 dargestellt – kann ebenfalls nicht konvertiert werden. Eine gültige Dezimalzahl mit mehr Nachkommastellen als in dem Parameter s angegeben ist kann, wird gerundet.

Kein Text

Wird eine Zahl nicht als Text an die Funktion TRY_CONVERT übergeben, sondern als eine typisierte Zahl, kann die Funktion jede beliebige Zahl in einen Wert vom Typ decimal konvertieren.

Das folgende Code-Beispiel zeigt die Ergebnisse der TRY_CONVERT Funktion für verschiedene gültige und ungültige Zahlen, die als typisierte Werte übergeben und jeweils in eine Zahl vom Typ decimal(5, 2) konvertiert werden:

 1: SELECT TRY_CONVERT(decimal(5,2), NULL     ) -- > NULL
 2: SELECT TRY_CONVERT(decimal(5,2), 123      ) -- > 123.00
 3: SELECT TRY_CONVERT(decimal(5,2), 123.456  ) -- > 123.46
 4: SELECT TRY_CONVERT(decimal(5,2), 1234.56  ) -- > NULL
 5: SELECT TRY_CONVERT(decimal(5,2), 123456E-3) -- > 123.46

In den Zeilen 3 und 4 wird eine Dezimal­zahl mit Nachkom­mastellen als Wert vom Typ float übergeben. In Zeile 4 kann die Zahl 1234.56 nicht konvertiert werden, da diese zu viele Vorkommastellen aufweist. Die Dezimalzahl in wissen­schaftlicher Notation in Zeile 5 kann, wenn sie als typisierte Zahl übergeben wird, konvertiert werden.

Sichere Typ-Konvertierung

In Deutscher Notation wird eine Dezimalzahl mit dem Dezimaltrennzeichen Komma notiert. Die Konvertierung von Dezimalzahlen, die als Text geliefert werden erfordert daher bei Zahlen die in nicht amerikanischer Notation geliefert werden eine besondere Behandlung.

SQL Server unterstützt bei der Konvertierung in die Datentypen decimal und float keine formatierte Angabe des Eingangs­wertes. Zulässig ist daher ausschließlich ein Punkt als Dezimal­trenn­zeichen. Tausendertrennzeichen sind unzulässig. Enthält der Eingangswert ein Komma oder einen Punkt, ist dieses Zeichen daher immer als Dezimal­trenn­zeichen zu interpretieren.

Eine besondere Beachtung muss der Umstand finden, dass die Funktion TRY_CONVERT Zahlen in wissenschaftlicher Notation, die als Text übergeben werden (zum Beispiel ‘123456E-3’), nicht konvertiert und ein NULL zurückgibt. Eine Konvertierung einer wissenschaftlichen Zahl, die als Text übergeben wird, in den Zieldatentyp float ist möglich. Um auch eine Konvertierung einer wissenschaftlichen Zahl in einen Wert vom Typ decimal zu unterstützen, ist der Eingangswert übergangsweise zunächst in einen Wert vom Typ float und anschließend in einen Wert vom Typ decimal zu konvertieren. Im Unterschied zu der Konvertierung einer leeren Zeichenfolge in den Datentyp decimal, ist das Ergebnis der Konvertierung einer leeren Zeichenfolge in den Datentyp float die Zahl 0 und nicht NULL. Dieses Verhalten kann als Fehler betrachtet werden. Leere Zeichenfolgen sind daher vor der Konvertierung in ein NULL zu übersetzen.

Damit ist die sicher Konvertierung von Texten in einen Wert vom Typ decimal alles andere als trivial.

Der folgende Codeblock zeigt die sichere Typ-Konvertierung unter Berücksichtigung der oben genannten Sonderfälle:

 1: TRY_CONVERT( [decimal|numeric](p, s)
 2:             ,TRY_CONVERT( float
 3:                          ,REPLACE( CASE WHEN TRIM([Input]) = ''
 4:                                       THEN NULL 
 5:                                       ELSE [Input]
 6:                                    END
 7:                                   , ','
 8                                    , '.'
 9:                                  )
10:                         )
11:            ) AS [Output]

Und hier ein Beispiel für die Anwendung…

 1: DECLARE @input as nvarchar(30);
 2: SET @input = '123,456';
 3: SELECT TRY_CONVERT
 4:        (
 5:          decimal(5, 2)
 6:         ,TRY_CONVERT
 7:          (
 8             float
 9:           ,REPLACE( CASE WHEN TRIM(@input) = ''
10:                        THEN NULL 
11:                        ELSE @input
12:                     END
13:                    , ','
14:                    , '.'
15:                   )
16:          )
17:        ) AS [Output];

Dieses Beispiel konvertiert eine leere Zeichenfolge zu einem NULL.

Zusammenfassung

Bei der Konvertierung von Texten in einen Wert vom Typ decimal, muss die Notation der Zahlen Berücksichtigung finden, da ein Komma als Dezimaltrennzeichen nicht unterstützt wird und damit zu dem Ergebnis NULL führen würde. Kommas werden daher vor der Konvertierung durch Punkte ersetzt. Um auch die Konvertierung von Zahlen in wissenschaftlicher Schreibweise zu unterstützen, wird der Eingangswert zunächst in eine Zahl vom Typ float konvertiert und anschließend nach decimal. Bei der Konvertierung eines Textes in den Datentyp float, ist eine leere Zeichenfolge zunächst durch ein NULL zu ersetzen, da sonst das Ergebnis der Konvertierung einer leeren Zeichenfolge der eine Zahl mit dem Wert 0 wäre.

Verwandet Artikel