字符串拆分到列

if object_id('[TB]') is not null drop table [TB] 
 go 
 create table [TB]([COL1] varchar(3),[COL2] varchar(18))  
  insert [TB]  
  select 'ABC','123,4568,1234565' union all  
  select 'DBD','1231,56455,5645665'  
  GO  
  
 select B.[COL1],substring(B.[COL2],p.number,charindex(',',B.[COL2]+',',p.number)-p.number) [COL2] 
 from TB B 
 join master..spt_values p 
 on p.type='P' 
 and charindex(',',','+B.[COL2],number)=number  
  
COL1 COL2
---- ------------------
ABC  123
ABC  4568
ABC  1234565
DBD  1231
DBD  56455
DBD  5645665
(6 行受影响)