字符串处理拆分字符串到列

create table #tab1 (aID INT,b VARCHAR(10))
INSERT INTO #tab1 values(1,'1,2')
insert into #tab1 values(2,'2,3,5')
insert into #tab1 values(3,'6')

SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b
select * from #
/*
先看substring的条件当aID为1的时候那么b就为1,2
此时在看substring(',1,2',1,1)=','条件成立,那么此时b.id=1
在看CHARINDEX(',','1,2,',1)结果为2减去b.id的1,在看substring(1,'1,2',1)那就是1
单d.id为2的时候看substring(',1,2',2,1)=1而不是','所以不满足,在看3满足条件
*/

SELECT A.aID, SUBSTRING(A.b, B.id, CHARINDEX(',', A.b + ',', B.id) - B.id)
FROM tab1 A, # B
WHERE SUBSTRING(',' + A.b, B.id, 1) = ','

--小梁的

if object_id('tb') is not null
   drop table tb
go

create table tb(id int,pid varchar(50))
go

insert into tb select 1,'3,4,5,6,7,' union all
select 2,'4,5,6,7,' union all
select 3,'1,3,5,7,'

go

;with liang as
(
    select distinct pid from
       (
        select id,
               pid= substring(pid,number,charindex(',',pid,number)-number)
        from tb,master..spt_values a
        where a.type = 'P'
              and substring(','+pid,number,1)=',' 
              and number <len(pid)
       )T
)
select id =(row_number() over(order by pid)),pid from liang

drop table tb

 

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/ws_hgo/archive/2009/04/13/4068286.aspx