使用Oracle SQL按定界符位置拆分字符串 [英] Split String by delimiter position using oracle SQL

问题描述

我有一个字符串，我想在特定位置用定界符分割该字符串.

I have a string and I would like to split that string by delimiter at a certain position.

例如，我的字符串是F/P/O，而我要查找的结果是:

For example, my String is F/P/O and the result I am looking for is:

因此，我想用最远的定界符来分隔字符串.
注意:我的一些字符串也是F/O，下面的SQL可以正常工作并返回所需的结果.

Therefore, I would like to separate the string by the furthest delimiter.
Note: some of my strings are F/O also for which my SQL below works fine and returns desired result.

我编写的SQL如下:

SELECT Substr('F/P/O', 1, Instr('F/P/O', '/') - 1) part1, 
       Substr('F/P/O', Instr('F/P/O', '/') + 1)    part2 
FROM   dual

结果是:

为什么会这样，我该如何解决?

Why is this happening and how can I fix it?

推荐答案

您要为此使用regexp_substr().这应该适用于您的示例:

You want to use regexp_substr() for this. This should work for your example:

select regexp_substr(val, '[^/]+/[^/]+', 1, 1) as part1,
       regexp_substr(val, '[^/]+$', 1, 1) as part2
from (select 'F/P/O' as val from dual) t

这里是SQL Fiddle.

Here, by the way, is the SQL Fiddle.

糟糕.我错过了问题的一部分，它说 last 定界符.为此，我们可以在第一部分中使用regex_replace():

Oops. I missed the part of the question where it says the last delimiter. For that, we can use regex_replace() for the first part:

select regexp_replace(val, '/[^/]+$', '', 1, 1) as part1,
       regexp_substr(val, '[^/]+$', 1, 1) as part2
from (select 'F/P/O' as val from dual) t

此处是此对应的SQL提琴.

And here is this corresponding SQL Fiddle.

这篇关于使用Oracle SQL按定界符位置拆分字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！