sqldatabasenetezza

Convert CHARACTER DATATYPE TO INTEGER DATATYPE in Netezza SQL

I have a table TABLE_A Which has all the column as CHARACTER DATATYPE. Let's say in one of the column I have the value like :

Column_A
<Blank_Value>
123
1,123

I have to insert this value in another table TABLE_B which has this Column_A datatype as Integer. When I try to insert this COLUMN_A from TABLE_A to TABLE_B, obviously it will fail because of the DATATYPE mismatch. I am using this

INSERT INTO TABLE_B SELECT CAST(COLUMN_A AS INTEGER) FROM TABLE_A;

This is failing because COLUMN_A has the value '1,123'. I tried to first replace the ',' from the value then convert the datatype using below

INSERT INTO TABLE_B SELECT CAST(REPLACE(COLUMN_A,',','') AS INTEGER) FROM TABLE_A;

This is working for , values, But now the issue is coming with the blank value. How Should I use the query where I can replace the comma values, convert the character datatype to Integer & also can handle the blank values.

Solution

  • i guess you there then a 0.

    Netezza supports CASE WHEN

    So hat your query looks like

    INSERT INTO TABLE_B 
SELECT CAST(REPLACE(
CASE WHEN COLUMN_A = ''  THEN 0 ELSE COLUMN_A END ,',','') AS INTEGER) 
FROM TABLE_A;