回复人: smalldeer(晓露) ( ) 信誉:100 | 2004-7-2 10:48:38 | 得分: 0 |
/*
字符串分拆函数
--调用示例
select * from dbo.fsplit('a;adf;fd;ed;dh',';')
select * from dbo.fsplit('a,,adf,,fd,,ed,,dh',',,')
邹建 2003.07*/
--检查函数是否存在
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fsplit]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fsplit]
GO
--创建字符串分拆的函数
create function fsplit(
@str varchar(8000) --要分拆的字符串
,@spli varchar(10) --字符串分隔符
)
returns @re table(istr varchar(8000))
as
begin
declare @i int,@splen int
select @splen=len(@spli)
,@i=charindex(@spli,@str)
while @i>0
begin
insert into @re values(left(@str,@i-1))
select @str=substring(@str,@i+@splen,8000)
,@i=charindex(@spli,@str)
end
if @str<>'' insert into @re values(@str)
return
end
select * from dbo.fsplit(123|abc|000|777','|')
回复人: smalldeer(晓露) ( ) 信誉:100 | 2004-7-2 10:49:14 | 得分: 0 | |
以上是邹建的
我这样用
declare @string varchar(1000)
set @string='123|abc|000|777'
set @string='select * into ##tmp from (select '''+replace(@string,'|','''as str union all select ''')+''') a'
exec(@string)
select * from ##tmp
drop table ##tmp