使用 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 Fiddle.
And here is this corresponding SQL Fiddle.
相关文章