Solved

PL/SQL Simple Concat Question

Posted on 2011-03-15
14
710 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 73

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 76

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 73

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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 73

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 73

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 73

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 73

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

777 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