CODEWITHSUNDEEP
stringoracle-databasesortingplsqloracle10g
filippo
filippo

Reputation: 5793

Sort delimited separated values in a string with (Oracle) SQL

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.

Upvotes: 1

Views: 9137

Answers (2)

MarkM
MarkM

Reputation: 161

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

Upvotes: 4

RichardJQ
RichardJQ

Reputation: 173

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

Upvotes: 7

Related Questions