oracel 排序含有字母的解决方式

今天在工作时,涉及到一个排序工作

正常是 我是使用 oracle  的 to_number()

但是这个排序字段中 含有 字母

搜索了一下其他人的解决方案 

摘自 http://blog.csdn.net/luocm/article/details/2648559

本文介绍了判断字符串是否全为数字的4种办法，另外还介绍了一个translate函数的小技巧，从任意字符串中提取数字（调用2次translate函数）。这个办法是一个公司同事发现的，用起来很方便，但理解起来稍有点困难。
1、通过ASCII码判断是否数字，介于[48, 57]之间，（ascii('0') = 48, ascii('9') = '57'）
2、调用cast函数尝试强制转换成NUMERIC或NUMBER，不是合法数字串即抛异常
3、调用translate函数，剔除所有[0-9]数字后，看是否为空串
4、调用正则表达式，进行模式匹配（10g版本新加入的功能）

    --通过ASCII码判断是否数字，介于[48, 57]之间，（ascii('0') = 48, ascii('9') = '57'）  
    DECLARE  
        str             VARCHAR2(10) := '123a';  
        val             NUMERIC(10);  
        i               int;  
        k               int;  
        flag            BOOLEAN;  
    BEGIN  
        flag := TRUE;  
        for i in 1..10 loop   --新密码是否6位数字  
            k := ascii(substr(str, i, 1));  
            if k < 48 or k > 57 THEN  
                flag := FALSE;  
            end if;  
        end LOOP;  
          
        IF flag = true THEN  
            dbms_output.put_line(str || '是[0-9]的数字序列');  
        ELSE  
            dbms_output.put_line(str || '不是[0-9]的数字序列');  
        END IF;  
    END;  
      
    -- 调用cast函数尝试强制转换成NUMERIC或NUMBER，不是合法数字串即抛异常  
    DECLARE  
        str    VARCHAR2(10) := '123';  
        val    NUMERIC(10);  
    BEGIN  
        val := CAST(str AS NUMERIC);  
        dbms_output.put_line(str || '是[0-9]的数字序列');  
    EXCEPTION  
       WHEN value_error THEN -- 字符串转实数错误  
           --dbms_output.put_line(SQLCODE || ', ' || SQLERRM);  
           dbms_output.put_line(str || '不是[0-9]的数字序列');  
    END;  
      
    --调用translate函数，剔除所有[0-9]数字后，看是否为空串  
    DECLARE  
        str VARCHAR2(10) := '123abc';  
    BEGIN  
        IF replace(translate(str, '0123456789', '0'), '0', '') IS NULL THEN  
            dbms_output.put_line(str || '是[0-9]的数字序列');  
        ELSE  
            dbms_output.put_line(str || '不是[0-9]的数字序列');  
        END IF;  
    END;  
      
    --调用正则表达式，进行模式匹配（10g版本新加入的功能）  
    SELECT *  
    FROM dual  
    WHERE regexp_like('1234', '^[[:digit:]]+$');  
      
    --从任意字符串中提取数字串（调用2次translate函数）。  
    --假定初始串为str。首先将str中数字全部替换为空格，输出记为str2；  
    --其次，对每个在str中出现的任意str2串中字符，如果是str2的首字符则替换为空格，其它字符则全部剔除  
    DECLARE  
        --str    VARCHAR2(100) := ' 护照01浙江2     3昆 山4苏 3';  
        str    VARCHAR2(100) := ' 护照浙江     昆 山苏 4';  
        ret    VARCHAR2(10);  
    BEGIN  
        ret := TRIM(TRANSLATE(str, trim(TRANSLATE(str, '1234567890', '          ')), ' '));  
        dbms_output.put_line(ret);  
    END;  

上面的方式应该可以行但是 我想用最小的修改完成效果

所以 思考后  使用了 translate  替换相应字母为 数字

然后根据 这个字段进行排序

select  zydm, zymc, kskm, ordernum
  from (select c.id,
               a.zydm,
               a.zymc,
               b.yjfxm,
               b.yjfxmc,
               c.kskm,
               translate(a.zydm, 'ZJ', '99') as ordernum
          from mlcj_zy_info a, mlcj_yjfx_info b, mlcj_yjfx_kskm c
         where a.type = b.type
           and a.type = c.type
           and a.recruitno = b.recruitno
           and a.recruitno = c.recruitno
           and a.zydm = b.zydm
           and a.zydm = c.zydm
           and b.yjfxm = c.yjfxm
           and allflag = '1'
           and a.type = '0'
           and a.recruitno in
               (select id from mng_recruitno_info where mlcj_selected = '1'))
 order by to_number(ordernum) asc, zydm asc

注意 此处 原来是 使用 distinct 过滤  导致 结果排序乱掉了  后来我改成了 group by