Oracle VALIDATE_CONVERSION() Function

Oracle VALIDATE_CONVERSION() is a built-in function that is used to determine whether a given expression can be converted to a given data type.

Oracle `VALIDATE_CONVERSION()` Syntax

Here is the syntax for the Oracle VALIDATE_CONVERSION() function:

VALIDATE_CONVERSION(expr AS type_name
  [, fmt [, 'nlsparam' ] ])

Parameters

expr: Required. It specifies an SQL expression.
type_name: Required. It specifies the data type to which expr should be converted.
fmt: Optional. A format string.
'nlsparam': Optional. You can use this parameter to set the parameter in the form 'NLS_DATE_LANGUAGE = language', where language is the language name.

For type_name, you can specify the following data types:

BINARY_DOUBLE

If BINARY_DOUBLE is specified, expr can be any character string that evaluates to a CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type, or a numeric value that is of type NUMBER, BINARY_FLOAT, or BINARY_DOUBLE. The optional fmt and nlsparam parameters have the same effect as the TO_BINARY_DOUBLE function. For more information, see TO_BINARY_DOUBLE.
BINARY_FLOAT

If BINARY_FLOAT is specified, expr can be any character string that evaluates to a CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type, or a numeric value that is of type NUMBER, BINARY_FLOAT, or BINARY_DOUBLE. The optional fmt and nlsparam parameters have the same effect as the TO_BINARY_FLOAT function. For more information, see TO_BINARY_FLOAT.
DATE

If DATE is specified, expr can be any character string that evaluates to a CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type. The optional fmt and nlsparam parameters have the same effect as the TO_DATE function. For more information, see TO_DATE.
INTERVAL DAY TO SECOND

If INTERVAL DAY TO SECOND is specified, expr can be any character string expression that evaluates to a CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type, and must contain a value in SQL interval format or ISO duration format. For this data type, the optional fmt and nlsparam parameters do not apply. For more information about SQL interval formats and ISO duration formats, see TO_DSINTERVAL.
INTERVAL YEAR TO MONTH

If INTERVAL YEAR TO MONTH is specified, expr can be any character string expression that evaluates to a CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type, and must contain a value in SQL interval format or ISO duration format. For this data type, the optional fmt and nlsparam parameters do not apply. For more information about SQL interval formats and ISO duration formats, see TO_YMINTERVAL.
NUMBER

If NUMBER is specified, expr can be any character string expression that evaluates to the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type, or a value of the NUMBER, BINARY_FLOAT, or BINARY_DOUBLE type. The optional fmt and nlsparam parameters have the same effect as the TO_NUMBER function. For more information, see TO_NUMBER.

If expr is a value of the NUMBER type, the VALIDATE_CONVERSION function validates whether expr is a valid number. If expr is not a valid number, the function returns 0. This enables you to identify corrupt numeric values in the database.
TIMESTAMP

If TIMESTAMP is specified, expr can be any character expression that evaluates to the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type. The optional fmt and nlsparam parameters have the same effect as the TO_TIMESTAMP function. If fmt is omitted, expr must use the default format of the TIMESTAMP data type, which is determined by the NLS_TIMESTAMP_FORMAT initialization parameter. For more information, see TO_TIMESTAMP.
TIMESTAMP WITH TIME ZONE

If TIMESTAMP WITH TIME ZONE is specified, expr can be any character expression that evaluates to the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type. The optional fmt and nlsparam parameters have the same effect as the TO_TIMESTAMP_TZ function. If fmt is omitted, expr must use the default format of the TIMESTAMP WITH TIME ZONE data type, which is determined by the NLS_TIMESTAMP_TZ_FORMAT initialization parameter. For more information, see TO_TIMESTAMP_TZ.
TIMESTAMP WITH LOCAL TIME ZONE

If TIMESTAMP WITH LOCAL TIME ZONE is specified, expr can be any character expression that evaluates to the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type. The optional fmt and nlsparam parameters have the same effect as the TO_TIMESTAMP function. If fmt is omitted, expr must use the default format of the TIMESTAMP data type, which is determined by the NLS_TIMESTAMP_FORMAT initialization parameter. For more information, see TO_TIMESTAMP.

Return Value

If expr can be successfully converted, the Oracle VALIDATE_CONVERSION() function returns 1; otherwise, it returns 0.

If expr evaluates to null, this function returns 1.

If an error occurs while evaluating expr, this function returns an error.

Oracle `VALIDATE_CONVERSION()` Examples

Here are some examples that demonstrate the usage of the Oracle VALIDATE_CONVERSION() function.

BINARY_DOUBLE

The following statement validates whether the given value can be converted to the BINARY_DOUBLE data type:

SELECT VALIDATE_CONVERSION(1000 AS BINARY_DOUBLE) Result
FROM DUAL;

输出：

   RESULT
_________
        1

BINARY_FLOAT

The following statement validates whether the given value can be converted to the BINARY_FLOAT data type:

SELECT VALIDATE_CONVERSION('1234.56' AS BINARY_FLOAT) Result
FROM DUAL;

输出：

   RESULT
_________
        1

DATE

The following statement validates whether the given value can be converted to the DATE data type:

SELECT VALIDATE_CONVERSION(
    'July 20, 1969, 20:18' AS DATE,
    'Month dd, YYYY, HH24:MI',
    'NLS_DATE_LANGUAGE = American'
  ) Result
FROM DUAL;

输出：

   RESULT
_________
        1

INTERVAL DAY TO SECOND

The following statement validates whether the given value can be converted to the INTERVAL DAY TO SECOND data type:

SELECT VALIDATE_CONVERSION('200 00:00:00' AS INTERVAL DAY TO SECOND) Result
FROM DUAL;

输出：

   RESULT
_________
        1

INTERVAL YEAR TO MONTH

The following statement validates whether the given value can be converted to the INTERVAL YEAR TO MONTH data type:

SELECT VALIDATE_CONVERSION('P1Y2M' AS INTERVAL YEAR TO MONTH) Result
FROM DUAL;

输出：

   RESULT
_________
        1

NUMBER

The following statement validates whether the given value can be converted to the NUMBER data type:

SELECT VALIDATE_CONVERSION(
    '$100,00' AS NUMBER,
    '$999D99',
    'NLS_NUMERIC_CHARACTERS = '',.'''
  ) Result
FROM DUAL;

输出：

   RESULT
_________
        1

TIMESTAMP

The following statement validates whether the given value can be converted to the TIMESTAMP data type:

SELECT VALIDATE_CONVERSION(
    '29-Jan-02 17:24:00' AS TIMESTAMP,
    'DD-MON-YY HH24:MI:SS',
    'NLS_DATE_LANGUAGE = American'
  ) Result
FROM DUAL;

输出：

   RESULT
_________
        1

TIMESTAMP WITH TIME ZONE

The following statement validates whether the given value can be converted to the TIMESTAMP WITH TIME ZONE data type:

SELECT VALIDATE_CONVERSION(
    '1999-12-01 11:00:00 -8:00' AS TIMESTAMP WITH TIME ZONE,
    'YYYY-MM-DD HH:MI:SS TZH:TZM'
  ) Result
FROM DUAL;

输出：

   RESULT
_________
        1

TIMESTAMP WITH LOCAL TIME ZONE

The following statement validates whether the given value can be converted to the TIMESTAMP WITH LOCAL TIME ZONE data type:

SELECT VALIDATE_CONVERSION(
    '11-May-16 17:30:00' AS TIMESTAMP WITH LOCAL TIME ZONE,
    'DD-MON-YY HH24:MI:SS'
  ) Result
FROM DUAL;

输出：

   RESULT
_________
        0

NULL Parameters

If the given expression is NULL, VALIDATE_CONVERSION() always returns 1, regardless of the given data type.

SELECT
    VALIDATE_CONVERSION(NULL AS NUMBER)
FROM dual;

输出：

   VALIDATE_CONVERSION(NULLASNUMBER)
____________________________________
                                   1

Here’s another example using a different data type:

SELECT
    VALIDATE_CONVERSION(NULL AS DATE)
FROM dual;

输出：

   VALIDATE_CONVERSION(NULLASDATE)
__________________________________
                                 1

In this example, we used the SET NULL 'NULL'; statement to display NULL values as the string 'NULL'.

Conclusion