Tuesday, 28 May 2013

Change separator of WM_CONCAT function of Oracle

Change separator of WM_CONCAT function of Oracle

Normally, WM_CONCAT is an aggregate function that return values from table separated by comma like here.
Suppose I have a table foo like this:
col_id     | col_text


111        | This

111        | is

111        | a

111        | test.
If I use this query:
SELECT CAST(WM_CONCAT(col_text) AS VARCHAR2(100)), col_id FROM foo
the result would be
This, is, a, test.
Is it possible to change the separator(',') to other characters like '.' or '|' of the WM_CONCAT() function?
Or create a user defined function that can be executed like WM_CONCAT()?

No comments:

Post a Comment