sqlserver函数示例

alter function delefor(@str varchar(128))
 RETURNS char(128)
  begin
   declare @end int,
           @i int,
           @result varchar(128)
set @end=0
set @i=0
set @result=''
 
while(@end<len(@str))
begin
set @end=charindex('-',@str,@end)
set @i=@i+1
set @end=@end+1
if(@i=4)
begin
  set     @result=substring(@str,1,@end-2)
break
end
end
return(@result)
end

select dbo.delefor('LX-SWPZ-2007-00002-Y-4-2-202')结果：LX-SWPZ-2007-00002                                                                                                             

截取n个‘-’前面的字串
---------------------------------------------------------------------------------
字符串倒转并截取第n个‘-’后又翻转
create function gethouseid(@str varchar(128) )
 RETURNS char(128)
begin
   declare @i int,
           @strtmp varchar(128),
           @pos int,
    @startpos int,
    @rtnstr varchar(128)

 

   set @str = rtrim(@str)
   set @i = 0;
set @rtnstr = ''
 set @startpos = 0

set @strtmp = REVERSE(@str)

    while ( @i <= 3 )
    begin
      
       set @pos = CHARINDEX('-',@strtmp,@startpos)

       if (@pos > 0 )
        begin
          set @startpos = @pos+1
          set @i = @i + 1
          if(@i = 3 )
           begin
              set @rtnstr = substring(@str,len(@str) - @pos+2,@pos)
             -- set @rtnstr =@str
break;
           end
        end
       else
          begin
               if(@i = 2 )
                 set @rtnstr = @str

   break;
          end  
    end;

    return(@rtnstr)
end

select dbo.gets('长清龙泉居小区佳和苑6号楼3单元201室')
---------------------------------------------------------------
alter function gets(@str varchar(128))
returns char(128)
begin
declare @tmpstr varchar(128),
        @i int
set @tmpstr=replace(@str,'号楼','-')
set @tmpstr=replace(@tmpstr,'单元','-')
set @tmpstr=left(@tmpstr,len(@tmpstr)-1)
set @i=patindex('%0,1,6%',@tmpstr)
set @tmpstr=substring(@tmpstr,@i,len(@tmpstr)-@i)
return(@tmpstr)
end    
------------------------------------------------------------
在字串的某个数字前补0
alter function stradd(@str varchar(128))
returns char(128)
begin
 declare @substr varchar(128),
         @su varchar(128)
  set @su=substring(@str,1,3)
  set @substr=substring(@str,charindex('-',@str)+1,len(@str)-3)
if (len(@substr)=1)
begin
set @substr='00'+right(@substr,1)
end
if (len(@substr)=2)
begin
set @substr='0'+right(@substr,2)

end
return(@su+@substr)
end