Solved

PL/SQL Simple Concat Question

Posted on 2011-03-15
14
728 Views
Last Modified: 2012-06-27
Hello all,

I am looking for the cleanest syntax to use to do the following:

I am using a CASE statement for this but want a clean way to handle it:

4 fields:
FIELD1
FIELD2
FIELD3
FIELD4

I need to concat the 4 fields together based on this logic:
FIELD1 [space] FIELD2 [space] FIELD3 [space] FIELD4 [space]

- If any of the fields are NULL or EQUAL TO 'N/A' I want to skip that field in the concatenation.  So need to handle the null value
- If ALL 4 fields are NULL or EQUAL TO 'N/A' I want to skip the concat completely and use the value 'Not Available'
0
Comment
Question by:sbornstein2
[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
  • 6
  • 4
  • 3
  • +1
14 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 35140737
nvl
(decode(field1,'N/A',null,null,null,field1 || ' ') ||
decode(field2,'N/A',null,null,null,field2 || ' ') ||
decode(field3,'N/A',null,null,null,field3 || ' ') ||
decode(field4,'N/A',null,null,null,field4 || ' '),'Not Available')
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35140772
Are you sure you want to do this?

You want to end up with output like:
FIELD1 [space] FIELD2 [space] FIELD3 [space] FIELD4 [space]
FIELD1 [space] FIELD4 [space]


0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35140774
you could use case too

or a little more arcane but not necessarily better...

nvl2(nulliff(field1,'N/A'),field1 || ' ',null) ||
nvl2(nulliff(field2,'N/A'),field2 || ' ',null) ||
nvl2(nulliff(field3,'N/A'),field3 || ' ',null) ||
nvl2(nulliff(field4,'N/A'),field4 || ' ',null)
0
Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

 
LVL 74

Expert Comment

by:sdstuber
ID: 35140823
oops double ff and missed the last nvl

 NVL(
              NVL2(nullif(field1, 'N/A'), field1 || ' ', NULL)
           || NVL2(nullif(field2, 'N/A'), field2 || ' ', NULL)
           || NVL2(nullif(field3, 'N/A'), field3 || ' ', NULL)
           || NVL2(nullif(field4, 'N/A'), field4 || ' ', NULL),
           'Not Available')
0
 
LVL 11

Expert Comment

by:Akenathon
ID: 35140846
I cannot think of anything more concise than this, you really have to check for the 'N/A' string explicitly...

create table test (field1 varchar2(10), field2 varchar2(10), field3 varchar2(10), field4 varchar2(10));
 
insert into test values (null, null, null, null);
insert into test values ('one', null, null, null);
insert into test values (null, 'two', 'three','four');
insert into test values (null, 'two', 'three', null);

SELECT NVL(
 decode(field1,null,'','N/A','',field1||' ')||
 decode(field2,null,'','N/A','',field2||' ')||
 decode(field3,null,'','N/A','',field3||' ')||
 decode(field4,null,'','N/A','',field4||' ')
 ,'Not Available')
 FROM test

Open in new window

0
 
LVL 74

Accepted Solution

by:
sdstuber earned 225 total points
ID: 35140852
Full test case

SELECT field1,
       field2,
       field3,
       field4,
       NVL(
              DECODE(field1,  'N/A', NULL,  NULL, NULL,  field1 || ' ')
           || DECODE(field2,  'N/A', NULL,  NULL, NULL,  field2 || ' ')
           || DECODE(field3,  'N/A', NULL,  NULL, NULL,  field3 || ' ')
           || DECODE(field4,  'N/A', NULL,  NULL, NULL,  field4 || ' '),
           'Not Available')
           decodeversion,
       NVL(
              NVL2(NULLIF(field1, 'N/A'), field1 || ' ', NULL)
           || NVL2(NULLIF(field2, 'N/A'), field2 || ' ', NULL)
           || NVL2(NULLIF(field3, 'N/A'), field3 || ' ', NULL)
           || NVL2(NULLIF(field4, 'N/A'), field4 || ' ', NULL),
           'Not Available')
           nullifversion,
       NVL(
              CASE WHEN field1 IS NOT NULL AND field1 != 'N/A' THEN field1 || ' ' END
           || CASE WHEN field2 IS NOT NULL AND field2 != 'N/A' THEN field2 || ' ' END
           || CASE WHEN field3 IS NOT NULL AND field3 != 'N/A' THEN field3 || ' ' END
           || CASE WHEN field4 IS NOT NULL AND field4 != 'N/A' THEN field4 || ' ' END,
           'Not Available')
           caseversion
  FROM (SELECT a.n field1, b.n field2, c.n field3, d.n field4
          FROM (SELECT 'x' n FROM DUAL
                UNION ALL
                SELECT NULL FROM DUAL) a,
               (SELECT 'x' n FROM DUAL
                UNION ALL
                SELECT NULL FROM DUAL) b,
               (SELECT 'x' n FROM DUAL
                UNION ALL
                SELECT NULL FROM DUAL) c,
               (SELECT 'x' n FROM DUAL
                UNION ALL
                SELECT NULL FROM DUAL) d)
0
 
LVL 11

Assisted Solution

by:Akenathon
Akenathon earned 25 total points
ID: 35140876
Correction: field4 must not have a trailing space appended:

SELECT NVL(
 decode(field1,null,'','N/A','',field1||' ')||
 decode(field2,null,'','N/A','',field2||' ')||
 decode(field3,null,'','N/A','',field3||' ')||
 decode(field4,null,'','N/A','',field4)
 ,'Not Available')
 FROM test

Open in new window


But I see you've got many similar answers already :-)
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35140916
correction to correction - the 4th field does have a space appended

at least it does according to the question example
FIELD1 [space] FIELD2 [space] FIELD3 [space] FIELD4 [space]


yes, I've given several differnt methods, none are really superior to the other just preference
I like decode, that's why I suggested that way first, but it's not really better than the nulliff or case versions
0
 

Author Closing Comment

by:sbornstein2
ID: 35140936
thanks guys.  this was a huge help
0
 

Author Comment

by:sbornstein2
ID: 35141008
sorry one more follow-up sd what if one of the values is '' blank VS null?
0
 

Author Comment

by:sbornstein2
ID: 35141027
I should have mentioned if all 4 are NULL or BLANK
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35141030
if by blank you mean ''  a zero-length string,  that is the same as null

if by blank you mean it has nothing but spaces in it  then put TRIM() around each field wherever they are used
0
 

Author Comment

by:sbornstein2
ID: 35141037
okay thanks
0
 
LVL 11

Expert Comment

by:Akenathon
ID: 35141671
Good catch Sean :-)
0

Featured Post

Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

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…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
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.

691 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