insert into b select * from ( Select [userid]=substring(a.[userid],b.number,charindex(',',a.[userid]+',',b.number)-b.number) from Tb a join master..spt_values b ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.[userid]) where substring(','+a.[userid],b.number,1)=',')t
create function [dbo].[m_split2](@c varchar(2000),@split varchar(2)) returns @t table(col varchar(200)) as begin while(charindex(@split,@c)<>0) begin if(substring(@c,1,charindex(@split,@c)-1)!=' ') begin insert @t(col) values (substring(@c,1,charindex(@split,@c)-1)) end set @c = stuff(@c,1,charindex(@split,@c),'') -- SET @c = substring(@c,charindex(' ',@c)+1,len(@c)) end if(@c!=' ' and @c is not null and @c!='') begin insert @t(col) values (@c) end return enddeclare @a表 table (userid varchar(10)) insert into @a表 select '1,2,4' union all select '5,6'declare @sql varchar(20) select @sql=isnull(@sql+',',' ')+userid from @a表 select ltrim(col) from [dbo].[m_split2] (@sql,',') /* 1 2 4 5 6 */ --插入b表的话 insert into b select ltrim(col) from [dbo].[m_split2] (@sql,',')
create table #a表 (userid nvarchar(100)) insert #a表 select '1,2,4' union all select '5,6' create table #b表 (id nvarchar(10))declare @sql as nvarchar(max) set @sql='' select @sql=@sql+' union all select '''+replace(userid,',',''' union all select ''')+'''' from #a表 set @sql='insert #b表 '+stuff(@sql,1,10,'') exec(@sql) select * from #b表
select '1,2,4' union all select '5,6'我想要数据库里的字段读出来的数据,不是直接写上去吧
create table #a (userid nvarchar(100)) insert #a select '1,2,4' union all select '5,6' declare @a nvarchar(500) select @a=isnull(@a+',','')+userid from #a create table #b(id nvarchar(100)) while CHARINDEX(',',@a)>0 begin insert into #b values(LEFT(@a,CHARINDEX(',',@a)-1)) set @a=STUFF(@a,1,CHARINDEX(',',@a),'') end insert into #b values(@a) select * from #b
b
select
*
from
(
Select
[userid]=substring(a.[userid],b.number,charindex(',',a.[userid]+',',b.number)-b.number)
from
Tb a join master..spt_values b
ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.[userid])
where
substring(','+a.[userid],b.number,1)=',')t
create function [dbo].[m_split2](@c varchar(2000),@split varchar(2))
returns @t table(col varchar(200))
as
begin
while(charindex(@split,@c)<>0)
begin
if(substring(@c,1,charindex(@split,@c)-1)!=' ')
begin
insert @t(col) values (substring(@c,1,charindex(@split,@c)-1))
end
set @c = stuff(@c,1,charindex(@split,@c),'')
-- SET @c = substring(@c,charindex(' ',@c)+1,len(@c))
end
if(@c!=' ' and @c is not null and @c!='')
begin
insert @t(col) values (@c)
end
return
enddeclare @a表 table (userid varchar(10))
insert into @a表
select '1,2,4' union all
select '5,6'declare @sql varchar(20)
select @sql=isnull(@sql+',',' ')+userid from @a表
select ltrim(col) from [dbo].[m_split2] (@sql,',')
/*
1
2
4
5
6
*/
--插入b表的话
insert into b
select ltrim(col) from [dbo].[m_split2] (@sql,',')
create table #a表
(userid nvarchar(100))
insert #a表
select '1,2,4' union all
select '5,6' create table #b表
(id nvarchar(10))declare @sql as nvarchar(max)
set @sql=''
select @sql=@sql+' union all select '''+replace(userid,',',''' union all select ''')+'''' from #a表
set @sql='insert #b表 '+stuff(@sql,1,10,'')
exec(@sql)
select * from #b表
select '5,6'我想要数据库里的字段读出来的数据,不是直接写上去吧
(userid nvarchar(100))
insert #a
select '1,2,4' union all
select '5,6'
declare @a nvarchar(500)
select @a=isnull(@a+',','')+userid from #a
create table #b(id nvarchar(100))
while CHARINDEX(',',@a)>0
begin
insert into #b values(LEFT(@a,CHARINDEX(',',@a)-1))
set @a=STUFF(@a,1,CHARINDEX(',',@a),'')
end
insert into #b values(@a)
select * from #b