存储函数实现字符串中存在数字的排序-创新互联

问题：不做处理的排序：A2.1，A2.10，A2.2；希望的排序：A2.1，A2.2，A2.10

解决：

******************************************Oracle实现方式*******************************************

--11g解决排序字段存在数字和其他字符时的排序，对数字的部分统一左边补6个零，再排序｛
 create or replace function toOder11g(str varchar2) return varchar2 as
 begin
      declare num_count number:=1;
      query_sql varchar2(300);
      newstr varchar2(300):=str;--返回的新字符串
      tempstr varchar2(300);--表示替换成xx字符
      sumcount number:=regexp_count(str,'[0-9]+',1,'i');--查找字符串中匹配数字的个数
       begin
          for num_count in 1..sumcount loop

                --把数字部分截取补零后替换字符串中的数字部分
               tempstr:=lpad(regexp_substr(newstr,'[0-9]+',1,num_count,'i'),6,'0');--左边补零6位

               newstr:=regexp_replace(newstr,'[0-9]+',tempstr,1,num_count,'i');
          end loop;
           return newstr;
        end;
 end toOder11g;
 --使用
 set serveroutput on;
 begin
  dbms_output.put_line('1212中国2323-33.2齐位补零后：'|| toOder11g('1212中国2323-33.2') ||'');
 end;
--｛

--10g解决排序字段存在数字和其他字符时的排序，对数字的部分统一左边补6个零，再排序
--（与11G基本一致，只是需要用自定义的regexpcount（获取数字的个数，因为10G没有这个函数））｛
 create or replace function toOder10g(str varchar2) return varchar2 as
   begin
     declare num_count number:=1;
     query_sql varchar2(300);
     newstr varchar2(300):=str;--返回的新字符串
     tempstr varchar2(300);--表示替换成xx字符
     sumcount number:=regexpcount(str,'[0-9]+');--数字个数
     begin
       for num_count in 1..sumcount loop
           tempstr:=lpad(regexp_substr(newstr,'[0-9]+',1,num_count,'i'),6,'0');--左边补零6位
           newstr:=regexp_replace(newstr,'[0-9]+',tempstr,1,num_count,'i');
       end loop;
       return newstr;
     end;
   end toOder10g;
--｛

--10g自定义返回正则表达式匹配的个数regexpcount（参数：源字符串，正则表达式，字符串每次遇到数字部分截取掉，计数加一，直到不存在数字）｛
   --定义
 create or replace function regexpcount(sourcestr varchar2,regexpstr varchar2) return number as
 begin
  declare nexindex number:=regexp_instr(sourcestr,regexpstr,1,1,1,'i');--定义第二个匹配组开始的位置
  rightcount number:=0;
  tempstr varchar2(300):=sourcestr;
  begin
       while nexindex>0 loop
          rightcount:=rightcount+1;

            --从第二个匹配组开始的位置起，截取后长度=总长度-起始位置+1

           tempstr:=substr(tempstr,nexindex,length(tempstr)-nexindex+1);--
          if(tempstr is null) then
                 return rightcount;
          end if;

            --重新为新的tempstr获取第二个匹配组

           nexindex:=regexp_instr(tempstr,regexpstr,1,1,1,'i');
       end loop;
       return rightcount;
  end;
 end regexpcount;

 --使用
 set serveroutput on;
 begin
  dbms_output.put_line('匹配该正则表达式的个数是：'|| regexpcount('1212AAA22ww','[0-9]+') ||'');
 end;
--｝

***************************************Oracle实现方式end************************************

***************************************sqlserver***********************************************

CREATE FUNCTION [titans_schema].[toorder](@sourcestr [varchar](50))
RETURNS [varchar](50) AS
begin
    declare @ret [varchar](50),@numindex int, @strindex int  --numindex不等于0，1表示第一个是数字，0表示没有数字
    set @numindex=patindex('%[0-9]%',@sourcestr) --返回数字首次出现的位置
     set @ret=''  --不能定义为null
    set @strindex=0
     while @numindex<>0       --当不等于0时，即存在数字时进入循环
          begin
               --从1开始截取，截取后长度=数字首次出现的位置-1,赋给ret,当第一个字符就是数字时这个值

                --是“”
               set @ret=@ret+substring(@sourcestr,1,(@numindex-1)) --aa
               --源字符串去掉非数字部分（从右边开始截取，长度=总长度-numindex+1）
               set @sourcestr=right(@sourcestr,len(@sourcestr)-@numindex+1)
               --截取数字部分补零成6位后跟前面的非数字部分合并
               --先获取非数字部分的index,如果为0,说明已经是末尾,补零后结束循环
               set @strindex=patindex('%[^0-9]%',@sourcestr)
               if @strindex=0
                    begin

                        --if else 中间用begin end包裹，一句话时虽然不用也没错
                         set @ret=@ret+right(replicate('0',6)+@sourcestr,6)

                         return ( @ret )
                    end
               else
                    begin

                        --从1开始截取长度=非数字首次出现的位置-1，截取到的数字再进行6个0的补零齐位
                         set @ret=@ret+right(replicate('0',6)+substring(@sourcestr,1,(@strindex-1)),6)
                    end
               --源字符串再去掉数字部分,接下来就进入了循环(非数字,数字,非数字....)
               set @sourcestr=right(@sourcestr,len(@sourcestr)-patindex('%[^0-9]%',@sourcestr)+1)
               --改变循环标识变量的值
               set @numindex=patindex('%[0-9]%',@sourcestr)
      end
   return ( @ret )
end

***************************************sqlserver实现方式end************************************

总结：

    以上函数均经过本人测试，如果有错误，改进，疑问的地方，请留言。文中使用的是函数在百度中均可查到，所以不再重复。

另外有需要云服务器可以了解下创新互联scvps.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。