Redshift Type Conversion Functions and Examples

  • Post author:
  • Post last modified:March 1, 2023
  • Post category:Redshift
  • Reading time:5 mins read

Redshift supports various type conversion functions. You can use Redshift type conversion functions to change data type of column values while using it in queries.

These functions use common calling functions i.e. the first argument is the value to be formatted, and the second argument is a template that defines the output or input format.

Below are some of commonly used Redshift type conversion functions and examples on usage.

Redshift CAST Function

You can do run-time conversions between compatible data types by using the CAST functions. This function is similar to CAST function of the other database. Below is the syntax:

CAST ( expression AS type );

Below is the other way to convert data type at run-time:

expression :: type

Redshift CAST Function Examples

Below are the examples on usage of CAST function:

training=# select cast('123456' as integer);
 int4
--------
 123456
(1 row)

training=# select '12456'::int;
 int4
-------
 12456
(1 row)

Related Articles,

Redshift CONVERT Function

You can convert values from one data types to other by using CONVERT function. You can do run-time conversions between compatible data types by using the CONVERT functions. This function is similar to CONVERT function. Below is the syntax and examples:

CONVERT ( type, expression );

Examples;

training=# select CONVERT ( int, '098761' );
 int4
-------
 98761
(1 row)
training=# select CONVERT ( int, translate('0987aA61','aA','') );
 translate
-----------
 98761
(1 row)

Redshift TO_CHAR Function

Converts the specified value (which can be a date, timestamp, real, double precision, or numeric value) to a character string. The TO_CHAR function is one of very useful Redshift type conversion function when you are working with miscellaneous data sets.

Below is the syntax and Examples:

TO_CHAR (timestamp_expression | numeric_expression , ‘format’);

Examples;

training=# select to_char(date '2017-06-02', 'YYYY Month');
 to_char
----------------
 2017 June
(1 row)

training=# select to_char(timestamp '2017-06-02 20:19:07', 'Day, DD HH:MI:SS am');
 to_char
---------------------------
 Friday , 02 08:19:07 pm
(1 row)

Redshift TO_NUMBER Function

Converts a character string containing numeric values to a numeric. Below is the syntax and example:

to_number(string, format);

Examples;

training=# Select to_number('12.345','99D999');
 to_number
-----------
 12.345
(1 row)

Related Article,

Redshift TO_DATE Function

TO_DATE converts a date represented in a character string to a DATE data type. Below is the syntax and example:

training=# select to_date ('01 Jan 2000', 'DD Mon YYYY');
 to_date
------------
 2000-01-01
(1 row)

Read:

Tags: