SQLServer自訂函數——fnSplit

/*************************************************************
** File: fnSplit.sql
** Name: fnSplit
** Description: Split the string.
  分割字串
** Return values: @temp 
** Parameters: @sql,@splits
** Author: 何毅翔(MyGodness)
** Date: 2005-1-11
** Use Method: select * from dbo.split('1,2,3,4',',')
*************************************************************/

CREATE Function Split(@Sql varchar(8000),@Splits varchar(100))
returns @temp Table (tempColumn varchar(100))
As
Begin
 Declare @i Int
 Set @Sql = RTrim(LTrim(@Sql))
 Set @i = CharIndex(@Splits,@Sql)
 While @i >= 1
  Begin
  Insert @temp Values(Left(@Sql,@i-1))
  Set @Sql = SubString(@Sql,@i+len(@Splits),Len(@Sql)-@i)
  Set @i = CharIndex(@Splits,@Sql)
  End

 If @Sql <> ''
 Insert @temp Values (@Sql)
 Return
End