declare @str as varchar(8000)
set @str='1-101,2-201,3-306,4-405,5-501,6-602'
declare @key as datetime
set @key ='2009/08/12'
declare @t table
(
Day datetime
,A int
,B int
)
结果
=================================
Day A B
2009/08/12 1 101
2009/08/12 2 201
2009/08/12 3 306
2009/08/12 4 405
2009/08/12 5 501
2009/08/12 6 602等等
循环处理截取字符串,构造出INSERT 语句,一时写不出来,大家帮个忙
set @str='1-101,2-201,3-306,4-405,5-501,6-602'
declare @key as datetime
set @key ='2009/08/12'
declare @t table
(
Day datetime
,A int
,B int
)
结果
=================================
Day A B
2009/08/12 1 101
2009/08/12 2 201
2009/08/12 3 306
2009/08/12 4 405
2009/08/12 5 501
2009/08/12 6 602等等
循环处理截取字符串,构造出INSERT 语句,一时写不出来,大家帮个忙
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_splitSTR]
GO--3.2.1 循环截取法
CREATE FUNCTION f_splitSTR(
@s varchar(8000), --待分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
DECLARE @splitlen int
SET @splitlen=LEN(@split+'a')-2
WHILE CHARINDEX(@split,@s)>0
BEGIN
INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
END
INSERT @re VALUES(@s)
RETURN
END
declare @str as varchar(8000)
set @str='1-101,2-201,3-306,4-405,5-501,6-602'
declare @key as datetime
set @key ='2009/08/12'
declare @t table
(
Day datetime
,A varchar(10)
,B int
)
insert into @t(A)
select * from dbo.f_splitSTR(@str,',')
update @t
set Day=@key,,
A=LEFT(a,1)
,B=RIGHT(a,3)
select * from @t
/*
(6 行受影响)(6 行受影响)
Day A B
----------------------- ---------- -----------
2009-08-12 00:00:00.000 1 101
2009-08-12 00:00:00.000 2 201
2009-08-12 00:00:00.000 3 306
2009-08-12 00:00:00.000 4 405
2009-08-12 00:00:00.000 5 501
2009-08-12 00:00:00.000 6 602(6 行受影响)
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_splitSTR]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_splitSTR]
GO--3.2.1 循环截取法
CREATE FUNCTION f_splitSTR(
@s varchar(8000), --待分拆的字符串
@split varchar(10) --数据分隔符
)RETURNS @re TABLE(col varchar(100))
AS
BEGIN
DECLARE @splitlen int
SET @splitlen=LEN(@split+'a')-2
WHILE CHARINDEX(@split,@s)>0
BEGIN
INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))
SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')
END
INSERT @re VALUES(@s)
RETURN
END
declare @str as varchar(8000)
set @str='1-101,2-201,3-306,4-405,5-501,6-602'
declare @key as datetime
set @key ='2009/08/12'
declare @t table
(
Day varchar(10)
,A varchar(10)
,B int
)
insert into @t(A)
select * from dbo.f_splitSTR(@str,',')
update @t
set Day=convert(varchar(10),@key,111),
A=LEFT(a,1)
,B=RIGHT(a,3)
select * from @t
/*
Day A B
---------- ---------- -----------
2009/08/12 1 101
2009/08/12 2 201
2009/08/12 3 306
2009/08/12 4 405
2009/08/12 5 501
2009/08/12 6 602
(6 行受影响)
*/
http://blog.csdn.net/happyflystone/archive/2009/07/21/4365264.aspx
(
Day datetime
,A int
,B int
)
declare @str as varchar(8000)
set @str='1-101,2-201,3-306,4-405,5-501,6-602'
declare @key as datetime
set @key ='2009/08/12'
set @str=@str+','
while charindex(',',@str)>0
begin
declare @s varchar(10)
set @s=left(@str,charindex(',',@str)-1)
insert into t select @key,left(@s,charindex('-',@s)-1),right(@s,len(@s)-charindex('-',@s))
set @str=stuff(@str,1,charindex(',',@str),'')
end
select * from t/*
Day A B
----------------------- ----------- -----------
2009-08-12 00:00:00.000 1 101
2009-08-12 00:00:00.000 2 201
2009-08-12 00:00:00.000 3 306
2009-08-12 00:00:00.000 4 405
2009-08-12 00:00:00.000 5 501
2009-08-12 00:00:00.000 6 602(6 行受影响)
*/drop table t
set @str='1-101,2-201,3-306,4-405,5-501,6-602'
declare @key as datetime
set @key ='2009/08/12' declare @s varchar(max)
set @s='
select '''+convert(varchar(10),@key,111)+''' a,row_number()over(order by getdate()) b,stuff(t.num,1,charindex(''-'',t.num),'''') as num
from (select num='''+replace(@str,',',''' union all select ''')+''') t'declare @t table
(
Day datetime
,A int
,B int
)
insert @t exec(@s)select convert(varchar(10),Day,111) as Day,A,B from @t
/*
Day A B
---------- ----------- -----------
2009/08/12 1 101
2009/08/12 2 201
2009/08/12 3 306
2009/08/12 4 405
2009/08/12 5 501
2009/08/12 6 602(6 行受影响)
*/
if object_id('sp_test') is not null
drop proc sp_test
go
create proc sp_test
@str varchar(8000),
@key datetime
as
declare @s varchar(max)
set @s='
select '''+convert(varchar(10),@key,111)+''' a,row_number()over(order by getdate()) b,stuff(t.num,1,charindex(''-'',t.num),'''') as num
from (select num='''+replace(@str,',',''' union all select ''')+''') t'
exec(@s)
godeclare @str as varchar(8000)
set @str='1-101,2-201,3-306,4-405,5-501,6-602'
declare @key as datetime
set @key ='2009/08/12' declare @t table
(
Day datetime
,A int
,B int
) insert @t exec sp_test @str,@keyselect convert(varchar(10),Day,111) as Day,A,B from @t
/*
Day A B
---------- ----------- -----------
2009/08/12 1 101
2009/08/12 2 201
2009/08/12 3 306
2009/08/12 4 405
2009/08/12 5 501
2009/08/12 6 602(6 行受影响)
*/如果系统是SQL2005以上版本,这个过程支持,而且效率不低。
(
@NO VARCHAR(8000)
,@strSDATE VARCHAR(10)
)
RETURNS @T TABLE([Day]INT, A VARCHAR(10), B VARCHAR(10)
SET @I1 = CHARINDEX(',', @NO)
WHILE @I1 > 1
BEGIN
SET @V1 = SUBSTRING(@NO, 1, @I1 - 1)
INSERT @TT SELECT @strSDATE, LEFT(@V1,1), RIGHT(@V1,3)
SET @NO= STUFF(@NO, 1, @I1, '')
SET @I1 = CHARINDEX(',', @NO)
END
INSERT @T SELECT * FROM @TT
RETURN
END
SELECT * FROM dbo.INSERTROWS0813(@key ,@str)
set @str='1-101,2-201,3-306,4-405,5-501,6-602'
declare @key as datetime
set @key ='2009/08/12'
declare @t table
(
Day datetime
,A int
,B int
) declare @str2 nvarchar(100)
set @str2=N''while CHARINDEX(',',@str)>0
begin
set @str2=SUBSTRING(@str,1,charindex(',',@str)-1)
insert @t(Day,A,B)
select @key,LEFT(@str2,1),RIGHT(@str2,3)
set @str=stuff(@str,1,charindex(',',@str),N'')
end
select * from @t
set @str='1-101,2-201,3-306,4-405,5-501,6-602'
declare @key as datetime
set @key ='2009/08/12'
declare @t table
(
Day datetime
,A int
,B int
) declare @str2 nvarchar(100)
set @str2=N''while CHARINDEX(',',@str)>0
begin
set @str2=SUBSTRING(@str,1,charindex(',',@str)-1)
insert @t(Day,A,B)
select @key,LEFT(@str2,1),RIGHT(@str2,3)
set @str=stuff(@str,1,charindex(',',@str),N'')
end
select * from @t
/*
Day A B
2009-08-12 00:00:00.000 1 101
2009-08-12 00:00:00.000 2 201
2009-08-12 00:00:00.000 3 306
2009-08-12 00:00:00.000 4 405
2009-08-12 00:00:00.000 5 501
*/