分割字符串declare @AllChar varchar(50)
declare @FirstChar varchar(50)
declare @FirstPoint int
declare @lenth int set @AllChar='afdsf,ASDFRE,WR,QWRQW,A,DSF,EW' ----可以传入一个字符串
set @lenth=len(@AllChar)
create table #Temp_String(FID int identity,Content varchar(50))
set @FirstPoint=charindex(',',@AllChar)while( @FirstPoint>0)
begin
set @FirstChar=substring(@AllChar,0,@FirstPoint)
--select @FirstChar
insert into #Temp_String(Content) values (@FirstChar)
set @AllChar=substring(@AllChar,@FirstPoint+1,@lenth)
set @FirstPoint=charindex(',',@AllChar)
end insert into #Temp_String(Content) values (@AllChar)
select * from #Temp_String
declare @FirstChar varchar(50)
declare @FirstPoint int
declare @lenth int set @AllChar='afdsf,ASDFRE,WR,QWRQW,A,DSF,EW' ----可以传入一个字符串
set @lenth=len(@AllChar)
create table #Temp_String(FID int identity,Content varchar(50))
set @FirstPoint=charindex(',',@AllChar)while( @FirstPoint>0)
begin
set @FirstChar=substring(@AllChar,0,@FirstPoint)
--select @FirstChar
insert into #Temp_String(Content) values (@FirstChar)
set @AllChar=substring(@AllChar,@FirstPoint+1,@lenth)
set @FirstPoint=charindex(',',@AllChar)
end insert into #Temp_String(Content) values (@AllChar)
select * from #Temp_String
insert @a select 1 ,'1001,1002,1003', '5001,5002,5003'
union all select 3 ,'1001', '5005,5004'SELECT TOP 100 id = IDENTITY(int, 1, 1)
INTO #a
FROM syscolumns a, syscolumns b
create table #tmp(id int identity(1,1),type varchar(200),brand varchar(100))
insert #tmp(type,brand)select aa.type type,bb.type brand from
(SELECT a.id,
type=SUBSTRING(a.type, b.id, CHARINDEX(',', a.type + ',', b.id) - b.id)
FROM @a a, #a b
WHERE SUBSTRING(',' + a.type, b.id, 1) = ',')AA
left Join
(
SELECT a.id,
type=SUBSTRING(a.brand, b.id, CHARINDEX(',', a.brand + ',', b.id) - b.id)
FROM @a a, #a b
WHERE SUBSTRING(',' + a.brand, b.id, 1) = ',')
BB
on aa.id=bb.id order by aa.id,typeselect * from #tmp
drop table #a
drop table #tmp