使用一对分隔符 * 和元素分隔符 | 交换 Oracle 记录内的字段

发布于 2024-10-04 05:04:23 字数 401 浏览 2 评论 0原文

在 Oracle 表中，我有 COUPLES (string,number) 的记录，如此分隔：

Abc|3456*Def|7890*Ghi|9430*Jkl|3534

在前面的示例中，对是：

(Abc,3456)
(Def,7890)
(Ghi,9430)
(Jkl,3534)

我想修改每条记录，交换每对的顺序（首先是数字，然后是字符串）：

3456|Abc*7890|Def*9430|Ghi*3534|Jkl

一对两个元素的分隔符是竖线 (|)。对之间的分隔符是星号 (*)。

我怎样才能实现交换每对夫妇的顺序的目标？

预先感谢您的友好合作！

原文

In an Oracle table, I have record with COUPLES (string,number) so separated:

Abc|3456*Def|7890*Ghi|9430*Jkl|3534

In the previous example, the couples are:

(Abc,3456)
(Def,7890)
(Ghi,9430)
(Jkl,3534)

I would like to modify each record swapping the order of every couple (first the number, then the string):

3456|Abc*7890|Def*9430|Ghi*3534|Jkl

The separator of the two elements of a couple is pipe (|).
The SEPARATOR BETWEEN COUPLES is asterisk (*).

How can I achieve my objective to swap the order of every couple?

Thank you in advance for your kind cooperation!

分享到QQ

分享到微博

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

发布评论

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

千里故人稀 2024-10-11 05:04:23

尝试使用正则表达式...现在您遇到了两个问题：

select 
  cola, 
  regexp_replace(cola, '([^*|]*)\|([^*|]*)(\*|$)','\2|\1\3') as swapped_col
from (
  select '3456|Abc*7890|Def*9430|Ghi*3534|Jkl' cola from dual
)

基本上，正则表达式表示搜索不是 | 的所有内容。或 * 直到找到 |，然后查找不是 | 的所有内容或 * 直到找到 * 或然后字符串结尾。然后交换这两个位并用您找到的作为最终分隔符的字符（* 或 EOL）终止它。交换的位由圆括号分组，然后在替换字符串中，数字表示哪个位置...因此，首先放置第二组括号的内容，然后是竖线，然后是第一组括号，然后是第三个。

默认情况下，REGEXP_REPLACE 将替换它找到的每个出现的模式并替换它

Try using regular expressions...now you've got two problems:

select 
  cola, 
  regexp_replace(cola, '([^*|]*)\|([^*|]*)(\*|$)','\2|\1\3') as swapped_col
from (
  select '3456|Abc*7890|Def*9430|Ghi*3534|Jkl' cola from dual
)

Basically the regex is saying search for everything that isn't a | or a * until you find |, then find everything that isn't a | or * until you find a * or then end of the string. Then swap the two bits and terminate it with the character you found as the final separator (either * or EOL). The bits that are swapped are grouped by the round brackets then in the replace string the numbers denote which is placed where... so the contents of the second set of brackets is put first, then a vertical bar, then the first set of brackets, then the third.

By default, REGEXP_REPLACE will replace every occurrence that it finds of the pattern and replace it

回复收藏 0 原文

~没有更多了~