Tag: sql cast how to

Netezza / PureData – Casting Numbers to Character Data Type

Cast Conversion Format
Cast Conversion Format

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 ;

Related References

IBM, IBM Knowledge Center, PureData System for Analytics,Version 7.2.1

IBM Netezza stored procedures, NZPLSQL statements and grammar, Variables and constants, Data types and aliases

IBM, IBM Knowledge Center, PureData System for Analytics,Version 7.2.1

IBM Netezza database user documentation,SQL statement grammar,Explicit and implicit casting, Summary of Netezza casting

IBM, IBM Knowledge Center, PureData System for Analytics,Version 7.2.1

IBM Netezza database user documentation ,Netezza SQL basics,Netezza SQL extensions

 

Netezza / PureData – SQL Cast Conversion to Integers

  • Cast Conversion Format, netezza sql cast function, Netezza PureData SQL Cast Conversion to Integers
    Cast Conversion Format

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.

Basic Casting Conversion Format

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

Example Casting Integers SQL

Select

cast(127 as BYTEINT ) as  BYTEINT_8_Bit,

cast(127 as Int1) as  BYTEINT_ALIAS,

cast(32767 as SMALLINT ) as  SMALLINT_16_Bit,

cast(32767  as Int ) as SMALLINT_ALIAS,

cast(2147483647 as INTEGER ) as INTEGER_32_Bit,

cast(2147483647 as Int) as INTEGER_ALIAS_1,

cast(2147483647 as Int4 ) as INTEGER_ALIAS_2,

cast(9223372036854775807 as BIGINT ) as BIGINT_64_bit,

cast(9223372036854775807 as Int8 ) as BIGINT_ALIAS_1

From _v_dual;

Table Of Netezza Integer Types

Type

Alias Names

Bit Size

Value Range

BYTEINTINT18-bit –128 to 127
SMALLINTINT216-bit –32,768 to 32,767
INTEGERINT, INT432-bit –2,147,483,648 to 2,147,483,647
BIGINTINT864-bit-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

Related References

IBM, IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza stored procedures, NZPLSQL statements and grammar, Variables and constants, Data types and aliases

IBM, IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza database user documentation, SQL statement grammar, Explicit and implicit casting, Summary of Netezza casting