TRY_CAST Function Alternative in Redshift – Examples

Post author:Vithal S
Post last modified:March 13, 2023
Post category:Redshift
Reading time:5 mins read

There are many situations where in CAST conversion fails. For example, let us say you are trying to convert varchar to integer. The cast function will fail if the content is not valid integer values. Data bases such as Snowflake, Azure SQL data warehouse supports try_cast function to safely convert data types. In this article, we will check TRY_CAST function alternative in Redshift and how to use it to safely convert data types of the input values.

TRY_CAST Function Alternative in Reshift

Before going into details about try_cast alternative in Redshift. Let us first check what is try_cast function.

TRY_CAST Function

TRY_CAST is a special version of the CAST conversion function. It converts a value of one data type into another data type, but returns a NULL value instead of raising an error when the conversion can not be performed. It will safely convert the data type into another data type.

For example, consider following example in Snowflake.

select try_cast('1234f' as integer);
+------------------------------+
| TRY_CAST('1234F' AS INTEGER) |
|------------------------------|
|                         NULL |
+------------------------------+

As you can see, the result is NULL for invalid string.

Now, same example with CAST function will return an error in Redshift.

For example,

dev=# select cast('1234f' as integer);
ERROR:  invalid input syntax for integer: "1234f"

We have to identify the alternative approach in Redshift that mimic the try_cast function. i.e. output should be NULL if the conversion fails.

TRY_CAST Alternative in Reshift

Redshift supports SIMILAR TO with regular expressions. We can use these regular expressions as an TRY_CAST function alternative in Redshift.

For example, consider following SQL statement with regular expressions.

select case
when amount SIMILAR TO '[0-9]+(.[0-9][0-9])?' then amount::integer
else null
end as amount
from (select '1234f' as amount union select '1234' as amount) a
;

 amount
--------
 1234
 null
(2 rows)

As you can see, the result is NULL for invalid string.

Create TRY_CAST Function in Redshift

You can use Redshift SQL user defined function to create TRY_CAST function using above logic.

For example,

create or replace function try_cast (integer)
 returns integer
stable
as $$
 select case when $1 SIMILAR TO '[0-9]+(.[0-9][0-9])?' then cast($1 as integer) else null 
 end
$$ language sql;

Now, simply call try_cast function in your SQL query.

select try_cast(amount)
from (select '1234f' as amount union select '1234' as amount) a;

 try_cast
----------
     1234
     null
(2 rows)