Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

PL/SQL Simple Concat Question

Posted on 2011-03-15
14
Medium Priority
?
735 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
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 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 900 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 100 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
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.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

618 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