使用 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 Fiddle.

And here is this corresponding SQL Fiddle.

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