TRY_CONVERT // Konvertierung nach bigint, int, smallint, tinyint

marcus

Dieser Artikel beschreibt die sichere Konvertierung von Eingangswerten in die Datentypen bigint, int, smallint und tinyint. Eine vermeintlich einfache Aufgabe, sollte man denken. Was passiert zum Beispiel, wenn eine Dezimalzahl oder eine leere Zeichenfolge in einen Wert vom Typ int konvertiert werden soll. Wird der konvertierte Wert automatisch gerundet? Gibt Funktion bei der Konvertierung ein NULL zurück?

Die Online Microsoft Online Dokumentation der Funktion TRY_CONVERT (oder vielmehr CAST und CONVERT) gibt an dass der zu konvertierende Wert ein beliebiger Ausdruck sein kann. Damit kann sowohl ein Wert vom allgemeinen Datentyp Text (zum Beispiel nvarchar) übergeben werden, die Funktion aber auch Werte eines beliebigen Datentyps akzeptiert.

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 bigint, int, smallint oder tinyint konvertieren würden. Das sind neben ganzen Zahlen 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

Wenn wir einen Text, der eine Zahl darstellen soll, in einen Wert vom Typ int konvertieren möchten sind unter anderem die folgenden Szenarien zu untersuchen:

  • Der Text enthält eine gültige Zahle
  • Die Zahl enthält ein Komme als Dezimaltrennzeichen
  • Der Text enthält Tausendertrennzeichen und ggf. Dezimaltrennzeichen
  • Der Text ist eine leere Zeichenfolge oder enthält nur Leerzeichen

Nachfolgend werden Aufrufe der Funktion TRY_CONVERT dargestellt, die Werte, wie sie oben beschrieben wurden, in einen Wert vom Typ int konvertieren. Hinter den jeweiligen Anweisungen ist das zurückgegebene Ergebnis notiert:

 1. SELECT TRY_CONVERT(int, NULL    ) -- > NULL
 2: SELECT TRY_CONVERT(int, N'123'  ) -- > 123
 3: SELECT TRY_CONVERT(int, N'123,4') -- > NULL
 4: SELECT TRY_CONVERT(int, N'1,234') -- > NULL
 5: SELECT TRY_CONVERT(int, N'123.4') -- > NULL
 6: SELECT TRY_CONVERT(int, N'1.234') -- > NULL
 7: SELECT TRY_CONVERT(int, N''     ) -- > 0
 8: SELECT TRY_CONVERT(int, N' '    ) -- > 0
 9: SELECT TRY_CONVERT(int, N' 123' ) -- > 123
10: SELECT TRY_CONVERT(int, N'123 ' ) -- > 123

Zahlen, die offensichtlich eine ganze Zahl darstellen, können wie erwartet in eine Zahl vom Typ int konvertiert werden. Führende und folgende Leerzeichen beeinflussen das Ergebnis nicht. Dezimalzahlen, die ein Dezimaltrennzeichen enthalten, können demnach nicht in eine Zahl vom Typ int konvertiert werden. Hierbei ist es unerheblich, ob der Wert ein Komma (Deutsche Notation) oder einen Punkt als Dezimaltrennzeichen (Amerikanische Notation) enthält. Leere Zeichenfolgen oder Zeichenfolgen, die nur aus Leerzeichen bestehen, werden zu dem Wert 0 konvertiert.

Damit hält das Ergebnis bereits einige Überraschungen bereit. Dezimalzahlen können nicht konvertiert werden und Leere Zeichenfolgen ergeben den Wert 0. Während das erste Ergebnis durchaus nachvollziehbar ist, ist das zweite Ergebnis schlicht falsch. Ein NULL kann nicht in einer Text-Datei geliefert werden. Ob eine leere Zeichenfolge als NULL zu interpretieren ist, ist eine Frage der Spezifikation der Datenquelle. Eine leere Zeichenfolge als 0 zu interpretieren, ist fachlich falsch.

Kein Text

Wenn wir Werte konvertieren möchten, die nicht als Text an die Funktion TRY_CONVERT übergeben werden, sind lediglich die folgenden Szenarien zu untersuchen:

  • Der übergebene Wert ist eine ganze Zahl
  • Die übergebene Wert ist eine Dezimaltrennzeichen

Nachfolgend werden Aufrufe der Funktion TRY_CONVERT dargestellt, die Werte, wie sie oben beschrieben wurden, in einen Wert vom Typ int konvertieren. Hinter den jeweiligen Anweisungen ist das zurückgegebene Ergebnis notiert:

 1: SELECT TRY_CONVERT(int, 2147483648)    -- > NULL
 2: SELECT TRY_CONVERT(int, 123       )    -- > 123
 3: SELECT TRY_CONVERT(int, 1234.5    )    -- > 123

Die erste Zahl ist um den Wert größer als die größte positive Zahl vom Typ int. Das Ergebnis liefert daher erwartungsgemäß ein NULL zurück. Dezimalzahlen können im Unterschied zu oben in einen Wert vom Typ int konvertiert werden. SQL Server führt jedoch keine Rundung aus. Die Funktion TRY_CONVERT liefert nur den ganzzahligen Anteil der Dezimalzahl zurück.

Sichere Typ-Konvertierung

Die beiden Code-Beispiele haben gezeigt, dass mindestens die Konvertierung leerer Zeichenfolgen eine besondere Berücksichtigung erforderlich macht, wenn eine Konvertierung zu dem Wert 0 nicht zulässig ist. Die Konvertierung einer typisierten Dezimalzahl kann ebenfalls Anlass für eine explizite Behandlung sein, wenn eine Rundung erforderlich ist. Alternativ kann eine Konvertierung in eine Dezimalzahl mit 0 Nachkommastellen hier die korrekte Vorgehensweise darstellen.

Der folgende Codeblock zeigt die sichere Typ-Konvertierung unter Berücksichtigung der Konvertierung von leeren Zeichenfolgen zu einem NULL.

 1: TRY_CONVERT( [bigint|int|smallint|tinyint]
 2:             ,CASE WHEN TRIM([Input]) = ''
 3:                 THEN NULL
 4:                 ELSE [Input]
 5:              END
 6:            ) AS [Output]

Und hier ein Beispiel für die Anwendung…

 1: DECLARE @input AS nvarchar(30);
 2: SET @input = '123';
 3: SELECT TRY_CONVERT(int, 
                       CASE WHEN TRIM(@input) = '' THEN NULL ELSE @input END
                      ) AS [Output]

Dieses Beispiel konvertiert eine leere Zeichenfolge zu einem NULL.

Zusammenfassung

Bei der Konvertierung von Texten in einen Wert vom Typ int, muss geklärt werden, welche Daten geliefert werden und wie Sonderfälle zu berücksichtigen sind. Eine leere Zeichenfolge wird von der Funktion TRY_CONVERT zu einer 0 konvertiert. Dezimalzahlen, die als Text vorliegen, werden in den ganzzahligen Anteil der Dezimalzahl konvertiert. Wenn eine Rundung gewünscht, dann ist dieses bei der Konvertierung zu berücksichtigen.

Typisierte Dezimalzahlen werden bei der Konvertierung in einen Wert vom Typ int nicht gerundet. Auch hier ist zu klären, ob dieses Ergebnis zulässig ist, oder ob dieses Feature bei der Konvertierung berücksichtigt werden muss.

Verwandet Artikel