CODEWITHSUNDEEP
sqloracle12c
Johnny Bones
Johnny Bones

Reputation: 8402

Conditionally concatenating fields in Oracle

What I need to do is to concatenate 4 fields in Oracle SQL Developer. The fields are:

Network, Network2, Network3, Network4

However, sometimes not all of the fields are filled in. This would always happen in sequence; it would never be just Network3 that's empty, it's either they fill in the first one only, the first 2 only, etc...

So, how can I write a Select statement that will ignore any fields that are NULL? I need the end result to look like:

Select Network, Network2, Network3, Network4 as Defect

and it should show Defect as something like "ON1, ON2, ON3, ON4" all in one field. But if only the first 2 are filled in, I don't want it to look like, "ON1, ON2, , , ".

Upvotes: 0

Views: 3433

Answers (2)

Lukas Eder
Lukas Eder

Reputation: 220877

Use NVL2(v, valueIfNotNull, valueIfNull)

SELECT
    Network
 || nvl2(Network2, ', ' || Network2, '')
 || nvl2(Network3, ', ' || Network3, '')
 || nvl2(Network4, ', ' || Network4, '') AS Defect
FROM my_table

Upvotes: 4

Serge
Serge

Reputation: 4036

Use the CONCATENATE || operator and COALESCE() function:

SELECT  Network
        || COALESCE(' - ' || Network2, '')
        || COALESCE(' - ' || Network3, '')
        || COALESCE(' - ' || Network4, '')
as Defect

Upvotes: -1

Related Questions