Top > SQL 関数一覧 (T‐Z) > VALIDATE_CONVERSION

指定したデータ型に変換可能が判定する Oracle 12cr2

syntax

VALIDATE_CONVERSION ( expr AS type_name [, format [, nls_param]] ) Oracle 12cr2

parameter

expr文字列式、数値式
type_nameデータ型の型名称
formatフォーマット文字列
nls_param各国語サポートパラメータNLS セッションパラメータ依存

return

  • expr を指定書式にて type_name 型に変換可能な場合には 1 、不可の場合には 0 を戻す。

avail

  • SQL および PL/SQL の両方で使用可能。

指定できるデータ型

  • 数値型 BINARY_DOUBLE / BINARY_FLOAT / NUMBER
  • 日付型 DATE / TIMESTAMP / TIME WITH TIME ZONE / TIMESTAMP WITH LOCAL TIME ZONE
  • 期間型 INTERVAL DAY TO SECOND / INTERVAL YEAR TO MONTH (※1)

NUMBER(3) のような精度は指定できない。(数値型はフォーマット文字列でチェックする)
指定すると "ORA-43910: 精度はValidate_Conversion演算子でサポートされていません" となる。(Oracle 12c R2時点)

(※1) 期間型には フォーマット文字列、各国語サポートパラメータは使用できない。

フォーマット文字列

各国語サポートパラメータ例

nls_param の種類expr のタイプ設定例
NLS_DATE_LANGUAGE日時文字列式JAPANESE
NLS_CALENDAR'JAPANESE IMPERIAL'(要クォート)
NLS_NUMERIC_CHARACTERS数値、数値文字列式NLS 関連パラメータ 参照
NLS_CURRENCY
NLS_ISO_CURRENCY
NLS_DUAL_CURRENCY

VALIDATE_CONVERSION 関数の内容

文字列式 string をフォーマット文字列 formattype_name に変換可能か判定する。
フォーマット文字列 format が省略されると type_name のデータタイプによってセッションの NLS 関連パラメータ 書式が使用される。

(例 ) DATE 型の場合 NLS_DATE_FORMAT、 TIMESTAMP 型の場合 NLS_TIMESTAMP_FORMAT など。

VALIDATE_CONVERSION 使用例 (日付の場合)

日付変換に失敗する文字列の場合 NULL に置き換える。

SQL> select
      str, fmt, nls,
      VALIDATE_CONVERSION(str AS date, fmt, nls) valid,
      case when VALIDATE_CONVERSION(str AS DATE, fmt, nls) = 1 then
        to_date(str, fmt, nls) else null 
      end DATE_VAL /* (※1) */
    from validate_conv_sample;
 
STR		 FMT		      NLS			       VALID DATE_VAL
---------------- -------------------- -------------------------------- ----- --------
2018-01-02	 FXyyyy-mm-dd	      nls_calendar='GREGORIAN'		   1 18-01-02
2018-01-02	 FXyyyy/mm/dd	      nls_calendar='GREGORIAN'		   0 <NULL>
平成30年1月3日   eeyy"年"mm"月"dd"日" nls_calendar='Japanese Imperial'     1 18-01-03
H30.01.04	 eyy.mm.dd	      nls_calendar='Japanese Imperial'	   1 18-01-04
1月5日           monthdd"日"          nls_date_language=Japanese           1 18-01-05 (※2)

(※1) この例において case when ~ end 部を to_date(str DEFAULT null ON CONVERSION ERROR, fmt, nls) という書き換えはできない。
  書式指定 fmt 部分に "ORA-43918: この引数はリテラルである必要があります" というエラーが発生する。(Oracle 12c R2時点)
  デフォルト値の部分はバインド変数、または、リテラルに制限される。
(※2) システム日付が2018年のとき(年の部分はシステム日付が補完される)

VALIDATE_CONVERSION 使用例 (数値の場合)

SQL> select
      str, fmt,
      VALIDATE_CONVERSION(str AS number, fmt) valid,
      case when VALIDATE_CONVERSION(str AS NUMBER, fmt) = 1 then
        to_number(str, fmt) else null 
      end NUM_VAL
    from validate_conv_sample2;
 
STR		 FMT			   VALID    NUM_VAL
---------------- -------------------- ---------- ----------
1,234.98	 999,999.99		       1    1234.98
1234.98 	 0000.0 		       0 <NULL>
<1,234.98>	 999,999.99PR		       1   -1234.98
+1234.98	 S9999D99		       1    1234.98
1.23498E+03	 9.99999EEEE		       1    1234.98

to_date と同様に to_number(str DEFAULT null ON CONVERSION ERROR, fmt) は使用できない。

 


SQL 関数 - VALIDATE_CONVERSION 関連

関連事項

 
アルファベット別 関数一覧 ショートカット
ABCDEFG
HILMNOP
RSTUVWX
日本オラクル
■ 日本オラクル 株式会社
■ オラクルマスター資格 (オラクルマスターとは
■ オラクルサポートセンター