当前位置: 动力学知识库 > 问答 > 编程问答 >

Concatenate string in Oracle SQL? (wm-concat)

问题描述:

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:

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

Thanks

网友答案:

Yes the WM_CONCAT function puts a comma between each value it concatenates.

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:

SELECT LISTAGG ('<br>• ' || FIELD1 || ' ' || FIELD2 || ' : '
                || FIELD 3 || ' text')
         WITHIN GROUP (ORDER BY <something>) AS "Team"

That will produce a result without commas.

网友答案:

Just trim the string for trailing commas:

RTRIM( wm_concat(...), ',' )
网友答案:

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

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.

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.

Source: http://www.databaseskill.com/3400944/

网友答案:

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

SELECT StrAgg('<br>• ' || FIELD1 || ' ' || FIELD2 || ' : ' || FIELD 3 || ' text') AS "Team"
FROM   Abc
分享给朋友:
您可能感兴趣的文章:
随机阅读: