Oracle：从字符串中获取部分字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1478542/
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
Oracle: get part string from a string
提问by David.Chu.ca
I have a string from oracle table like this:
我有一个来自 oracle 表的字符串，如下所示：
SELECT col1 FROM MyTable;
col1
part1;part2;part3
....
How can I get a substring from col1 such as part2?
如何从 col1 获取子字符串，例如 part2？
SELECT col1, INSTR(col1, ';', 1, ...) SubStrPart2 // string func?
col1 SubStrPart2
.... part2
Not sure what string functions I can use here so that I can easily update it to get a specific sub-string part out?
不确定我可以在这里使用哪些字符串函数以便我可以轻松更新它以获取特定的子字符串部分？
回答by Jeremy Bourque
If you want to split out on semi-colons, you could try something like this:
如果你想用分号分开，你可以尝试这样的事情：
select col1
, regexp_substr(col1, '^[^;]') as part1
, replace(regexp_substr(col1, ';[^;]+;'), ';') as part2
, replace(regexp_substr(col1, ';[^;]+$'), ';') as part3
The replace function removes any occurence of the second parameter if third parameter is not given.
如果没有给出第三个参数，替换函数会删除第二个参数的任何出现。
回答by Alex Martelli
If you want the "slice" of col1 between the first and second occurrence of ';', excluded, then the right approach shoule be something like...:
如果你想在第一次和第二次出现 ';' 之间的 col1 的“切片”，排除，那么正确的方法应该是这样的......：
select substr(col1,
instr(col1, ';', 1, 1)+1,
instr(col1, ';', 1, 2)-instr(col1, ';', 1, 1)-1)
it's a bit complicated because you need to specify the first character to take and then the number of characters to take (and there might be an off-by-one here, needs checking, but I do believe those +1 and -1 are correct).
这有点复杂，因为您需要指定要取的第一个字符，然后指定要取的字符数（这里可能有一个关闭，需要检查，但我相信那些 +1 和 -1 是正确的）。
回答by Sarah Vessels
This is a good referenceabout those functions.
SELECT col1, SUBSTR(col1, INSTR(col1, ';', 1, ...)) SubStrPart2 // string func?
col1 SubStrPart2
.... part2
You could also work with a CASEstatement:
您还可以使用CASE语句：
SELECT col1,
CASE WHEN INSTR(col1, ';', 1, ...) > 0
THEN SUBSTR(col1, INSTR(col1, ';', 1, ...))
ELSE 'Other value'
END AS SubStrPart2
...
See also these examples.
另请参阅这些示例。
