使用 oracle SQL 按分隔符位置拆分字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26878291/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Split String by delimiter position using oracle SQL
提问by Avinesh Kumar
I have a string and I would like to split that string by delimiter at a certain position.
我有一个字符串，我想在某个位置通过分隔符分割该字符串。
For example, my String is F/P/Oand the result I am looking for is:
例如，我的 String 是F/P/O，我正在寻找的结果是：
Therefore, I would like to separate the string by the furthest delimiter.
Note: some of my strings are F/Oalso for which my SQL below works fine and returns desired result.
因此，我想用最远的分隔符分隔字符串。
注意：我的一些字符串F/O也适用于我下面的 SQL 工作正常并返回所需的结果。
The SQL I wrote is as follows:
我写的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
and the result is:
结果是：
Why is this happening and how can I fix it?
为什么会发生这种情况，我该如何解决？
采纳答案by Gordon Linoff
You want to use regexp_substr()for this. This should work for your example:
你想用regexp_substr()这个。这应该适用于您的示例：
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
Here, by the way, is the SQL Fiddle.
顺便说一下，这里是 SQL Fiddle。
Oops. I missed the part of the question where it says the lastdelimiter. For that, we can use regex_replace()for the first part:
哎呀。我错过了问题中最后一个分隔符的部分。为此，我们可以regex_replace()在第一部分使用：
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
And hereis this corresponding SQL Fiddle.
而这里是此对应的SQL小提琴。
回答by Lalit Kumar B
Therefore, I would like to separate the string by the furthest delimiter.
因此，我想用最远的分隔符分隔字符串。
I know this is an old question, but this is a simple requirement for which SUBSTRand INSTRwould suffice. REGEXPare still slowerand CPU intensiveoperations than the old subtsr and instr functions.
我知道这是一个老问题，但这是一个简单的要求，SUBSTR和INSTR就足够了。REGEXP仍然比旧的 subtsr 和 instr 函数更慢且CPU 密集型操作。
SQL> WITH DATA AS
2 ( SELECT 'F/P/O' str FROM dual
3 )
4 SELECT SUBSTR(str, 1, Instr(str, '/', -1, 1) -1) part1,
5 SUBSTR(str, Instr(str, '/', -1, 1) +1) part2
6 FROM DATA
7 /
PART1 PART2
----- -----
F/P O
As you said you want the furthestdelimiter, it would mean the first delimiter from the reverse.
正如您所说，您想要最远的分隔符，这意味着从reverse 开始的第一个分隔符。
You approach was fine, but you were missing the start_positionin INSTR. If the start_position is negative, the INSTRfunction counts back start_position number of characters from the end of string and then searches towards the beginning of string.
你的做法是好的，但你缺少START_POSITION在INSTR。如果 start_position 为负数，则该INSTR函数从字符串末尾开始计算 start_position 字符数，然后向字符串开头搜索。
