在第二个和第三个逗号之间获取值 [英] Get value between 2nd and 3rd comma

问题描述

我正在尝试从一个列中所有内容的地址中提取状态，下面是一个示例:

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

我一直试图弄清楚如何将substr和instr一起使用，但是我似乎无法理解instr的本质.这是我到目前为止的内容:

I have been trying to figure out how to do it with the substr and instr together, but I cant seem to get the hang of instr. Here is what I have so far:

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.

感谢您的帮助.

推荐答案

select 
  regexp_substr('2901 MAIN ST,CORNING,NY,14830', '(.*?,){2}(.*?),', 1, 1, '', 2) 
from dual

通常

n_th_component := 
  regexp_substr(string, '(.*?,){'||(n-1)||'}([^,]*)', 1, 1, '', 2);

示例:

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)

这篇关于在第二个和第三个逗号之间获取值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！