WITH TEMP AS
(
SELECT '[ABCD].TEST.DATA.ITEM[0]' STR FROM DUAL
UNION
SELECT '[DEV].TEST.DATA.ITEM[9]' STRFROM DUAL
UNION
SELECT '[BBB].TEST.DATA.ITEM[10]' STR FROM DUAL
UNION
SELECT '[CCC].TEST.DATA.ITEM[11]' STR FROM DUAL
UNION
SELECT '[DDDDEF].TEST.DATA.ITEM[100]' STR FROM DUAL
)
SELECT TRIM(TRANSLATE(REGEXP_SUBSTR(STR,'[[0-9]+]',1,1),'[]',' ')) FROM TEMP
注意,上面的写法只适合第一个[]中不包含数字的情况。
如果前面的[]中也包含数字,而且最后的[]中也包含字母的情况用下面的语句
WITH TEMP AS
(
SELECT '[ABCD].TEST.DATA.ITEM[abcd]' STR FROM DUAL
UNION
SELECT '[DEV].TEST.DATA.ITEM[9]' STRFROM DUAL
UNION
SELECT '[BBB].TEST.DATA.ITEM[10asdf]' STR FROM DUAL
UNION
SELECT '[CCC].TEST.DATA.ITEM[11]' STR FROM DUAL
UNION
SELECT '[DDDDEF].TEST.DATA.ITEM[zzz100]' STR FROM DUAL
)
SELECT REVERSE(TRIM(TRANSLATE(REGEXP_SUBSTR(STR,'[]][[:alnum:]]+[[]',1,1),'[]',' '))) STR FROM (SELECT REVERSE(STR) STR FROM TEMP)
STR
------
abcd
10asdf
11
zzz100
9
|