Solved

Oracle decode statement

Posted on 2004-09-28
12
1,524 Views
Last Modified: 2008-01-09
Hi

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
my_addr2
and if data exists for address3 then the result
my_addr2,my_addr3

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)

0
Comment
Question by:mahjag
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
12 Comments
 
LVL 7

Expert Comment

by:Bigfam5
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
0
 
LVL 7

Expert Comment

by:Bigfam5
ID: 12172076

Oh and do the same for  address2

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


0
 

Author Comment

by:mahjag
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..
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Expert Comment

by:sapnam
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
0
 
LVL 22

Expert Comment

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

Author Comment

by:mahjag
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

my_Address2,

still I see comma at the end?
0
 
LVL 12

Expert Comment

by:geotiger
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)
                           end
from address

0
 
LVL 22

Accepted Solution

by:
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);
0
 
LVL 22

Expert Comment

by:earth man2
ID: 12594901
My solution works !
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

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…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

623 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