用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

查询A表中的数据得到如下结果:

                               图 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

上面的SQL语句理解起来非常困难,那么我们抽丝剥茧看它是如何进行字符串分隔的。首先看下面的SQL语句:

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

以图3中的第一条记录为例对上面的语句进行转化

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

总结

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

阅读更多
个人分类: 数据库
所属专栏: MS Sql Server技术分享
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭