CODEWITHSUNDEEP
sqloracle-databasesql-null
Geek
Geek

Reputation: 3329

sql decode function

I have a query that join 2 tables and displays the name as follows:

select a.first_name||','||a.last_name as name, a.number as id, b.address
from student a, address b
where a.number = b.id;

In couple cases the name wouldnt have a value in student table. Ouput looks as:

Name      Id    Address
abc, def  100   CA
xxx,yyy   101   MA
,         102   PA

So I tried using decode like,

select decode(a.first_name||','||a.last_name,',','') as name, a.number as id, b.address
from student a, address b
where a.number = b.id;

But this return null for name fields in all the rows. I have to show null values for names when it doesnt exists. I need to get rid of the comma.

Upvotes: 0

Views: 478

Answers (3)

Barbaros Özhan
Barbaros Özhan

Reputation: 65218

NVL2() function, which brings results for not null or null cases of the first argument, would suit well for your case in which a trailing comma is added whenever first_name is not null by through the second argument of the function(s.first_name||',') after whitespaces trimmed from that column's value

SELECT NVL2(TRIM(s.first_name),s.first_name||',','')||s.last_name AS name, 
       s."number" AS id, a.address
  FROM student s
  JOIN address a
    ON s."number" = a.id

If you suspect that there might have been some non-printable characters, then add a regular expression also such as

SELECT NVL2(TRIM(REGEXP_REPLACE(s.first_name,'[^[:print:]]')),s.first_name||',','')||s.last_name AS name, 
       s."number" AS id, a.address
  FROM student s
  JOIN address a
    ON s."number" = a.id

Demo

Upvotes: 1

Thomas Kirchhoff
Thomas Kirchhoff

Reputation: 988

Why not with TRIM:

    WITH demo_data AS ( SELECT 'abc' AS first_name, 'def' AS last_name, 100 AS id, 'CA' AS address FROM DUAL UNION ALL
                    SELECT 'xxx' AS first_name, 'yyy' AS last_name, 101 AS id, 'MA' AS address FROM DUAL UNION ALL
                    SELECT NULL  AS first_name, NULL  AS last_name, 102 AS id, 'PA' AS address FROM DUAL UNION ALL
                    SELECT 'ddd' AS first_name, NULL  AS last_name, 103 AS id, 'CA' AS address FROM DUAL UNION ALL
                    SELECT NULL  AS first_name, 'eee' AS last_name, 104 AS id, 'MA' AS address FROM DUAL )
SELECT TRIM(',' FROM first_name||','||last_name) as name,
       id,
       address
  FROM demo_data;

Result:

NAME            ID AD
------- ---------- --
abc,def        100 CA
xxx,yyy        101 MA
               102 PA
ddd            103 CA
eee            104 MA

Upvotes: 0

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

What about NULLIF function -

select NULLIF(a.first_name||','||a.last_name, ',') as name, a.number as id, b.address
from student a, address b
where a.number = b.id;

Upvotes: 0

Related Questions