用SQL将字符串按分隔符拆为子串

项目中同一个型号包含多个尺码信息，这些信息采用分隔符拼接在一起存放在一个字段中，现在需要提交每个型号的各个尺码信息，也就是说原来一条记录存储的，现在需要变成多条记录。下面给出原始表和插入数据的脚本：

create table A(Lot_Key varchar(15),    F_Size varchar(100))
 
insert into A
select ‘CA101‘,    ‘33.50x1.50x2.50x3.35‘ union all
select ‘CA200‘,    ‘18.50x17.00x1.60‘ union all
select ‘CG300‘,    ‘21.15x1.65x2.50x1.30x1.65‘ union all
select ‘CQ255‘,    ‘31.50x1.75‘
go

                               图 1

期望的结果是每条记录的F_Size按照x进行分隔，转化成多条记录，最终得到如下结果：

                               图2

这里考虑采用SUBSTRING函数取子串的方式获取每个尺码，引入master..spt_values表，通过该表可以获取0到2047总共2048个连续的数字，这样可以定位每个分隔符的索引位置，实现两个分隔符之间数字的截取。下面是查询上图结果的SQL语句：

select Lot_Key,F_Size,
       MAX(case when rownum = 1 then v else null end) F_Size01,
       MAX(case when rownum = 2 then v else null end) F_Size02,
       MAX(case when rownum = 3 then v else null end) F_Size03,
       MAX(case when rownum = 4 then v else null end) F_Size04,
       MAX(case when rownum = 5 then v else null end) F_Size05          
from 
(
    select Lot_Key,
	 F_Size,
	 SUBSTRING(t.F_Size, number ,CHARINDEX(‘x‘,t.F_Size+‘x‘,number)-number) as v,
	 ROW_NUMBER() over(partition by lot_key order by getdate()) rownum
    from A t,master..spt_values s
    where s.number >=1
    and s.type = ‘P‘
    and SUBSTRING(‘x‘+t.F_Size,s.number,1) = ‘x‘
)t
group by Lot_Key,F_Size
order by Lot_Key

select * from  A t,master..spt_values s
where s.number >=1
and s.type = ‘P‘
and SUBSTRING(‘x‘+t.F_Size,s.number,1) = ‘x‘ 

                                                           图3

原来的CA101记录变成了4条记录，注意number列，1,7,12,17分别是每个分隔符的位置，当然是针对“x33.50x1.50x2.50x3.35”而言。图3中的记录数和最终的结果的记录数是一致的，这个时候就需要在图3中每个F_Size数据后面加一个x，例如图3中的第一条记录为例，就变成了“33.50x1.50x2.50x3.35x”，每次需要首先知道要取的子串的长度，采用下面的语句：

select CHARINDEX(‘x‘,t.F_Size+‘x‘,number)-number

最终再看看如何取出子串数字

总结

这种方式只能针对字符串的长度在一定限度范围内的，否则就会因master..spt_values表的连续自然数不足，导致后面的没办法分割。