Ich habe eine Tabelle mit einer varchar-Spalte und möchte Werte finden, die mit einer bestimmten Zahl übereinstimmen. Also sagen wir, dass die Spalte die folgenden Einträge enthält (außer mit Millionen von Zeilen im wirklichen Leben):
%Vor%Also entscheide ich, dass ich alle Zeilen, die numerisch sind, 123456789012 eine Anweisung schreiben soll, die ungefähr so aussieht:
%Vor%Es sollte die erste und die letzte Zeile zurückgeben, aber die gesamte Abfrage springt in die Luft, weil sie "23 45" und "713? 2" nicht in bigint konvertieren kann.
Gibt es eine andere Möglichkeit, die Konvertierung durchzuführen, die NULL für Werte zurückgibt, die nicht konvertiert werden können?
SQL Server garantiert keinen Booleschen Operator-Kurzschluss, siehe Auf SQL Server Boolescher Operator Kurzschluss . Daher sind alle Lösungen, die ISNUMERIC(...) AND CAST(...) verwenden, grundsätzlich fehlerhaft (sie können funktionieren, aber sie können später beliebig abhängig vom generierten Plan ausfallen). Eine bessere Lösung ist die Verwendung von CASE, wie Thomas vorschlägt: CASE ISNUMERIC(...) WHEN 1 THEN CAST(...) ELSE NULL END . Aber, wie gbn gezeigt hat, ist ISNUMERIC notorisch knifflig beim Identifizieren, was "numerisch" bedeutet, und in vielen Fällen, wo man erwarten würde, dass es 0 zurückgibt, gibt es 1 zurück. So vermischt man den CASE mit dem LIKE:
Aber das wirkliche Problem ist, dass wenn Sie Millionen von Zeilen haben und Sie sie so durchsuchen müssen, Sie immer Ende-zu-Ende Scannen werden, da der Ausdruck nicht SARG-fähig ist (egal wie wir umschreiben es). Das eigentliche Problem ist die Datenreinheit und sollte auf der entsprechenden Ebene angegangen werden, auf der die Daten enthalten sind. Eine andere Sache zu prüfen ist, ob es möglich ist, eine persistente berechnete Spalte mit diesem Ausdruck zu erstellen und einen gefilterten Index darauf zu erstellen, der NULL (dh nicht-numerisch) eliminiert. Das würde die Dinge etwas beschleunigen.
Wenn Sie SQL Server 2012 verwenden, können Sie die zwei neuen Methoden verwenden:
Beide Methoden sind äquivalent. Wenn der Cast erfolgreich ist, wird ein Wert in den angegebenen Datentyp zurückgegeben. Andernfalls wird null zurückgegeben. Der einzige Unterschied ist, dass CONVERT SQL Server-spezifisch ist, CAST ist ANSI. Durch die Verwendung von CAST wird Ihr Code portabler (obwohl nicht sicher, ob ein anderer Datenbankanbieter TRY_CAST implementiert)
ISNUMERIC akzeptiert leere Zeichenfolgen und Werte wie 1.23 oder 5E-04 und kann daher unzuverlässig sein.
Und Sie wissen nicht, in welcher Reihenfolge die Dinge ausgewertet werden, damit es immer noch fehlschlagen kann (SQL ist deklarativ, nicht prozedural, daher wird die WHERE-Klausel wahrscheinlich nicht von links nach rechts ausgewertet)
Also:
Etwas wie:
%Vor%Bearbeiten: schnelles Beispiel, das fehlschlägt.
%Vor%Die Funktion ISNUMERIC () sollte Ihnen geben, was Sie brauchen.
%Vor%Und um eine Fallaussage wie Thomas vorgeschlagen hinzuzufügen:
%Vor%Zusätzlich können Sie eine CASE-Anweisung verwenden, um Nullwerte zu erhalten.
%Vor%Wenn Sie eine zusätzliche Filterung benötigen, können Sie für Zahlen, die nicht in bigint umgewandelt werden können, anstelle von ISNUMERIC Folgendes verwenden:
PATINDEX ('% [^ 0-9]%', MyColumn)) = 0
Wenn Sie Dezimalwerte anstelle von Ganzzahlen benötigen, müssen Sie stattdessen in den Float-Modus umwandeln und die Regex in '% [^ 0-9.]%'
ändernTags und Links tsql invalid-characters