T-SQL: 数値型や日付型かどうかを確認して変換する方法

数値型や日付型かどうかを確認して変換する

クエリーを書いていると、文字列の値が数値型や日付型かどうかを確認して、有効な場合はそれぞれの型に変換したいような時がありますよね。

今回はそんな時に便利な関数とクエリーをご紹介します。


TRY_CAST や TRY_CONVERT を使う方法

もし、SQL Server のバージョンが 2012 以降なのであれば、簡単なのは TRY_CAST と TRY_CONVERT を使う方法です。

構文は次の通りで、それぞれ CAST や CONVERT と同じで、ファンクション名に TRY_ がつくだけです。 

TRY_CAST ( 変換する値 AS データ型 [ ( 長さ ) ] )  

TRY_CONVERT ( データ型 [ ( 長さ ) ] , 変換する値 [ , スタイル ] )

CAST と CONVERT は変換が失敗した時にエラーになりますが、TRY_CAST と TRY_CONVERT は変換が失敗すると NULL を返します。


それでは実際に使ってみましょう。

次のような MONEY 型に変換したい NVARCHAR(20) 型の @InputNumberString1 と　@InputNumberString2 と、DATE 型に変換したい @InputDateString1 と @InputDateString2 があるとします。

それぞれ 1 で終わる変数は変換に失敗する値、2 は変換に成功する値が入っています。

これらの値を TRY_CAST、TRY_CONVERT を使って変換すると次のようになります。 

DECLARE @InputNumberString1 NVARCHAR(20) = N'2500円', -- Invalid
	@InputNumberString2 NVARCHAR(20) = N'3500',	  -- Valid
	@InputDateString1   NVARCHAR(20) = N'2019-02-29', -- Invalid
	@InputDateString2   NVARCHAR(20) = N'2019-02-28'; -- Valid

SELECT	TRY_CAST(@InputNumberString1 AS MONEY) AS Price1,
	TRY_CAST(@InputNumberString2 AS MONEY) AS Price2,
	TRY_CONVERT(DATE, @InputDateString1) AS Date1,
	TRY_CONVERT(DATE, @InputDateString2) AS Date2;

数値型や日付型かどうかを確認して変換する方法 1

変換できない値はエラーにならずに NULL が返っていますね！

ISNUMERIC や ISDATE でチェックしてから変換する

SQL Server のバージョンが 2012 より前のバージョンの場合は、TRY_CAST や TRY_CONVERT がないので、エラーを出さない為には変換する前に、値が有効か自分でチェックしなければなりません。

値が数値型として有効かどうかを調べるには ISNUMERIC(値) を、日付型として有効かどうかを調べるには ISDATE(値) を使うことができます。

両方の関数とも、有効な場合は 1 を、そうでない場合は 0 を返します。


先ほどと同様の値をISNUMERIC や ISDATE でチェックしてから変換してみましょう。 

DECLARE @InputNumberString1 NVARCHAR(20) = N'2500円', -- Invalid
	@InputNumberString2 NVARCHAR(20) = N'3500', -- Valid
	@InputDateString1   NVARCHAR(20) = N'2019-02-29', -- Invalid
	@InputDateString2   NVARCHAR(20) = N'2019-02-28'; -- Valid

SELECT	CASE 
	   WHEN ISNUMERIC(@InputNumberString1) = 1 
	      THEN CAST(@InputNumberString1 AS MONEY)
	      ELSE NULL
	END AS Price1,
	CASE 
	   WHEN ISNUMERIC(@InputNumberString2) = 1 
	      THEN CAST(@InputNumberString2 AS MONEY)
	      ELSE NULL
	END AS Price2,
	CASE 
	   WHEN ISDATE(@InputDateString1) = 1 
	      THEN CONVERT(DATE, @InputDateString1)
	      ELSE NULL
	END AS Date1,
	CASE 
	   WHEN ISDATE(@InputDateString2) = 1 
	      THEN CONVERT(DATE, @InputDateString2)
	      ELSE NULL
	END AS Date2;

数値型や日付型かどうかを確認して変換する方法 2

先ほどと同様の結果が得られましたね。

今回の例では、数値型に CAST、日付型に CONVERT を使っていますが、特に意味はなく CAST と CONVERT を両方使いたかったからだけで、どちらを使っても大丈夫です！


TRY_CAST や TRY_CONVERT が使える時は、できるだけ使ったほうがシンプルですし良いですね。

ただ、TRY_CAST や TRY_CONVERT でも、明示的に許可されない型を変換をしようとするとエラーになります。

現状、NVARCHAR から明示的に許可されない変換は IMAGE 型へだけだと思いますが、その他についてはマイクロソフトの 「 CAST および CONVERT のページ 」 の 「 暗黙的な変換 」 の表で確認してみてください。


また、確実に変換する値が有効なことがわかっている場合は、TRY_CAST や TRY_CONVERT ではなく、CAST や CONVERT を使ったほうがパフォーマンス的に良いので、そうしてくださいね！

© 2010-2024 SQL Server 入門