指定したデータ型に変換可能が判定する
VALIDATE_CONVERSION ( expr AS type_name [, format [, nls_param]] )
- 式 expr を指定書式にて type_name 型に変換可能な場合には 1 、不可の場合には 0 を戻す。
指定できるデータ型
- 数値型 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 をフォーマット文字列 format で type_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 関連
関連事項