有一存储过程参数值为'1|2|10|4',现在需要将其进行拆分为
1
2
10
4
并将其插入到一张表tmp中
结构是:oid,value,请问如何操作?
create table tmp
(
oid int identify(1,1),
value int
)
1
2
10
4
并将其插入到一张表tmp中
结构是:oid,value,请问如何操作?
create table tmp
(
oid int identify(1,1),
value int
)
a.oid,value=substring(a.value,b.number,charindex('|',a.value+'|',b.number)-b.number)
from
Tmp a join master..spt_values b
ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.value)
where
substring('|'+a.value,b.number,1)='|'
Create function [dbo].[f_split](@SourceSql varchar(8000))
returns @temp table(a varchar(100))
as
begin
declare @i int
set @SourceSql=rtrim(ltrim(@SourceSql))
set @i=charindex('|',@SourceSql)
while @i>=1
begin
insert @temp values(left(@SourceSql,@i-1))
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex('|',@SourceSql)
end
if @SourceSql<>'|'
insert @temp values(@SourceSql)
return
end--表值函数
select a from f_split_hengxian('1|2|10|4')
--1
--2
--10
--4--插入到你的表中
insert into tmp
select a from f_split_hengxian('1|2|10|4')
(
oid int IDENTITY(1,1),
value int
)
declare @i int
declare @sql varchar(50)
set @sql='1|2|10|4'
set @sql=rtrim(ltrim(@sql))
set @i=charindex('|',@sql)
while @i>=1
begin
insert tmp values(left(@sql,@i-1))
set @sql=substring(@sql,@i+1,len(@sql)-@i)
set @i=charindex('|',@sql)
end
if @sql<>'|'
insert tmp values(@sql)
select * from tmpoid value
----------- -----------
1 1
2 2
3 10
4 4(4 行受影响)
if object_id('tmp') is not null
drop table tmp
go
create table tmp
(
oid int identity(1,1),
[value] int
)
go
if object_id('up_test') is not null
drop procedure up_test
go
create procedure up_test @str varchar(100)
as
declare @t table(col varchar(100))
insert into @t select @str
insert into tmp ([value]) select substring(col,number,charindex('|',col+'|',number)-number) from @t a cross join master..spt_values b where type='P' and number between 1 and len(col) and substring('|'+col,number,1)='|'
go
exec up_test '1|2|10|4'
go
select * from tmp
/*
oid value
----------- -----------
1 1
2 2
3 10
4 4(4 行受影响)
*/
set @a='1|2|10|4'
declare @b varchar(max)
set @b=REPLACE('insert into tmp (value) values ('+@a+')','|',');insert into tmp (value) values (')
exec (@b)