asked on

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'

Sean Stuber

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')

slightwv (䄆 Netminder)

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]

Sean Stuber

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)

Sean Stuber

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')

Akenathon

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

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Akenathon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sean Stuber

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

sbornstein2

ASKER

thanks guys. this was a huge help

sbornstein2

ASKER

sorry one more follow-up sd what if one of the values is '' blank VS null?

sbornstein2

ASKER

I should have mentioned if all 4 are NULL or BLANK

Sean Stuber

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

sbornstein2

ASKER

okay thanks

Akenathon

Good catch Sean :-)