Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Oracle decode statement

Posted on 2004-09-28
12
Medium Priority
?
1,528 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
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.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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…
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 explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

688 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