Agfdhyk

SELECT TRIM(TRIM(FIRST_NAME || ' ' || MIDDLE_NAME) || ' ' || LAST_NAME)   

FROM TABLE_A; 

From the Oracle's documentation:

CONCAT_WS(separator,str1,str2,...)

CONCAT_WS() stands for Concatenate With Separator and is a special
form of CONCAT(). The first argument is the separator for the rest of
the arguments. The separator is added between the strings to be
concatenated. The separator can be a string, as can the rest of the
arguments. If the separator is NULL, the result is NULL.

And the very important comment:

CONCAT_WS() does not skip empty strings. However, it does skip any
NULL values after the separator argument.

So in your case it should be:

CONCAT_WS(',', FIRST_NAME, MIDDLE_NAME, LAST_NAME);

with indata as

(

select 'John' as first_name, 'W' as middle_name, 'Smith ' as last_name from dual

union

select null as first_name, null as middle_name, 'Adams' as last_name from dual

union

select 'Tom' as first_name, null as middle_name, 'Jefferson' as last_name from dual

)

select

regexp_replace(trim(indata.first_name || ' ' || indata.middle_name || ' ' || indata.last_name), 's{2,}', ' ')

from indata;

You could use RPAD() to add in the space character:

SELECT RPAD(first_name, LENGTH(first_name)+1, ' ')||RPAD(middle_name, LENGTH(middle_name)+1, ' ')||last_name

FROM TABLE_A;

When any of the parameters to RPAD are NULL, the result will be NULL, and in Oracle appending NULL to a string returns the original string.

This is how I typically concatenate several fields and remove whitespace in Oracle:

TRIM(REGEXP_REPLACE(HOUSE_NO || ' ' || PREFIX || ' ' || STREET_NAME || ' ' || STREET_TYPE || ' ' || SUFFIX, ' +', ' '))

1. Concatenate all the fields necessary with a space in between each. Empty strings and NULL values will result in a two or more spaces;


2. Use a regular expression to change any occurrences of multiple spaces [' +'] to a single space [' '];


3. Finally, trim any whitespace at the beginning and/or end from the resulting string.

Yet another option:

SELECT first_name

       || DECODE(middle_name

          ,      NULL, NULL

          ,      ' ' || middle_name)

       || DECODE(last_name

          ,      NULL, NULL

          ,      ' ' || last_name) full_name

FROM   table_a

;

Or you could simply use the REPLACE function:

with indata as 

  (select 'John' as first_name, 'W' as middle_name, 'Smith ' as last_name from dual 

   union 

   select null as first_name, null as middle_name, 'Adams' as last_name from dual 

    union 

    select 'Tom' as first_name, null as middle_name, 'Jefferson' as last_name from dual) 

SELECT REPLACE(TRIM(indata.first_name || ' ' || indata.middle_name || ' ' || indata.last_name), '  ', ' ')

  FROM indata

(And thanks to @tbone for the example data :-)

I have this work around with example. Hope this helps. Just go to SQL server, select new query and CP following query:

DECLARE @NULL_SAMLES TABLE

       (

       NS_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL

      ,COL_01 VARCHAR(10) NULL

      ,COL_02 VARCHAR(10) NULL

      ,COL_03 VARCHAR(10) NULL

      ,COL_04 VARCHAR(10) NULL

   )



   INSERT INTO @NULL_SAMLES(COL_01,COL_02,COL_03,COL_04)

   VALUES

    ('A','B','C','D')

   ,(' ' ,'B','C','D')

   ,(' ',NULL,'C','D')

   ,('A','B',NULL,'D')

   ,('A','B','C',NULL)

   ,(NULL,'B',NULL,'D')

   ,(NULL,'B','C',NULL)

   ,('A',NULL,'C',NULL)

   ,('A',NULL,NULL,'D')

   ,('A',NULL,NULL,NULL)

   ,(NULL,'B',NULL,NULL)

   ,(NULL,NULL,'C',NULL)

   ,(NULL,NULL,NULL,'D')





   SELECT

        NS.COL_01

       ,NS.COL_02

       ,NS.COL_03

       ,NS.COL_04,

      Stuff(  

        Coalesce(', ' + nullif(NS.COL_01, ''), '') 

      + Coalesce(', ' + nullif(NS.COL_02, ''), '') 

      + Coalesce(', ' + nullif(NS.COL_03, ''), '') 

      +Coalesce(', ' + nullif(NS.COL_04, ''), '')

      , 1, 1, '') AS CONC_COLS

   FROM @NULL_SAMLES NS

Don't underestimate the simple power of the CASE statement, which can be concatenated. Here's a self-contained example you can run as-is:

SELECT

CASE WHEN x.FIRST_NAME IS NULL THEN x.FIRST_NAME ELSE x.FIRST_NAME || ' ' END || 

CASE WHEN x.MIDDLE_NAME IS NULL THEN x.MIDDLE_NAME ELSE x.MIDDLE_NAME  || ' ' END || 

x.LAST_NAME

FROM (SELECT 'John' AS FIRST_NAME, NULL AS MIDDLE_NAME, 'Doe' AS LAST_NAME FROM DUAL) x;