连接多列和一列为空时的空格 - Oracle
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11956844/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Spaces when concatenating multiple columns and one column is null - Oracle
提问by dstnrgrs
I need to concatenate several columns into one, with spaces between each value. The problem is when one value is null, I end up with a double space between two values.
我需要将几列连接成一列，每个值之间有空格。问题是当一个值为空时，我最终会在两个值之间出现双倍空格。
Example
例子
SELECT (FIRST_NAME || ' ' || MIDDLE_NAME || ' ' || LAST_NAME
FROM TABLE_A;
If the middle name happens to be NULL, then I end up with two spaces between the first and last name. Any way to get around this and only have one space when there's a null value?
如果中间名恰好是 NULL，那么我最终在名字和姓氏之间有两个空格。有什么办法可以解决这个问题，并且在有空值时只有一个空格？
采纳答案by Grisha Weintraub
another option is to use decode :
另一种选择是使用 decode ：
SELECT decode(FIRST_NAME,'','',FIRST_NAME ||' ') ||
decode(MIDDLE_NAME,'','',MIDDLE_NAME ||' ') || LAST_NAME
FROM TABLE_A;
回答by RedFilter
SELECT TRIM(TRIM(FIRST_NAME || ' ' || MIDDLE_NAME) || ' ' || LAST_NAME)
FROM TABLE_A;
回答by omnomnom
From the Oracle's documentation:
来自 Oracle 的文档：
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.
CONCAT_WS（分隔符，str1，str2，...）
CONCAT_WS() 代表 Concatenate With Separator，是 CONCAT() 的一种特殊形式。第一个参数是其余参数的分隔符。在要连接的字符串之间添加分隔符。分隔符可以是字符串，其余参数也可以。如果分隔符为 NULL，则结果为 NULL。
And the very important comment:
以及非常重要的评论：
CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument.
CONCAT_WS() 不会跳过空字符串。但是，它会跳过分隔符参数之后的任何 NULL 值。
So in your case it should be:
所以在你的情况下它应该是：
CONCAT_WS(',', FIRST_NAME, MIDDLE_NAME, LAST_NAME);
回答by tbone
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;
回答by Erik Anderson
This is how I typically concatenate several fields and remove whitespace in Oracle:
这就是我通常在 Oracle 中连接多个字段并删除空格的方式：
TRIM(REGEXP_REPLACE(HOUSE_NO || ' ' || PREFIX || ' ' || STREET_NAME || ' ' || STREET_TYPE || ' ' || SUFFIX, ' +', ' '))
TRIM(REGEXP_REPLACE(HOUSE_NO || ' ' || PREFIX || ' ' || STREET_NAME || ' ' || STREET_TYPE || ' ' || SUFFIX, ' +', ' '))
- Concatenate all the fields necessary with a space in between each. Empty strings and NULL values will result in a two or more spaces;
- Use a regular expression to change any occurrences of multiple spaces [' +'] to a single space [' '];
- Finally, trim any whitespace at the beginning and/or end from the resulting string.
- 连接所有必要的字段，每个字段之间有一个空格。空字符串和 NULL 值将导致两个或多个空格；
- 使用正则表达式将任何出现的多个空格 [' +'] 更改为单个空格 [' ']；
- 最后，修剪结果字符串开头和/或结尾的任何空格。
回答by ninesided
You could use RPAD()to add in the space character:
您可以使用RPAD()添加空格字符：
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 RPADare NULL, the result will be NULL, and in Oracle appending NULL to a string returns the original string.
当 to 的任何参数为RPADNULL 时，结果将为 NULL，并且在 Oracle 中将 NULL 附加到字符串返回原始字符串。
回答by Tebbe
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
;
回答by Chetan Birajdar
I have this work around with example. Hope this helps. Just go to SQL server, select new query and CP following query:
我有这个例子。希望这可以帮助。只需转到 SQL 服务器，选择新查询和 CP 以下查询：
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
回答by Gustavo Lopez
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:
不要低估 CASE 语句的简单功能，它可以连接起来。这是一个独立的示例，您可以按原样运行：
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;
回答by Bob Jarvis - Reinstate Monica
Or you could simply use the REPLACE function:
或者您可以简单地使用 REPLACE 函数：
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 :-)
（感谢@tbone 提供示例数据:-)
