CASE Using CASE Statements to check for Null or Empty value

Question

I need to write a DB function to concatenate 4 values. These values can be empty or null. How can I concatenate only the values that are not empty or null?

Pseudo Code

CASE
WHEN #p1 IS NULL THEN
  ''
ELSE
  #p1
END ||
CASE
WHEN #p2 IS NULL THEN
  ''
ELSE
  #p2
END ||
CASE
WHEN #p3 IS NULL THEN
  ''
ELSE
  #p3
END ||
CASE
WHEN #p4 IS NULL THEN
  ''
ELSE
  #p4
END

## End

No need to make it so complex, simply concatenate the values. NULL would be ignored anyway. — Lalit Kumar B, May 19 '15 at 07:21
Oracle has function called `GROUP_CONCAT` look into that it should be exactly what you need http://explainextended.com/2009/04/05/group_concat-in-oracle-10g/ — , May 19 '15 at 17:59

score 1 · Answer 1 · answered May 19 '15 at 07:04

1

ANSI SQL:

coalesce(#p1,'') || coalesce(#p2,'') || coalesce(#p3,'') || coalesce(#p4,'')

answered May 19 '15 at 07:04

jarlh

42,561
8
45
63

What is the purpose of all the `coalesce` when you could simply concatenate? `''` is an empty string, which is `NULL` in Oracle. So, coalesce null with null makes no sense. – Lalit Kumar B May 19 '15 at 07:18
In ANSI SQL, null || 'str' gives null. I didn't know Oracle treated ' ' and null as equal. (I'm not an Oracle guy, that's why I specified ANSI SQL in my answer.) More portable anyway! – jarlh May 19 '15 at 07:22
Ok, perhaps this might be useful to you http://stackoverflow.com/questions/203493/why-does-oracle-9i-treat-an-empty-string-as-null – Lalit Kumar B May 19 '15 at 07:23

score 0 · Accepted Answer · answered May 19 '15 at 07:17

You are replacing null with empty strings? In Oracle null and empty string are the same thing. You don't need to make any decision based on null.

So, you can just:

expr:= p1||p2||p3||p4;

For example if

p1 = 'a';
p2 is null;
p3 = 'b';
p4 is null;

expr p1||p2||p3||p4 would be 'ab'.

score 0 · Answer 3 · answered May 19 '15 at 07:18

How can i check for null or empty values for all the four parameters and then concatenate based on what is not null.

If all you need is to concatenate the NOT NULL values, then simply concatenate them. Since NULL values would anyway be ignored.

#p1||#p2||#p3||#p4

For example,

SQL> WITH t(A, b, c, d) AS(
  2  SELECT 'x', NULL, 'y', NULL FROM dual UNION ALL
  3  SELECT 'x', '', NULL, 'z' FROM dual UNION ALL
  4  SELECT NULL, NULL, 'x', NULL FROM dual
  5  )
  6  SELECT a||b||c||d stra FROM t;

STR
---
xy
xz
x

SQL>

CASE Using CASE Statements to check for Null or Empty value

Pseudo Code

3 Answers3