需求:对一个位数固定使用分隔符连接的字符串的拆分,如将记录215|482|850|870拆分为215,482,850,870四条记录
结果:WITH RECURSIVE 语句能够实现此功能,测试如下:
CREATE TABLE dwsdata.gift_info (
gf_id varchar(50),
channels varchar(100)
);
select * from dwsdata.gift_info;
gf_id channels
10710701010201002 215|482|850|870
WITH RECURSIVE temp_table (gf_id,channels) AS
(
select
gf_id
,trim(channels)
from dwsdata.gift_info
where channels<>''
union all
select
gf_id
,substr(channels,5 )
from temp_table aa
where char(aa.channels ) <>3
)
select
TRIM(gf_id)
,SUBSTR(channels,1,3)
from temp_table
;
结果:
gf_id channels
10710701010201002 215
10710701010201002 482
10710701010201002 850
10710701010201002 870
摘自:http://blog.csdn.net/fanxiaoliang83/archive/2009/07/30/4395288.aspx