--写个自定义函数就行了.if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_split]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_split]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[序数表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [序数表]
GO--为了效率,所以要一个辅助表配合
select top 8000 id=identity(int,1,1) into 序数表
from syscolumns a,syscolumns b
alter table 序数表 add constraint pk_id_序数表 primary key(id)
go/*--字符串分函数 分拆指定分隔符的的字符串,返回结果表--邹建 2004-4--*/
create function f_split(
@str varchar(8000), --要分拆的字符串
@splitchar varchar(10) --分隔符
)returns table
as
return(
select re=substring(@str,id,charindex(@splitchar,@str+@splitchar,id)-id)
from 序数表
where id<=len(@str)+1 and charindex(@splitchar,@splitchar+@str,id)-id=0
)
go--调用
select * from f_split('asdlkfa,asf as,dd a,',',')
drop function [dbo].[f_split]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[序数表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [序数表]
GO--为了效率,所以要一个辅助表配合
select top 8000 id=identity(int,1,1) into 序数表
from syscolumns a,syscolumns b
alter table 序数表 add constraint pk_id_序数表 primary key(id)
go/*--字符串分函数 分拆指定分隔符的的字符串,返回结果表--邹建 2004-4--*/
create function f_split(
@str varchar(8000), --要分拆的字符串
@splitchar varchar(10) --分隔符
)returns table
as
return(
select re=substring(@str,id,charindex(@splitchar,@str+@splitchar,id)-id)
from 序数表
where id<=len(@str)+1 and charindex(@splitchar,@splitchar+@str,id)-id=0
)
go--调用
select * from f_split('asdlkfa,asf as,dd a,',',')
SET @s='aaa,bbb,ccc'
WHILE CHARINDEX(',',@s)>0
BEGIN
print LEFT(@s,CHARINDEX(',',@s)-1)
SET @s=RIGHT(@s,LEN(@s)-CHARINDEX(',',@s))
END
print @s
create trigger tr_示例 on 表
for insert
as
declare @s varchar(1000),@i int
select @s='aaa,bbb,ccc,…'
,@i=charindex(',',@s+',')
while @i>0
begin
print left(@s,@i-1)
select @s=substring(@s,@i+1,1000)
,@i=charindex(',',@s+',')
end
CREATE FUNCTION dbo.splitstring(@str varchar(4000),@c varchar(10))
RETURNS @t table(s varchar(100))
AS
BEGIN
DECLARE @s varchar(4000)
SET @s=@str
WHILE CHARINDEX(@c,@s)>0
BEGIN
INSERT INTO @t(s) VALUES(LEFT(@s,CHARINDEX(@c,@s)-1))
SET @s=RIGHT(@s,LEN(@s)-CHARINDEX(@c,@s))
END
INSERT INTO @t(s) VALUES(@s)
RETURN
END
GO
--调用:
select * from dbo.SplitString('1000,1001,1002',',')
结果集:
1000
1001
1002
如果这个需要分割的字符串是从数据库中读取的呢?
select @str = str from inserted
zjcxc(邹建)
pbsql(风云)