Say you have
select '9|2|6|7|5' as somedata from dual
Is there a way where I could do something like:
select
in_string_sort('|', a.somedata)
from
(select '9|2|6|7|5' as somedata from dual) a
So the result would be '2|5|6|7|9'?
I know, I could use a function to get that, but this is so basic I was wondering if Oracle would have some built-in function for this sort of thing.
[EDIT] forgot to mention: this would be in Oracle 10gR2.
So far as I know there is no such built-in function. You don't say which version, so I'll assume 11g. This query should do it for you:
1 select listagg(somedata, '|') within group (order by somedata) somedata from (
2 with q as (select '|'||'9|2|6|7|5' as somedata from dual)
3 select substr(somedata, instr(somedata, '|', 1, rownum) + 1, 1) somedata
4 from q,
5 (select 1 from q connect by level <= length(regexp_replace(somedata, '[0-9]', '')))
6* )
SOMEDATA
------------------------------
2|5|6|7|9
This is an older question but I ran across it searching for a solution to my particular problem. The answer from RichardJQ is good but it only worked for single char fields (length of 1) and numeric digits. In addition, I wanted to simplify the solution to use regexp functions available in newer versions of Oracle. The following snippet will work for fields with any char length and alphanumeric values. This solution works with 11g or newer.
select listagg(somedata, '|') within group (order by somedata) somedata from (
with q as (select '|'||'bbb|aaa|99|9|2|6|7|5' as somedata from dual)
select
regexp_substr(somedata, '\|([^|]+)',1,rownum,'i',1) somedata
from q,
(select 1 from q connect by level <= length(regexp_replace(somedata, '[^|]', '')))
)
SOMEDATA
------------------------------
2|5|6|7|9|99|aaa|bbb
