create function dbo.fn_split ( @inputstr varchar(8000), @seprator varchar(10) ) returns @temp table (a varchar(200)) as begin declare @i int set @inputstr = rtrim(ltrim(@inputstr)) set @i = charindex(@seprator, @inputstr) while @i >= 1 begin insert @temp values(left(@inputstr, @i - 1)) set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i) set @i = charindex(@seprator, @inputstr) end if @inputstr <> '\' insert @temp values(@inputstr) return end go --调用 declare @s varchar(1000) set @s='1,2,3,4,5,6,7,8,55' select * from dbo.fn_split(@s,',') drop function dbo.fn_split
如果只有四个,可以使用parsename()
create table tb(ID int, NAME varchar(10), AGE int)declare @ID as varchar(20) declare @NAME as varchar(20) declare @age as int set @id = '1,2,3,4' set @name='A,B,C,D' set @AGE=18 insert into tb select parsename(replace(@id , ',' , '.'),4) , parsename(replace(@name , ',' , '.'),4) , @age union all select parsename(replace(@id , ',' , '.'),3) , parsename(replace(@name , ',' , '.'),3) , @age union all select parsename(replace(@id , ',' , '.'),2) , parsename(replace(@name , ',' , '.'),2) , @age union all select parsename(replace(@id , ',' , '.'),1) , parsename(replace(@name , ',' , '.'),1) , @ageselect * from tbdrop table tb/*ID NAME AGE ----------- ---------- ----------- 1 A 18 2 B 18 3 C 18 4 D 18(所影响的行数为 4 行) */
楼上的大哥,我用你这个函数怎么只能用在一个字段里面啊。2个字段就报错了 insert 表A select bb.a ,aa.a , @age from dbo.fn_split(@ID,',') as bb, dbo.fn_split(@NAME,',')as aa 是不是我写错了
if object_id('tb')is not null drop table tb go create table tb(ID int,Name varchar(20),AGE INT) insert into tb values(1,'A',18) insert into tb values(2,'B',18) insert into tb values(3,'C',18) insert into tb values(4,'D',18) GO select ID,Name,AGE from tb group by ID,Name,AGE
用存储过程create table tb(ID int, NAME varchar(10), AGE int) go create procedure my_proc @id as varchar(20), @NAME as varchar(20), @age as int as begin while charindex(',' , @id) > 0 begin insert into tb values(left(@id , charindex(',' , @id) - 1) , left(@name , charindex(',' , @name) - 1) , @age) set @id = substring(@id , charindex(',' , @id) + 1 , len(@id)) set @NAME = substring(@NAME , charindex(',' , @NAME) + 1 , len(@NAME)) end insert into tb values(@id , @name , @age) end goexec my_proc '1,2,3,4' , 'A,B,C,D' , 18select * from tbdrop table tb drop procedure my_proc/* ID NAME AGE ----------- ---------- ----------- 1 A 18 2 B 18 3 C 18 4 D 18(所影响的行数为 4 行)*/
注意:ID和NAME的中的值必须按逗号匹配.create table tb(ID int, NAME varchar(10), AGE int) go create procedure my_proc @id as varchar(20), @NAME as varchar(20), @age as int as begin while charindex(',' , @id) > 0 begin insert into tb values(left(@id , charindex(',' , @id) - 1) , left(@name , charindex(',' , @name) - 1) , @age) set @id = substring(@id , charindex(',' , @id) + 1 , len(@id)) set @NAME = substring(@NAME , charindex(',' , @NAME) + 1 , len(@NAME)) end insert into tb values(@id , @name , @age) end goexec my_proc '1,2,3,4,5,6,7,8' , 'A,B,C,D,E,F,G,H' , 19select * from tbdrop table tb drop procedure my_proc/* ID NAME AGE ----------- ---------- ----------- 1 A 19 2 B 19 3 C 19 4 D 19 5 E 19 6 F 19 7 G 19 8 H 19(所影响的行数为 8 行) */
CREATE PROC ly @ID VARCHAR(100)='1,2,3,4', @NAME VARCHAR(100)='A,B,C,D,E', @AGE int=18 AS SET NOCOUNT ON DECLARE @b TABLE(Id INT IDENTITY(1,1),x VARCHAR(100)) DECLARE @a TABLE(Id int identity(1,1),Ix INT,NAME VARCHAR(20),age INT)IF LEN(@name)-LEN(REPLACE(@name,',',''))>=LEN(@ID)-LEN(REPLACE(@id,',','')) BEGIN INSERT @a (NAME,age) SELECT x,@age FROM dbo.getIL(@name) INSERT @b (x) SELECT x FROM dbo.getIL(@id) UPDATE a SET Ix=x FROM @a a Inner join @b b ON a.id=b.id END ELSE BEGIN INSERT @a (ix,age) SELECT x,@age FROM dbo.getIL(@id) INSERT @b (x) SELECT x FROM dbo.getIL(@name) UPDATE a SET Name=x FROM @a a Inner join @b b ON a.id=b.id END SELECT * FROM @aGOCREATE FUNCTION getIL(@x VARCHAR(100)) RETURNS @a TABLE(id INT IDENTITY(1,1),x VARCHAR(100)) AS BEGIN INSERT @a(x) SELECT SUBSTRING(@x+',',langid,CHARINDEX(',',@x+',',langid)-langid) FROM MASTER.dbo.syslanguages A WHERE SUBSTRING(','+@x,langid,1)=',' ORDER BY langid RETURN ENDGOEXEC ly --result /*Id Ix NAME age ----------- ----------- -------------------- ----------- 1 1 A 18 2 2 B 18 3 3 C 18 4 4 D 18 5 NULL E 18*/
(
@inputstr varchar(8000),
@seprator varchar(10)
)
returns @temp table (a varchar(200))
as
begin
declare @i int
set @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator, @inputstr)
while @i >= 1
begin
insert @temp values(left(@inputstr, @i - 1))
set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i)
set @i = charindex(@seprator, @inputstr)
end
if @inputstr <> '\'
insert @temp values(@inputstr)
return
end
go
--调用
declare @s varchar(1000)
set @s='1,2,3,4,5,6,7,8,55'
select * from dbo.fn_split(@s,',')
drop function dbo.fn_split
declare @NAME as varchar(20)
declare @age as int
set @id = '1,2,3,4'
set @name='A,B,C,D'
set @AGE=18 insert into tb
select parsename(replace(@id , ',' , '.'),4) , parsename(replace(@name , ',' , '.'),4) , @age
union all
select parsename(replace(@id , ',' , '.'),3) , parsename(replace(@name , ',' , '.'),3) , @age
union all
select parsename(replace(@id , ',' , '.'),2) , parsename(replace(@name , ',' , '.'),2) , @age
union all
select parsename(replace(@id , ',' , '.'),1) , parsename(replace(@name , ',' , '.'),1) , @ageselect * from tbdrop table tb/*ID NAME AGE
----------- ---------- -----------
1 A 18
2 B 18
3 C 18
4 D 18(所影响的行数为 4 行)
*/
insert 表A select bb.a ,aa.a , @age from dbo.fn_split(@ID,',') as bb, dbo.fn_split(@NAME,',')as aa
是不是我写错了
if object_id('tb')is not null
drop table tb
go
create table tb(ID int,Name varchar(20),AGE INT)
insert into tb values(1,'A',18)
insert into tb values(2,'B',18)
insert into tb values(3,'C',18)
insert into tb values(4,'D',18)
GO
select ID,Name,AGE
from tb
group by ID,Name,AGE
go
create procedure my_proc
@id as varchar(20),
@NAME as varchar(20),
@age as int
as
begin
while charindex(',' , @id) > 0
begin
insert into tb values(left(@id , charindex(',' , @id) - 1) , left(@name , charindex(',' , @name) - 1) , @age)
set @id = substring(@id , charindex(',' , @id) + 1 , len(@id))
set @NAME = substring(@NAME , charindex(',' , @NAME) + 1 , len(@NAME))
end
insert into tb values(@id , @name , @age)
end
goexec my_proc '1,2,3,4' , 'A,B,C,D' , 18select * from tbdrop table tb
drop procedure my_proc/*
ID NAME AGE
----------- ---------- -----------
1 A 18
2 B 18
3 C 18
4 D 18(所影响的行数为 4 行)*/
go
create procedure my_proc
@id as varchar(20),
@NAME as varchar(20),
@age as int
as
begin
while charindex(',' , @id) > 0
begin
insert into tb values(left(@id , charindex(',' , @id) - 1) , left(@name , charindex(',' , @name) - 1) , @age)
set @id = substring(@id , charindex(',' , @id) + 1 , len(@id))
set @NAME = substring(@NAME , charindex(',' , @NAME) + 1 , len(@NAME))
end
insert into tb values(@id , @name , @age)
end
goexec my_proc '1,2,3,4,5,6,7,8' , 'A,B,C,D,E,F,G,H' , 19select * from tbdrop table tb
drop procedure my_proc/*
ID NAME AGE
----------- ---------- -----------
1 A 19
2 B 19
3 C 19
4 D 19
5 E 19
6 F 19
7 G 19
8 H 19(所影响的行数为 8 行)
*/
@ID VARCHAR(100)='1,2,3,4',
@NAME VARCHAR(100)='A,B,C,D,E',
@AGE int=18
AS
SET NOCOUNT ON DECLARE @b TABLE(Id INT IDENTITY(1,1),x VARCHAR(100))
DECLARE @a TABLE(Id int identity(1,1),Ix INT,NAME VARCHAR(20),age INT)IF LEN(@name)-LEN(REPLACE(@name,',',''))>=LEN(@ID)-LEN(REPLACE(@id,',',''))
BEGIN
INSERT @a (NAME,age) SELECT x,@age FROM dbo.getIL(@name)
INSERT @b (x) SELECT x FROM dbo.getIL(@id)
UPDATE a SET Ix=x FROM @a a Inner join @b b ON a.id=b.id
END
ELSE
BEGIN
INSERT @a (ix,age) SELECT x,@age FROM dbo.getIL(@id)
INSERT @b (x) SELECT x FROM dbo.getIL(@name)
UPDATE a SET Name=x FROM @a a Inner join @b b ON a.id=b.id
END
SELECT * FROM @aGOCREATE FUNCTION getIL(@x VARCHAR(100))
RETURNS @a TABLE(id INT IDENTITY(1,1),x VARCHAR(100))
AS
BEGIN
INSERT @a(x)
SELECT SUBSTRING(@x+',',langid,CHARINDEX(',',@x+',',langid)-langid)
FROM MASTER.dbo.syslanguages A
WHERE SUBSTRING(','+@x,langid,1)=',' ORDER BY langid
RETURN
ENDGOEXEC ly --result
/*Id Ix NAME age
----------- ----------- -------------------- -----------
1 1 A 18
2 2 B 18
3 3 C 18
4 4 D 18
5 NULL E 18*/