/*
 字符串分拆函數
 
--調用示例
 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','|')