1

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.

wweicker
  • 4,833
  • 5
  • 35
  • 60
filippo
  • 5,583
  • 13
  • 50
  • 72
  • 1
    This function is not "basic" because it's not needed. Storing delimited data in a single column is a bad idea and the wrong datamodel –  Oct 18 '11 at 14:19
  • @a_horse_with_no_name Well.. I think it's safe to say that sorting things sure is a basic need. This one might be too specific, but the I idea seems fair. Also this has nothing to do with storage issues - is rather about aggregated strings which is quite a common issue out there in the Googles... I'm looking for this cos in my particular case will a be a bit too much pain to aggregate it already ordered. – filippo Oct 18 '11 at 14:28
  • 1
    Aggregated strings like yours violate the basic principles of good database design: normalization. If you had chosen to store those numbers as rows, the solution is as simply as using `ORDER BY` –  Oct 18 '11 at 15:05
  • @a_horse_with_no_name Seriously... this is not the point of this question. – filippo Oct 18 '11 at 16:47

2 Answers2

7

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
RichardJQ
  • 173
  • 3
  • True story, forgot to mention the version. It's actually 10gR2, I don't have stragg. The string I'm trying to sort is actually already a aggregation, but from a bit too complex situation it would be a pain to aggregate it sorted already. Well I guess if there's no built in I'll end up having to dirt me hands. – filippo Oct 18 '11 at 14:34
  • 2
    Check the accepted answer to the linked question for instructions on how to do listagg in Oracle 10: http://stackoverflow.com/questions/468990/how-can-i-combine-multiple-rows-into-a-comma-delimited-list-in-oracle – Klas Lindbäck Oct 18 '11 at 14:47
  • Thanks, that is actually what I'm doing in one of the cases. The problem I had here was exactly comparing the result from a "collected aggregation" with a much more obscure xml based one which I did not understood very well (turns out to be very fast - and interesting, here: http://www.williamrobertson.net/documents/one_row.html). I ended up digesting it and got it correctly sorted. Thanks anyway! – filippo Oct 18 '11 at 17:00
4

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
MarkM
  • 141
  • 1
  • 4