oracle 获取第二个和第三个逗号之间的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17795794/
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
Get value between 2nd and 3rd comma
提问by user2405778
I am trying to extract the state from an address where everything is in one column, heres an example:
我试图从所有内容都在一列中的地址中提取状态，这是一个示例：
2901 MAIN ST,CORNING,NY,14830
I have been trying to figure out how to do it with the substrand instrtogether, but I cant seem to get the hang of instr. Here is what I have so far:
我一直试图弄清楚如何与substr和instr一起做，但我似乎无法掌握instr. 这是我到目前为止所拥有的：
select substr('hello,hello,NY,11725-1234',1,instr('hello,hello,NY,11725-1234',',',2,3))
from dual;
I thought it would start at the second comma and end at the 3rd and get my everything in between, but that doesnt seem to be the case.
我以为它会从第二个逗号开始，到第三个逗号结束，然后将我的所有内容放在两者之间，但似乎并非如此。
Any help is appreciated.
任何帮助表示赞赏。
回答by Egor Skriptunoff
select
regexp_substr('2901 MAIN ST,CORNING,NY,14830', '(.*?,){2}(.*?),', 1, 1, '', 2)
from dual
In general,
一般来说，
n_th_component :=
regexp_substr(string, '(.*?,){'||(n-1)||'}([^,]*)', 1, 1, '', 2);
Example:
例子：
select
n,
regexp_substr('2901 MAIN ST,CORNING,NY,14830',
'(.*?,){'||(n-1)||'}([^,]*)', 1, 1, '', 2)
from (select level n from dual connect by level <= 4)
回答by Jeffrey Kemp
Regular expressions are a great way to do this sort of thing. SUBSTR and INSTR can also be used, however, by taking advantage of the 4th parameter of INSTR, nth_appearance:
正则表达式是做这种事情的好方法。SUBSTR 和 INSTR 也可以使用，但是，通过利用 INSTR 的第 4 个参数，nth_appearance：
select INSTR(mystring,',',1,1) AS first_comma
,INSTR(mystring,',',1,2) AS second_comma
,SUBSTR(mystring
,INSTR(mystring,',',1,1) + 1
,INSTR(mystring,',',1,2)
- INSTR(mystring,',',1,1)
- 1)
AS middle_bit
FROM
(select 'hello,world,NY,11725-1234' as mystring from dual);
FIRST_COMMA SECOND_COMMA MIDDLE_BIT
=========== ============ ==========
6 12 world
