CODEWITHSUNDEEP
sqloracle-databaseconcatenation
Nick
Nick

Reputation: 1243

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

Upvotes: 1

Views: 18861

Answers (4)

JavaDragon
JavaDragon

Reputation: 451

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/

Upvotes: 1

Dave Costa
Dave Costa

Reputation: 48111

Just trim the string for trailing commas:

RTRIM( wm_concat(...), ',' )

Upvotes: 5

Robert Giesecke
Robert Giesecke

Reputation: 4314

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

Upvotes: 0

Tony Andrews
Tony Andrews

Reputation: 132570

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.

Upvotes: 7

Related Questions