使用(Oracle)SQL对字符串中的定界分隔值进行排序 [英] Sort delimited separated values in a string with (Oracle) SQL

问题描述

说你有

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

那么结果将是'2|5|6|7|9'?

我知道，我可以使用一个函数来实现这一点，但这太基本了，我想知道Oracle是否会为此类事情提供一些内置函数.

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.

忘记提及:这将在Oracle 10gR2中出现.

forgot to mention: this would be in Oracle 10gR2.

推荐答案

据我所知，尚无此类内置函数.您不说哪个版本，所以我假设11g.此查询应为您完成:

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

这篇关于使用(Oracle)SQL对字符串中的定界分隔值进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！