SQL Serverで文字列が数値かどうかを確認する

SQL Serverで数値をチェックするには、TRY_CONVERT関数 や ISNUMERIC関数を使用します。TRY_CONVERT関数は型変換をする関数で、数値型へ変換できるかで確認を行います。ISNUMERIC関数は数値確認をする関数ですが、いくつか注意点があります。

TRY_CONVERT関数で数値確認する方法

TRY_CONVERT関数は型変換をする関数です。TRY_CONVERT関数を使用して数値型に型変換をし、失敗した場合は 数値ではない と判断できます。 第一引数にデータ型(intやdecimalなど)を、第二引数にチェックしたい式(文字列やテーブルのカラム名など)を渡します。戻り値は 変換に失敗した場合はNULLを、変換できた場合は変換後の値を返します。 
結果(int値     または NULL -> 数値ではない) = TRY_CONVERT(int, <式>)
結果(decimal値 または NULL -> 数値ではない) = TRY_CONVERT(decimal, <式>)

次のサンプルSQLはすべて NULL以外 の値を返します。

--結果 = NOT NULL
SELECT TRY_CONVERT(int    , '123456');
SELECT TRY_CONVERT(decimal, '123456');
SELECT TRY_CONVERT(decimal, '123456.0');
SELECT TRY_CONVERT(decimal, '123456.789');
実行結果
実行結果

次のサンプルSQLはすべて NULL を返します。int型に少数を渡すとNULLが返るので、少数の判定をする場合は decimal または float を指定します。

--結果 = NULL
SELECT TRY_CONVERT(int    , '123456.789');
SELECT TRY_CONVERT(int    , '123456.0');
SELECT TRY_CONVERT(int    , '11234abc');
SELECT TRY_CONVERT(decimal, '11234abc');
実行結果
実行結果

次のSQLは テーブル「SampleTable」の 列「Password」が 数値のみ のレコードを検索します。

--テーブル「SampleTable」の 列「Password」が 数値のみ のレコードを検索
SELECT *
    FROM [SampleTable]
    WHERE TRY_CONVERT(int, [Password]) IS NOT NULL;
実行結果
実行結果

ISNUMERIC関数で数値確認する方法

ISNUMERIC関数の引数に チェックしたい 文字列 や テーブルのカラム名 などを指定します。結果はINT型で返され、数値の場合は 1 を、それ以外は 0 を返します。 
結果(1:数値 0:数値ではない) = ISNUMERIC(<式>)

次のサンプルSQLは 文字列 '123456'、'123456.678' と '123abc' が数値かを判定しています。

--結果 = 1
SELECT ISNUMERIC('123456');
SELECT ISNUMERIC('123456.789');

--結果 = 0
SELECT ISNUMERIC('123abc');
実行結果
実行結果

次のSQLは テーブル「SampleTable」の 列「Password」が 数値のみ のレコードを検索します。

--テーブル「SampleTable」の 列「Password」が 数値のみ のレコードを検索
SELECT *
    FROM [SampleTable]
    WHERE ISNUMERIC([Password]) = 1;
実行結果
実行結果

ISNUMERIC関数の注意点

ISNUMERIC関数を使用すると手軽に数値のチェックができますが、通貨記号や指数も数値として判定されます。

次のSQLは全て1(数値判定)を返します。

--結果 = 1
SELECT ISNUMERIC('1E2');
SELECT ISNUMERIC(',');
SELECT ISNUMERIC('.');
SELECT ISNUMERIC('$');
SELECT ISNUMERIC('\');
実行結果
実行結果

参考資料

検証環境