/****************************************************************
Name: fnSplitter
Description: 字串分拆
Usage: select result from fnSplitter('Data1|Data2|Data3','|')
以某一個字符把字串分析開來
Return Table : Result
------
Data1
Data2
Data3
History:
Date Name Description
****************************************************************/
create Function ufn_Splitter(@IDs Varchar(1050),@Delimited Varchar(5) )
Returns @Tbl_IDs Table (result Varchar(50)) As
Begin
-- Append comma
Set @IDs = @IDs + @Delimited
-- Indexes to keep the position of searching
Declare @Pos1 Int
Declare @pos2 Int
-- Start from first character
Set @Pos1=1
Set @Pos2=1
While @Pos1<Len(@IDs)
Begin
Set @Pos1 = CharIndex(@Delimited,@IDs,@Pos1)
Insert @Tbl_IDs Select Cast(Substring(@IDs,@Pos2,@Pos1-@Pos2) As Varchar(50))
-- Go to next non comma character
Set @Pos2=@Pos1+Len(@Delimited)
-- Search from the next charcater
Set @Pos1 = @Pos1+Len(@Delimited)
End
Return
End