CASE Statement -- CASE <expression>

Database

CASE Statement -- CASE <expression>

Hello --

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; 

Any ideas?

Thanks so much.

Jackson

2 REPLIES
Junior Contributor

Re: CASE Statement -- CASE <expression>

Hi Jackson,

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?

DIeter

Enthusiast

Re: CASE Statement -- CASE <expression>

Hi Jackson,

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,

Adharssh.