排序问题，想了好几天了...望高人指点

-- ELSE部分有点小错误，修正一下：



CREATE OR REPLACE FUNCTION dot_lpad

(

  v_char_col VARCHAR2, -- 将要填充的变量(字段)

  v_sign     VARCHAR2, -- 字段中的字符判断标识(你的例子中是句点(.))

  bit_num    NUMBER,   -- 将要左填充的位数

  fill_str   VARCHAR2  -- 将要左填充的字符

)

  RETURN VARCHAR2

AS

  v_char_col2 VARCHAR2(4000);

  v_loop      NUMBER(4,0);

  v_return    VARCHAR2(4000);

BEGIN

  v_char_col2 := v_sign||v_char_col||v_sign;

  v_return := '';

  v_loop := length(v_char_col2) - length(replace(v_char_col2,v_sign,''));

  IF v_loop > 2 THEN

  BEGIN

    FOR i IN 1..v_loop LOOP

      v_return := v_return||lpad( substr(v_char_col2,instr(v_char_col2,v_sign,1,i)+1,instr(v_char_col2,v_sign,1,i+1)-instr(v_char_col2,v_sign,1,i)-1),bit_num,fill_str);

    END LOOP;

    RETURN v_return;

  END;

  ELSE 

    RETURN substr(v_char_col2,2,length(v_char_col2)-2);

  END IF;

END;

/



-----------------------------------------------------------------------------------------------------------

DROP TABLE test;



CREATE TABLE test(name VARCHAR2(40));



INSERT INTO test(name) VALUES('1.1');

INSERT INTO test(name) VALUES('1.1.1');

INSERT INTO test(name) VALUES('1.2.1');

INSERT INTO test(name) VALUES('1.101.5');

INSERT INTO test(name) VALUES('1.10.100.20.3');



COMMIT;



column name for a15

column name2 for a20

SELECT name,

       dot_lpad(name,'.',4,'0') as name2

FROM test

ORDER BY dot_lpad(name,'.',4,'0') desc;





SELECT name,

       dot_lpad(name,'-',4,'0') as name2

FROM test

ORDER BY dot_lpad(name,'-',4,'0') desc;

select ccm,

       ccm||'-0-0-0' ccmm,

       lpad(  substr(ccm||'-0-0-0', 1,                              instr(ccm||'-0-0-0','-')-1),                                        4, '0' ) as ccm1,

       lpad(  substr(ccm||'-0-0-0', instr(ccm||'-0-0-0','-',1,1)+1, instr(ccm||'-0-0-0','-',1,2)-instr(ccm||'-0-0-0','-',1,1)-1 ),      4, '0' ) as ccm2,

       lpad(  substr(ccm||'-0-0-0', instr(ccm||'-0-0-0','-',1,2)+1, instr(ccm||'-0-0-0','-',1,3)-instr(ccm||'-0-0-0','-',1,2)-1 ),      4, '0' ) as ccm3

 from table1

ORDER BY lpad(  substr(ccm||'-0-0-0', 1,                              instr(ccm||'-0-0-0','-')-1),                                      4, '0' ),

         lpad(  substr(ccm||'-0-0-0', instr(ccm||'-0-0-0','-',1,1)+1, instr(ccm||'-0-0-0','-',1,2)-instr(ccm||'-0-0-0','-',1,1)-1 ),    4, '0' ),

         lpad(  substr(ccm||'-0-0-0', instr(ccm||'-0-0-0','-',1,2)+1, instr(ccm||'-0-0-0','-',1,3)-instr(ccm||'-0-0-0','-',1,2)-1 ),    4, '0' );



---------------------------------------------------------------------------------------------------------------------------------------------------------

CCM       CCMM         CCM1             CCM2               CCM3

--------- ------------ ---------------- ------------------ --------------------

1         1-0-0-0      0001             0000               0000

1-1       1-1-0-0-0    0001             0001               0000

1-1-1     1-1-1-0-0-0  0001             0001               0001

1-1-2     1-1-2-0-0-0  0001             0001               0002

1-2       1-2-0-0-0    0001             0002               0000

1-3       1-3-0-0-0    0001             0003               0000

1-3-1     1-3-1-0-0-0  0001             0003               0001

1-3-2     1-3-2-0-0-0  0001             0003               0002

1-3-3     1-3-3-0-0-0  0001             0003               0003

1-4       1-4-0-0-0    0001             0004               0000

1-5       1-5-0-0-0    0001             0005               0000

1-6       1-6-0-0-0    0001             0006               0000

1-7       1-7-0-0-0    0001             0007               0000

1-8       1-8-0-0-0    0001             0008               0000

1-9       1-9-0-0-0    0001             0009               0000

1-10      1-10-0-0-0   0001             0010               0000



16 rows selected.