从字符串中提取数字

with n(str, ori, pos) as (
values (‘1,3533,3528,3657,‘, 1, locate(‘,‘,‘1,3533,3528,3657‘))
union all
select str, pos+length(‘,‘), locate(‘,‘, str, pos+length(‘,‘))
from n
where locate(‘,‘, str, pos+length(‘,‘))>0) ,
tmp as (
select substr(str, ori, pos-ori) as trmnlId from n)
select INT(trmnlId) from tmp;

 locate(‘,‘,‘1,3533,3528,3657‘)返回‘,‘在‘1,3533,3528,3657‘中第一次出现的位置

从字符串中提取数字

原文：http://www.cnblogs.com/ren-shi-min/p/4671467.html