Oracle® 非公式 SQL, PL/SQL & DBA's リファレンス
|
Top > SQL 関数一覧 （T‐Z） > VALIDATE_CONVERSION
指定したデータ型に変換可能が判定する
|
| expr | 文字列式、数値式 | |
| type_name | データ型の型名称 | |
| format | フォーマット文字列 | |
| nls_param | 各国語サポートパラメータ | NLS セッションパラメータ依存 |
指定できるデータ型
- 数値型 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) 期間型には フォーマット文字列、各国語サポートパラメータは使用できない。
フォーマット文字列
- type_name が日付に関する場合 日付式のフォーマットに使用する文字列
- type_name が数値に関する場合 数値式のフォーマットに使用する文字列
各国語サポートパラメータ例
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.98to_date と同様に to_number(str DEFAULT null ON CONVERSION ERROR, fmt) は使用できない。
