?
Solved

PL/SQL Simple Concat Question

Posted on 2011-03-15
14
Medium Priority
?
740 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
  • 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 78

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

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!

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

840 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