Netezza datatype conversion function

Netezza datatype conversion function

To convert decimal to integer use cast(value as int) in netezza:

select cast(12345.30 as int)

In the above example in place of 12345.30 you can use any decimal or numeric column.

To convert integer to decimal use cast(value as numeric(precision,scale)) or cast(value as decimal(precision,scale)) in netezza:

select cast(1234 as numeric(7,2))

In the above example in place of 12345 you can use any integer column.

To convert string to number use to_number function in netezza:

 select to_number('12345','99999')

In the above example in place of '12345' you can use any column that is of data type string but has numbers in it.

To convert date to specific character format use to_char function in netezza:

select to_char(current_date,'YYYY-MM-DD')

To check for nulls in netezza use nvl function:

select nvl(col_name, 1) from table_name

In the example above it checks if the column is a null and if it is a null it returns 1. To avoid division by 0 you can use this nvl function or case when function.

Sample case when statement in Netezza:

select case when col1=0 then 1 else 0 end from table_name

To remove blanks spaces at the beginning or end of a string use ltrim or rtrim respectively in netezza: 

select ltrim(rtrim(col_name)) from table_name

To convert a string to upper or lower case use UPPER or LOWER functions:

select upper('abc'), lower('ABC') from dual

In the above example in place of 'abc' or 'ABC' you can use the string column that you need to convert