通过分界符划分字符串函数

-- 函数功能:  在字符串@str中,以@split为分界符，第@pos个的值
create   function  f_GetCol( @str   varchar ( 200 ), @split   varchar ( 10 ), @pos   int )
returns   varchar ( 20 )
as
begin
while   @pos > 1   and   charindex ( @split , @str + @split ) > 0
           select   @pos = @pos - 1 , @str = stuff ( @str , 1 , charindex ( @split , @str + @split ), '' )
return  ( isnull ( left ( @str , charindex ( @split , @str + @split ) - 1 ), '' ))
end

go

declare   @t    table  (msg  varchar ( 200 ))
insert   into   @t
select   ' YK|43.00|43.00| '   union   all
select   ' ZX|2350.00|2350.00| '   union   all
select   ' 2032|MD|15|120.00|120.00|4518105496132709,12/2006,0,,| '  

select  msg,
dbo.f_GetCol(msg, ' | ' , 1 )  as  col1,
dbo.f_GetCol(msg, ' | ' , 2 )  as  col2,
dbo.f_GetCol(msg, ' | ' , 3 )  as  col3 
  from   @t

/**/ /*
msg                        col1            col2             col3
-----------------------------------------------------------------------                --------       --------       ----------
YK|43.00|43.00|    YK    43.00    43.00
ZX|2350.00|2350.00|    ZX    2350.00    2350.00
2032|MD|15|120.00|120.00|4518105496132709,12/2006,0,,|    2032    MD    15
*/

drop   function  f_GetCol