Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Oracle decode statement

Posted on 2004-09-28
12
Medium Priority
?
1,534 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
  • 3
  • 2
  • 2
  • +2
9 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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
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 500 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses
Course of the Month15 days, 16 hours left to enroll

580 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