在 Oracle SQL 中连接字符串？(wm-concat)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5513547/
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
Concatenate string in Oracle SQL? (wm-concat)
提问by Nick
I've got some SQL that I'd like to format correctly for a mailout (generated directly from SQL - don't ask!). The code is as follows:
我有一些 SQL，我想为邮件正确格式化（直接从 SQL 生成 - 不要问！）。代码如下：
SELECT wm_concat('<br>? ' || FIELD1 || ' ' || FIELD2 || ' : ' || FIELD 3 || ' text') AS "Team"
Okay, so this kinda works - but it places a comma at the end of each line. Silly question, and possibly quite trivial, but is there anyway at all to remove the comma please? I think it's being added by the wm_concat function
好的，所以这有点工作 - 但它在每行的末尾放置一个逗号。愚蠢的问题，可能很微不足道，但是无论如何都可以删除逗号吗？我认为它是由 wm_concat 函数添加的
Thanks
谢谢
回答by Tony Andrews
Yes the WM_CONCAT function puts a comma between each value it concatenates.
是的，WM_CONCAT 函数在它连接的每个值之间放置一个逗号。
If there are no commas in your data you could do this:
如果您的数据中没有逗号，您可以这样做：
SELECT replace (wm_concat('<br>? ' || FIELD1 || ' ' || FIELD2 || ' : '
|| FIELD 3 || ' text'),
',', null) AS "Team"
If you are on 11G you can use the new LISTAGG function instead:
如果您使用的是 11G，则可以改用新的 LISTAGG 函数：
SELECT LISTAGG ('<br>? ' || FIELD1 || ' ' || FIELD2 || ' : '
|| FIELD 3 || ' text')
WITHIN GROUP (ORDER BY <something>) AS "Team"
That will produce a result without commas.
这将产生一个没有逗号的结果。
回答by Dave Costa
Just trim the string for trailing commas:
只需修剪尾随逗号的字符串：
RTRIM( wm_concat(...), ',' )
回答by JavaDragon
Oracle 10g provides a very convenient function wm_concat used to solve line reclassified demand, very easy to use this function, but the function provides only ',' this kind of delimiter. In fact, as long as some simple conversion you can use other delimiters separated, the first thought is replace function
Oracle 10g提供了一个非常方便的函数wm_concat用于解决行重分类的需求，这个函数使用起来非常方便，但是该函数只提供了','这种分隔符。其实只要做一些简单的转换就可以用其他分隔符隔开，首先想到的是replace函数
with t as( select 'a' x from dual union select 'b' from dual )
select replace(wm_concat(x),',','-') from t;
with t as( select 'a' x from dual union select 'b' from dual )
select replace(wm_concat(x),',','-') from t;
But taking into account the string itself may contain ',' character, use the above SQL will lead to erroneous results, but also made some changes to the above SQL.
但是考虑到字符串本身可能包含','字符，使用上面的SQL会导致错误的结果，也对上面的SQL做了一些改动。
with t as( select 'a' x from dual union select 'b' y from dual)
select substr(replace(wm_concat('%'||x),',%','-'),2) from t;
with t as( select 'a' x from dual union select 'b' y from dual)
select substr(replace(wm_concat('%'||x),',%','-'),2) from t;
In the above SQL by a '%' as a separator, and then replace the '%' to remove the error. The program assumes that the string does not exist within the '%' string to replace the '%' in the SQL can also use other special characters.
在上面的SQL中以一个'%'作为分隔符，然后替换'%'来消除错误。程序假定字符串内不存在'%' 字符串来替换SQL 中的'%' 也可以使用其他特殊字符。
回答by Robert Giesecke
You can create your own aggregate functions in Oracle and use those to aggregate strings.
Or use the StrAgg function written by Tom Kyte: http://www.sqlsnippets.com/en/topic-11591.html
您可以在 Oracle 中创建自己的聚合函数并使用它们来聚合字符串。
或者使用 Tom Kyte 编写的 StrAgg 函数：http://www.sqlsnippets.com/en/topic-11591.html
SELECT StrAgg('<br>? ' || FIELD1 || ' ' || FIELD2 || ' : ' || FIELD 3 || ' text') AS "Team"
FROM Abc
