如何得到字符串中最一个逗号和最后一个分号之间的子串内容。

[denghan]

如何得到字符串中最一个逗号和最后一个分号之间的子串内容？
字段名 ANSWER,类型 VARCHAR2(256) 例如：
ANSWER='答案=A,1,1;' => 1
ANSWER='答案=B,2,34;' => 34
ANSWER='答案=C,2,34,567;' => 567
ANSWER='答案=D,2,34,567,AGD;' => AGD

环境说明 Oracle9i + Windows Server 2003

求解SQL语句. 可用正则表达式。

百分感谢~！

[HelloWorld_001]

蹭分的

[denghan]

自己想了一个。但似乎效率不是很高。

select REVERSE(
SUBSTR(
REVERSE('答案=A,1,12,345;')
,instr(REVERSE('答案=A,1,12,345;'),';',1,1) + 1
,instr(REVERSE('答案=A,1,12,345;'),',',1,1) - instr(REVERSE('答案=A,1,12,345;'),';',1,1) -1
)
)
as result from dual;

[dawugui]

create table tb(ANSWER VARCHAR2(256))
insert into tb values('答案=A,1,1;')
insert into tb values('答案=B,2,34;')
insert into tb values('答案=C,2,34,567;')
insert into tb values('答案=D,2,34,567,AGD;')

select reverse(substr(reverse(answer) , instr(reverse(answer),';') + 1 , instr(reverse(answer),',') - instr(reverse(answer),';') - 1)) result 
from tb
where instr(answer,';') > 0 and instr(answer,',') > 0 and instr(answer,';') > instr(answer,',')

/*
RESULT                                                                          
--------------------------------------------------------------------------------
1                                                                               
34                                                                              
567                                                                             
AGD                                                                             

4 rows selected.
*/

drop table tb

[bai_jiong]

ORACLE 9I中不能使用ORACLE正则表达式.要在ORACLE10G或更高版本中才能用

可以试用一下这个SQL语句(ORACLE10G中调试):

select substr(regexp_substr(answer,',[0-9a-zA-Z]+;'),2,length(regexp_substr(answer,',[0-9a-zA-Z]+;')) - 2) FROM yourtable

[ruihuahan]

select substr('D,2,34,567,AGD;', 
              instr('D,2,34,567,AGD;', ',', '-1') + 1 , 
              length('D,2,34,567,AGD;') - instr('D,2,34,567,AGD;', ',', '-1') - 1
              )
from dual

[chiyt]

select substr(ANSWER,
instr(ANSWER,',',-1)+1,
instr(ANSWER,';',-1)-instr(ANSWER,',',-1)-1
) from your_table;

[denghan]

哦，对啊。instr(ANSWER,',',-1)可以从右边开始查找。
谢谢 chiyt糖糖

[dawugui]

早知道我就不反了.

[dawugui]

create table tb(ANSWER VARCHAR2(256))
insert into tb values('答案=A,1,1;')
insert into tb values('答案=B,2,34;')
insert into tb values('答案=C,2,34,567;')
insert into tb values('答案=D,2,34,567,AGD;')

select substr(answer , instr(answer , ',' , -1) + 1 , instr(answer , ';' , -1) - instr(answer , ',' , -1) - 1) result from tb

drop table tb

/*
RESULT                                                                          
--------------------------------------------------------------------------------
1                                                                               
34                                                                              
567                                                                             
AGD                                                                             

4 rows selected.
*/

[dawugui]

create table tb(ANSWER VARCHAR2(256))
insert into tb values('答案=A,1,1;')
insert into tb values('答案=B,2,34;')
insert into tb values('答案=C,2,34,567;')
insert into tb values('答案=D,2,34,567,AGD;')

select substr(answer , instr(answer , ',' , -1) + 1 , instr(answer , ';' , -1) - instr(answer , ',' , -1) - 1) result from tb

drop table tb

/*
RESULT                                                                          
--------------------------------------------------------------------------------
1                                                                               
34                                                                              
567                                                                             
AGD                                                                             

4 rows selected.
*/

[wangxuelid]

select reverse( substring(reverse(ANSWER),2 ,charindex(',',reverse(ANSWER))-2) ) as ANSWER from tb

[simadada]

看看