存儲函數實現字符串中存在數字的排序

問題：不做處理的排序：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************************************

總結：

    以上函數均經過本人測試，如果有錯誤，改進，疑問的地方，請留言。文中使用的是函數在百度中均可查到，所以不再重復。