使用 (Oracle) SQL 对字符串中的分隔值进行排序

发布于 2024-12-10 10:46:54 字数 391 浏览 0 评论 0原文

假设你有有

select '9|2|6|7|5' as somedata from dual

没有一种方法可以让我做这样的事情：

select
    in_string_sort('|', a.somedata)
from
    (select '9|2|6|7|5' as somedata from dual) a

所以结果是 '2|5|6|7|9'？

我知道，我可以使用一个函数来实现这一点，但这太基本了，我想知道 Oracle 是否会有一些内置函数来处理这类事情。

[编辑]忘记提及：这将在 Oracle 10gR2 中出现。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

蔚蓝源自深海 2024-12-17 10:46:54

据我所知没有这样的内置功能。你没有说哪个版本，所以我假设11g。这个查询应该为你做：

  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

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

回复收藏 0 原文

氛圍 2024-12-17 10:46:54

这是一个较旧的问题，但我遇到它来寻找我的特定问题的解决方案。 RichardJQ 的答案很好，但它只适用于单个字符字段（长度为 1）和数字。此外，我想简化解决方案以使用较新版本的 Oracle 中提供的正则表达式函数。以下代码片段适用于具有任何字符长度和字母数字值的字段。该解决方案适用于 11g 或更高版本。

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

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

回复收藏 0 原文

~没有更多了~