create table tb(col1 varchar(10) , col2 int)
go
create proc my_proc @str1 as varchar(100),@str2 as varchar(100)
as
begin
while charindex(',',@str1) > 0
begin
insert into tb values(left(@str1 , charindex(',',@str1)-1) ,left(@str1 , charindex(',',@str2)-1) )
set @str1 = substring(@str1 , charindex(',',@str1)+1 , len(@str1))
set @str2 = substring(@str2 , charindex(',',@str2)+1 , len(@str2))
end
insert into tb values(@str1 , @str2)
end
goexec my_proc '00901,00501,234A25','215,300,458'select * from tbdrop table tb
drop proc my_proc/*
col1 col2
---------- -----------
00901 9
00501 5
234A25 458(所影响的行数为 3 行)
*/
create table tb(col1 varchar(10) , col2 int)
go
create proc my_proc @str1 as varchar(100),@str2 as varchar(100)
as
begin
while charindex(',',@str1) > 0
begin
insert into tb values(left(@str1 , charindex(',',@str1)-1) ,left(@str1 , charindex(',',@str2)-1) )
set @str1 = substring(@str1 , charindex(',',@str1)+1 , len(@str1))
set @str2 = substring(@str2 , charindex(',',@str2)+1 , len(@str2))
end
insert into tb values(@str1 , @str2)
end
goexec my_proc '00901','215'select * from tbdrop table tb
drop proc my_proc/*col1 col2
---------- -----------
00901 215(所影响的行数为 1 行)*/
go
create proc my_proc @str1 as varchar(100),@str2 as varchar(100)
as
begin
while charindex(',',@str1) > 0
begin
insert into tb values(left(@str1 , charindex(',',@str1)-1) ,left(@str1 , charindex(',',@str2)-1) )
set @str1 = substring(@str1 , charindex(',',@str1)+1 , len(@str1))
set @str2 = substring(@str2 , charindex(',',@str2)+1 , len(@str2))
end
insert into tb values(@str1 , @str2)
end
goexec my_proc '00901,00501,234A25','215,300,458'select * from tbdrop table tb
drop proc my_proc/*
col1 col2
---------- -----------
00901 9
00501 5
234A25 458(所影响的行数为 3 行)
*/
create table tb(col1 varchar(10) , col2 int)
go
create proc my_proc @str1 as varchar(100),@str2 as varchar(100)
as
begin
while charindex(',',@str1) > 0
begin
insert into tb values(left(@str1 , charindex(',',@str1)-1) ,left(@str1 , charindex(',',@str2)-1) )
set @str1 = substring(@str1 , charindex(',',@str1)+1 , len(@str1))
set @str2 = substring(@str2 , charindex(',',@str2)+1 , len(@str2))
end
insert into tb values(@str1 , @str2)
end
goexec my_proc '00901','215'select * from tbdrop table tb
drop proc my_proc/*col1 col2
---------- -----------
00901 215(所影响的行数为 1 行)*/
(
@s1 VARCHAR(1000),
@s2 VARCHAR(1000)
)
RETURNS @t TABLE (f1 VARCHAR(10),f2 INT)
AS
BEGIN
DECLARE @x1 XML,@x2 XML
SELECT @x1='<r>' + REPLACE(@s1,',','</r><r>') + '</r>',@x2='<r>' + REPLACE(@s2,',','</r><r>') + '</r>'
INSERT @t
SELECT f1,f2 FROM
(SELECT f1=x.value('.','VARCHAR(10)'),ROW_NUMBER() OVER(ORDER BY GETDATE()) idx FROM @x1.nodes('//r') AS T(x)) a,
(SELECT f2=x.value('.','VARCHAR(10)'),ROW_NUMBER() OVER(ORDER BY GETDATE()) idx FROM @x2.nodes('//r') AS T(x)) b
WHERE a.idx=b.idx
RETURN
END
GOSELECT * FROM ufn_test('00901,00501,234A25','215,300,458')
/*
00901 215
00501 300
234A25 458
*/
GODROP FUNCTION ufn_test
GO
(
@s1 VARCHAR(1000),
@s2 VARCHAR(1000)
)
RETURNS @t TABLE (f1 VARCHAR(10),f2 INT)
AS
BEGIN
WHILE CHARINDEX(',',@s1)>0
BEGIN
INSERT @t SELECT LEFT(@s1,CHARINDEX(',',@s1)-1),LEFT(@s2,CHARINDEX(',',@s2)-1)
SELECT @s1=STUFF(@s1,1,CHARINDEX(',',@s1),''),@s2=STUFF(@s2,1,CHARINDEX(',',@s2),'')
END
INSERT @t SELECT @s1,@s2
RETURN
END
GOSELECT * FROM ufn_test('00901,00501,234A25','215,300,458')
/*
00901 215
00501 300
234A25 458
*/
GODROP FUNCTION ufn_test
GO
as
begin
while charindex(',',@str1) > 0
begin
insert into @tb values(left(@str1 , charindex(',',@str1)-1) ,left(@str1 , charindex(',',@str2)-1) )
set @str1 = substring(@str1 , charindex(',',@str1)+1 , len(@str1))
set @str2 = substring(@str2 , charindex(',',@str2)+1 , len(@str2))
end
insert into @tb values(@str1 , @str2)
return
end
goselect * from dbo.my_func('00901,00501,234A25','215,300,458')
/*
col1 col2
---------- -----------
00901 9
00501 5
234A25 458(所影响的行数为 3 行)
*/select * from dbo.my_func('00901','215')
/*
col1 col2
---------- -----------
00901 215(所影响的行数为 1 行)
*/drop function dbo.my_func