Oracle decode statement


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)
from address

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
my_addr2 ,
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)

Who is Participating?
earth man2Connect With a Mentor Commented:
select  a1 || case when a1 is not null and a2 is not null then ', ' end || a2 || case when (a1 is not null or a2 is not null) and a3 is not null then ', ' end || a3  as address
from (select trim(address1) as a1, trim(address2) as a2, trim(address3) as a3 from address);
Simply move the ||',' to be in concatenated with address3 if it is not null

select  DECODE(TRIM(address2),null,'  ',address2  ) || DECODE(TRIM(address3),null,'  ', (', '||address3))
from address

Oh and do the same for  address2

select  DECODE(TRIM(address2),null,'  ',   ',' ||address2  )
       || DECODE(TRIM(address2),null,'  ',   ',' ||address3  )
from address

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

mahjagAuthor Commented:
But the problem is the data in address2 is a space and does not eqaul to null so I want decode to put a space for concatenation if the address2 value first character is space or if address2 value is not null ( in this case it has actual data) then  I need comma..

Hope you got this..
Why dont you just rtrim the , like as under

SELECT RTRIM(DECODE(TRIM(address2),null,'  ',address2) || ',' || DECODE(TRIM(address3),null,'  ',address3),',')
from address
earth man2Commented:
select regexp_replace( regexp_replace( replace( trim(address1) || ', ' || trim(address2) || ', ' || trim(address3), ', , ', ', ' ), '^, ', '' ), ', $','' )  from address;
mahjagAuthor Commented:
This works only if the data in address3 is a valid value (like Suite50) but if address3 has first character space '  ' then I see the result


still I see comma at the end?
Try it with CASE:

select  address1||case when address2 is null OR TRIM(address2) is null then ''
                                  else ','||trim(address2)
                           end ||
                           case when address3 is null or TRIM(address3) is null then ''
                                   else ',' || trim(address3)
from address

earth man2Commented:
My solution works !
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.