TRY_CAST - Hologres - Alibaba Cloud Documentation Center

The TRY_CAST function is used to convert data of the TEXT type into another data type. If the data cannot be converted into the desired data type, the TRY_CAST function does not return an error message but returns NULL.

Note

In addition to the TRY_CAST function, Hologres supports other data type conversion functions. For more information, see Date and time functions.Date and time functions

Limits

The TRY_CAST function can be used only in Hologres V2.2. If the version of your Hologres instance is V2.1 or earlier, contact the Hologres technical support to upgrade your Hologres instance.

Syntax

TRY_CAST( <source_string_expr> AS <target_data_type> )

The following table describes the parameters in the preceding syntax.

Parameter

Data type

Description

source_string_expr

TEXT

The column whose data type you want to convert.

target_data_type

INTEGER, SMALLINT, and BIGINT
REAL and DOUBLE PRECISION
BOOLEAN
DECIMAL
JSONB
DATE
TIMESTAMP
TIMESTAMPTZ

Note

Only Hologres V3.0 and later support the DATE, TIMESTAMP, and TIMESTAMPTZ data types.

The data type into which you want to convert the data.

Note

If a value in the specified column cannot be converted into the specified data type, NULL is returned.

Example

Create a table named try_cast_test and write test data to the table.

-- Create a table named try_cast_test. 
CREATE TABLE IF NOT EXISTS  try_cast_test (
 key int,
 value text
);

-- Write test data to the table. 
INSERT INTO try_cast_test VALUES (1, '1'), (2, 'abc');

Use the TRY_CAST function to convert data of the TEXT type into the INT type.

-- Convert data of the TEXT type in the value column into the INT type and display the converted data in the value_int column. 
SELECT
 key,
 value,
 try_cast (value AS INT) as value_int
FROM
 try_cast_test;

The following result is returned. The data abc in the value column cannot be converted into the INT type. The TRY_CAST function returns NULL.

 key | value | value_int 
-----+-------+-----------
    1|1      |         1
    2|abc    |\N 
(2 rows)