SQL的Split方法

create function f_split(@SourceSql varchar(8000),@StrSeprate varchar(10))
returns @temp table(a varchar(100))
--实现split功能 的函数
--date    :2003-10-14
as
begin
    declare @i int
    set @SourceSql=rtrim(ltrim(@SourceSql))
    set @i=charindex(@StrSeprate,@SourceSql)
    while @i>=1
    begin
        insert @temp values(left(@SourceSql,@i-1))
        set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
        set @i=charindex(@StrSeprate,@SourceSql)
    end
    if @SourceSql<>''
       insert @temp values(@SourceSql)
    return
end

返回的是一个table，所以执行要用如下格式：select * from dbo.f_split('ABC:BC:C:D:E',':')

－－－－－－－－－－－－－－－－－－－－－－－－－－

SQL实现split函数，自定义分割字符，自定义取出第几个分割字符前的字符串

自定义取出第几个分割字符前的字符串，默认位置（0）
格式：dbo.split(字段名,'分隔字符',取出的第几个字符串)
如果没有分隔的字符，则返回整个字符串。
如果取出的位置字符串的位置超出Index则返回空。

CREATE FUNCTION [ dbo ] . [ split ]
( @str nvarchar ( 4000 ), @code varchar ( 10 ), @no int )  
RETURNS varchar ( 200 )
AS   
BEGIN

declare @intLen int
declare @count int
declare @indexb    int
declare @indexe    int
set @intLen = len ( @code )
set @count = 0
set @indexb = 1


if @no = 0
   if charindex ( @code , @str , @indexb ) <> 0
      return left ( @str , charindex ( @code , @str , @indexb ) - 1 )
   else
      return @str

while charindex ( @code , @str , @indexb ) <> 0
   begin
        set @count = @count + 1
        if @count = @no
          break
        set @indexb = @intLen + charindex ( @code , @str , @indexb )
   end


if @count = @no
   begin

       set @indexe = @intLen + charindex ( @code , @str , @indexb )
           if charindex ( @code , @str , @indexe ) <> 0
              return substring ( @str , charindex ( @code , @str , @indexb ) + len ( @code ), charindex ( @code , @str , @indexe ) - charindex ( @code , @str , @indexb ) - len ( @code ))
           else
              return right ( @str , len ( @str ) - charindex ( @code , @str , @indexb ) - len ( @code ) + 1 )

   end

return ''

END

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

CREATE Function f_trimstr(@str varchar(100))

returns varchar(100)

--功能：去掉字符串中的所有空格

AS

begin

declare @i int
declare @s1 varchar(50)
declare @result varchar(100)
declare @len int

select @result = ''
select @str = ltrim(rtrim(@str))
select @len = len(@str)
select @i = 1

while @i<=@len
begin
select @s1 = substring(@str,@i,1)
if(@s1<>'')
begin
select @result = @result + @s1
end
select @i = @i + 1
end

return @result

end

CREATE FUNCTION [ dbo ] . [ split ]
( @str nvarchar ( 4000 ), @code varchar ( 10 ), @no int )  
RETURNS varchar ( 200 )
AS   
BEGIN

declare @intLen int
declare @count int
declare @indexb    int
declare @indexe    int
set @intLen = len ( @code )
set @count = 0
set @indexb = 1


if @no = 0
   if charindex ( @code , @str , @indexb ) <> 0
      return left ( @str , charindex ( @code , @str , @indexb ) - 1 )
   else
      return @str

while charindex ( @code , @str , @indexb ) <> 0
   begin
        set @count = @count + 1
        if @count = @no
          break
        set @indexb = @intLen + charindex ( @code , @str , @indexb )
   end


if @count = @no
   begin

       set @indexe = @intLen + charindex ( @code , @str , @indexb )
           if charindex ( @code , @str , @indexe ) <> 0
              return substring ( @str , charindex ( @code , @str , @indexb ) + len ( @code ), charindex ( @code , @str , @indexe ) - charindex ( @code , @str , @indexb ) - len ( @code ))
           else
              return right ( @str , len ( @str ) - charindex ( @code , @str , @indexb ) - len ( @code ) + 1 )

   end

return ''

END