Geek Answers Handbook

Spaces when concatenating multiple columns and one column are null - Oracle

I need to combine several columns into one, spaces between each value. The problem is that when one value is null, I get double space between two values.

Example

SELECT (FIRST_NAME || ' ' || MIDDLE_NAME || ' ' || LAST_NAME FROM TABLE_A;

If the middle name is NULL, I get two spaces between the first and the name. Any way to get around this and have only one space when there is a null value?

+4
source share
8 answers

another option is to use decoding:

 SELECT decode(FIRST_NAME,'','',FIRST_NAME ||' ') || decode(MIDDLE_NAME,'','',MIDDLE_NAME ||' ') || LAST_NAME FROM TABLE_A;
-1
source 
 SELECT TRIM(TRIM(FIRST_NAME || ' ' || MIDDLE_NAME) || ' ' || LAST_NAME) FROM TABLE_A;
+7
source

From the Oracle documentation:

CONCAT_WS (delimiter, str1, str2, ...)

CONCAT_WS () means Concatenate With Separator and is a special form of CONCAT (). The first argument is a separator for the rest of the arguments. A separator is added between the lines that should be concatenated. The separator can be a string, like the rest of the arguments. If the delimiter is NULL, the result is NULL.

And a very important comment:

CONCAT_WS () does not skip empty lines. However, it skips any NULL values ​​after the delimiter argument.

So, in your case, it should be:

 CONCAT_WS(',', FIRST_NAME, MIDDLE_NAME, LAST_NAME);
+5
source 
 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;
+3
source

You can use RPAD() to add a space character:

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

If any of the RPAD parameters is NULL, the result will be NULL, and in Oracle adding NULL to the string, the original string is returned.

+1
source

This is how I usually combine multiple fields and remove spaces in Oracle:

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

  • Combine all the fields needed with the space between them. Empty lines and NULL values ​​result in two or more spaces;
  • Use a regular expression to change any occurrences of multiple spaces ['+'] into one space [''];
  • Finally, trim any spaces at the beginning and / or end of the resulting string.
+1
source

Another option:

 SELECT first_name || DECODE(middle_name , NULL, NULL , ' ' || middle_name) || DECODE(last_name , NULL, NULL , ' ' || last_name) full_name FROM table_a ;
0
source

Or you can just 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 example data :-)

0
source

More articles:


All Articles