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

 

 

转：http://apps.hi.baidu.com/share/detail/4523777