CODEWITHSUNDEEP
sqloracle-database
user8467219
user8467219

Reputation:

Concatenate with string but exclude when null

Oracle v11

Exclude a string in a Concat when the value is NULL?

Work Query:

Select 
 *
 LastName || ',' || FirstName AS Last_First_Name

 --I've tried using NVL & NVL2
 --NVL(LastName || ',' || FirstName,'') AS Last_First_Name2
 FROM TableA

TableA

LastName   FirstName  ID
 Smith       Ann      1
                      2

Output I'm getting

LastName FirstName ID   LastName_FirstName
 Smith       Ann    1    Smith,Ann
                    2    ,

Expected Output:

LastName FirstName ID   LastName_FirstName
 Smith       Ann    1    Smith,Ann
                    2

Upvotes: 0

Views: 8880

Answers (6)

Mike Slocombe
Mike Slocombe

Reputation: 1

You can concatenate any number of fields with delimiters using:

SELECT REGEXP_REPLACE(REGEXP_REPLACE(Col1 || ',' || Col2 || ',' || Col3, ',,*', ','), '^,|,$', '')

The inner REGEXP_REPLACE removes repeated delimiters occurring when you have interior NULL values. The outer REGEXP_REPLACE removes the leading and trailing delimiters occurring if your list of values starts or ends with NULL. If all values are NULL, returns NULL.

Upvotes: 0

Maxim Mandrik
Maxim Mandrik

Reputation: 447

By analogy, you can do with anything, including names.

Consider the example address:

select 
    SUBSTR(
        (CASE WHEN city is null THEN '' ELSE  ', ' || city END) ||
        (CASE WHEN street is null THEN '' ELSE ', ' || street END) ||
        (CASE WHEN house is null THEN '' ELSE ', ' || house END) ||
        (CASE WHEN addition is null THEN '' ELSE ', ' || addition END)
    , 3) as address
from departments;

Upvotes: 0

user5683823
user5683823

Reputation:

Something like this... I added simulated inputs to test all four possibilities. However, note that if you may have last name but no first name, and also first name but no last name, the combined column will show just one name but you will not know if it is first or last. (Also, when a comma is added, I also add a space after the comma, as is usual; that can be controlled easily though.)

with
  tablea ( lastname, firstname, id ) as (
               select 'Smith', 'Ann'  , 1 from dual
     union all select null   , null   , 2 from dual
     union all select 'Ang'  , null   , 3 from dual
     union all select null   , 'Murat', 4 from dual
  )
-- End of simulated inputs (for testing only, not part of the solution).
-- SQL query begins BELOW THIS LINE. Use your actual table and column names.
select lastname, firstname, id,
       lastname 
         || case when lastname is not null and firstname is not null then ', ' end
         || firstname
       as last_first
from   tablea
;



LASTNAME   FIRSTNAME          ID LAST_FIRST 
---------- ---------- ---------- ------------
Smith      Ann                 1 Smith, Ann  
                               2             
Ang                            3 Ang         
           Murat               4 Murat

Upvotes: 1

Mr. Llama
Mr. Llama

Reputation: 20899

Oracle supports NULLIF. If both first and last name are null, the result will just be , which can be filtered.

SELECT
    NULLIF(LastName ||','|| FirstName, ',') AS FullName
FROM table

Alternately, if you need to allow for when a single name is populated, you can use TRIM to remove leading/trailing commas.

SELECT
    TRIM(',', LastName ||','|| FirstName) AS FullName
FROM table

Upvotes: 0

D Stanley
D Stanley

Reputation: 152566

One way is to use a CASE statement to conditionally add the comma:

CASE WHEN LastName IS NULL 
     THEN LastName || ','
     ELSE NULL
END
 || FirstName AS Last_First_Name

Upvotes: 0

Radim Bača
Radim Bača

Reputation: 10701

Use CASE

Select *,
       CASE WHEN LastName IS NOT NULL AND FirstName IS NOT NULL
            THEN LastName || ',' || FirstName AS Last_First_Name
       END lastname_firstname
FROM TableA

Upvotes: 2

Related Questions