Oracle decode statement

Posted on 2004-09-28
Last Modified: 2008-01-09

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)

Question by:mahjag
  • 3
  • 2
  • 2
  • +2

Expert Comment

ID: 12172061
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

Expert Comment

ID: 12172076

Oh and do the same for  address2

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


Author Comment

ID: 12172097
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..

Expert Comment

ID: 12172539
Why dont you just rtrim the , like as under

SELECT RTRIM(DECODE(TRIM(address2),null,'  ',address2) || ',' || DECODE(TRIM(address3),null,'  ',address3),',')
from address
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

LVL 22

Expert Comment

by:earth man2
ID: 12172585
select regexp_replace( regexp_replace( replace( trim(address1) || ', ' || trim(address2) || ', ' || trim(address3), ', , ', ', ' ), '^, ', '' ), ', $','' )  from address;

Author Comment

ID: 12172593
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?
LVL 12

Expert Comment

ID: 12173013
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

LVL 22

Accepted Solution

earth man2 earned 125 total points
ID: 12175883
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);
LVL 22

Expert Comment

by:earth man2
ID: 12594901
My solution works !

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
select query - oracle 16 91
oracle query help 18 99
Oracle 12c patching 1 59
Action link in Union Reports Not Working in OBIEE 11g 1 52
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now