Oracle decode statement
Posted on 2004-09-28
I have a column in oracle database that has always a space and nothing stored in it. The column names are address1, address2 and address3, even if the address are blanks the user keyed in a space in the first character for all these 3 fields.
I am trying to write a decode that puts a comma only if the address2 and address3 is not null, here is my statement..
select DECODE(TRIM(address2),null,' ',address2) || ',' || DECODE(TRIM(address3),null,' ',address3)
There is always data for address1 so that field poses no problem
some cases have address2 (80%) and very little (20%) has address3
If I have data in address2 as my_addr2 and address3 as spaces in first character
I get result
but what I wanted is just
and if data exists for address3 then the result
How can I acheive this in decode (using PLSQL in this case) when especially the data has first character space in them (address2 and address3)