PL/SQL Simple Concat Question

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'
sbornstein2Asked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
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
 
sdstuberCommented:
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
 
slightwv (䄆 Netminder) Commented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
sdstuberCommented:
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
 
sdstuberCommented:
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
 
AkenathonCommented:
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
 
AkenathonConnect With a Mentor Commented:
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
 
sdstuberCommented:
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
 
sbornstein2Author Commented:
thanks guys.  this was a huge help
0
 
sbornstein2Author Commented:
sorry one more follow-up sd what if one of the values is '' blank VS null?
0
 
sbornstein2Author Commented:
I should have mentioned if all 4 are NULL or BLANK
0
 
sdstuberCommented:
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
 
sbornstein2Author Commented:
okay thanks
0
 
AkenathonCommented:
Good catch Sean :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.