Category Archives: Cast

I noticed that someone has been searching for this example on my site, so, here is a quick example of how to cast number data to a character data type. I ran this SQL example in netezza and it worked fine.

Basic Casting Conversion Format

cast(<<FieldName>> as <<IntegerType_or_Alias>>) as <<FieldName>>

Example Casting Number to Character Data Type SQL

SELECT
—-Casting Integer to Character Data Type —————

SUBMITDATE_SRKY as SUBMITDATE_SRKY_INTEGER
, cast(SUBMITDATE_SRKY as char(10)) as Integer_to_CHAR
, cast(SUBMITDATE_SRKY as Varchar(10)) as Integer_to_VARCHAR
, cast(SUBMITDATE_SRKY as Varchar(10)) as Integer_to_VARCHAR
, cast(SUBMITDATE_SRKY as Nchar(10)) as Integer_to_NCHAR
, cast(SUBMITDATE_SRKY as NVarchar(10)) as Integer_to_NVARCHAR

—-Casting Double Precision Number to Character Data Type —————

, HOST_CPU_SECS as DOUBLE_PRECISION_NUMBER
, cast(HOST_CPU_SECS as char(30)) as DOUBLE_PRECISION_to_CHAR
, cast(HOST_CPU_SECS as Varchar(30)) as DOUBLE_PRECISION_to_VARCHAR
, cast(HOST_CPU_SECS as Varchar(30)) as DOUBLE_PRECISION_to_VARCHAR
, cast(HOST_CPU_SECS as Nchar(30)) as DOUBLE_PRECISION_to_NCHAR
, cast(HOST_CPU_SECS as NVarchar(30)) as DOUBLE_PRECISION_to_NVARCHAR

—-Casting Numeric to Character Data Type —————

, TOTALRUNTIME as NUMERIC_FIELD
, cast(TOTALRUNTIME as char(30)) as NUMERIC_FIELD_to_CHAR
, cast(TOTALRUNTIME as Varchar(30)) as NUMERIC_FIELD_to_VARCHAR
, cast(TOTALRUNTIME as Varchar(30)) as NUMERIC_FIELD_to_VARCHAR
, cast(TOTALRUNTIME as Nchar(30)) as NUMERIC_FIELD_to_NCHAR
, cast(TOTALRUNTIME as NVarchar(30)) as NUMERIC_FIELD_to_NVARCHAR
FROM NETEZZA_QUERY_FACT ;

From time to time, I have had to look up this information, especially, when working with old school SQL intensive ETL patterns where the transformations are being performed in SQL rather within the ETL application. So, rather than scrambling to find the differing integer casting range limits, bit type conversions, and alias for different integer types, and pieces it together, I thought I would consolidate it here for future reference.

There are some important rules in the summary of Netezza casting reference below, but here are a few, which I have found useful to remember:

It is safer to convert from a small to a larger integer type, otherwise the input values may not fit

You may need to apply other data treatments to handle special characters, and decimals to prevent casting errors.