SQL> with t as( 2 select 'QTB12345' txt from dual union all 3 select 'QTB23456' from dual union all 4 select 'QTB34568' from dual ) 5 select regexp_substr(txt,'[0-9]+') from t 6 /
with t as( select 'cid' txt from dual union all select '12345' txt from dual union all select 'QTB23456' from dual union all select 'QTB34568' from dual ) select regexp_replace(txt,'[[a-zA-z]+([0-9]+)|([a-zA-Z]+)]','\1') from t -------------------------------------------------- cid 12345 23456 34568
SQL> with t as(
2 select 'QTB12345' txt from dual union all
3 select 'QTB23456' from dual union all
4 select 'QTB34568' from dual )
5 select regexp_substr(txt,'[0-9]+') from t
6 /
REGEXP_SUBSTR(TXT,'[0-9]+')
---------------------------
12345
23456
34568
cid
QTB12345789012345
QTB23456789012344
QTB34568901234567
我想查出
cid
12345
12344
34567
也就是说只取后5位呢?
select substr(cid,-5,5)from table
select substr(cid,-5)from t;
select 'cid' txt from dual union all
select '12345' txt from dual union all
select 'QTB23456' from dual union all
select 'QTB34568' from dual )
select regexp_replace(txt,'[[a-zA-z]+([0-9]+)|([a-zA-Z]+)]','\1') from t
--------------------------------------------------
cid
12345
23456
34568