Convert CHARACTER DATATYPE TO INTEGER DATATYPE in Netezza SQL

Question

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.

Answer 1

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;

Answer 2

In this case use a casewhen block. I'm using TRANSLATE since I do not have SQLEXT installed

SYSTEM.ADMIN(ADMIN)=> create table TABLE_A (COLUMN_A char(50));
CREATE TABLE
SYSTEM.ADMIN(ADMIN)=> create table TABLE_B (COLUMN_A int);
CREATE TABLE
SYSTEM.ADMIN(ADMIN)=> insert into TABLE_A values('123');
INSERT 0 1
SYSTEM.ADMIN(ADMIN)=> insert into TABLE_A values('1,123');
INSERT 0 1
SYSTEM.ADMIN(ADMIN)=> insert into TABLE_A values('');
INSERT 0 1
SYSTEM.ADMIN(ADMIN)=> INSERT INTO TABLE_B SELECT CASE WHEN COLUMN_A='' THEN 0 ELSE TRANSLATE(COLUMN_A,',','')::INTEGER END FROM TABLE_A;
INSERT 0 3
SYSTEM.ADMIN(ADMIN)=> SELECT * FROM TABLE_B;
 COLUMN_A 
----------
      123
     1123
        0
(3 rows)