I have a CHAR column which is returning '0' vs. NULL or blank.
I have tried several flavors of CASE, but cannot override the '0' with ' ', etc.
The closest I have come is to do a LIKE ('0') then ' ' and while that seems to work, the results are returned in 2 columns -- the ORIGINAL column with the '0' and a CASE <expression> column with the blanks that I want -- I need single-column output so it can be fed to a compare program (before and after checking)...
what I'm doing is a SELECT columnA, CASE when columnA LIKE ('0') then ' ' end SAMPLE 1000;
Thanks so much.
i really don't understand what you actually want.
What data is in your column and what data should be reruned by the CASE?
'0' replaced by blanks?
CASE WHEN columA = '0' THEN '' ELSE columnA END?
Your query is that. You are having an Table with column as A and i am having Sample like this,
'0 ','ABC','DEF',etc. I should get the output as
NULL,ABC,DEF. Either the Zeros should be replaced to Null or Blanks.
SEL CASE WHEN TRIM(COLUMNA)='0' THEN NULL ELSE COLUMNA END AS COLUMNA;
I have used TRIM because you have specified i am having the column datatype as CHARACTER. Since the Column length will be the maximum length of the column when we specified in the Create Table.
CT TABLE A ( A CHAR(10));
insert into A('ABC');
SEL * FROM A;
The Output will be character ABC followed by 7 Blank Spaces.
Thanks & Regards,