问题描述
我有一个字符串,我想在某个位置用定界符分割该字符串.
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.
这篇关于使用 oracle SQL 按分隔符位置拆分字符串的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!