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:
- Commonly used Cloudera Impala String Functions and Examples
- Commonly used Cloudera Impala Date Functions and Examples
- Redshift Regular Expression functions and Examples
- Teradata Number Date Alternative in Redshift