please reference:with n(str, ori, pos) as (values ('AA●BB●CC●DD●', 1, posstr('AA●BB●CC●DD●', '●'))union all select str, pos+2, locate('●', str, pos+2)from n where locate('●', str, pos+2)>0)select str, ori, pos, substr(str, ori, pos-ori) as...
显示全部please reference:
with n(str, ori, pos) as (
values ('AA●BB●CC●DD●', 1, posstr('AA●BB●CC●DD●', '●'))
union all
select str, pos+2, locate('●', str, pos+2)
from n
where locate('●', str, pos+2)>0)
select str, ori, pos, substr(str, ori, pos-ori) as result from n
result :
AA●BB●CC●DD●
| 1
| 3
| AA
|
AA●BB●CC●DD●
| 5
| 7
| BB
|
AA●BB●CC●DD●
| 9
| 11
| CC
|
AA●BB●CC●DD●
| 13
| 15
| DD
|
note: AA●BB●CC●DD●需要增加一个●结束符 db2v9 测试通过
[
本帖最后由 leo 于 2009-7-16 16:03 编辑 ]
收起