实现制定分隔符拆分字符串

gong_max

不使用parsename函数来拆分字符串
ALTER function dbo.UDF_SplitSubString

(

@Expression varchar(8000)

,@Delimiter varchar(100)

,@ int

)

returns varchar(8000)

as

begin

--declare @Expression varchar(8000)

--set @Expression = 'abc@@pppp@aa'

--declare @Delimiter varchar(100)

--set @Delimiter = '@@@'

--declare @ int

--set @ = 3


declare @p int

set @p = CharIndex(@Delimiter,@Expression)

if @p > 0

begin

   set @p = @p + len(@Delimiter) - 1

end

declare @i int

set @i = 1

while @i  @

begin  

   set @i = @i + 1

   set @Expression = substring (@Expression, @p + 1,len(@Expression) - @p )

   set @p = CharIndex(@Delimiter,@Expression)

   if @p > 0

   begin

      set @p = @p + len(@Delimiter) - 1

   end

   else

   begin

      break

   end

end


declare @s varchar(1000)

if @p = 0 and @i = @

begin

   set @s = @Expression

end

else if @i = @

begin

   set @s = substring(@Expression, 1,@p - len(@Delimiter))

end

return @s

end


GO


declare @ varchar(1000)

set @ = '@@playyuer@www&&.Microshaoft&&com@90.90@@pppp@.'

declare @d varchar(1000)

set @d = '@@'

select

dbo.UDF_SplitSubString (@ , @d, 0)

,dbo.UDF_SplitSubString (@ , @d, 1)

,dbo.UDF_SplitSubString (@ , @d, 2)

,dbo.UDF_SplitSubString (@ , @d, 3)

,dbo.UDF_SplitSubString (@ , @d, 4)

,dbo.UDF_SplitSubString (@ , @d, 5)

,dbo.UDF_SplitSubString (@ , @d, 6)

,dbo.UDF_SplitSubString (@ , @d, 7)

,dbo.UDF_SplitSubString (@ , @d, 8)

,dbo.UDF_SplitSubString (@ , @d, 100)
               
               
               

本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u2/63828/showart_576170.html