How to sort varchar values in oracle

Question

I want to know how to sort ascending or descending varchar values.

This is my field and values Filling_s_no varchar2;

Table:

Id  Filling_s_no    name
1   1.1.1            X
1   1.1.10           X
1   1.1.5            X
1   1.1.2            X
1   1.2.1            X
1   1.2.0            X

Now I want to sort my table like this format

Id  Filling_s_no    name
1   1.1.1            X
1   1.1.2            X
1   1.1.5            X
1   1.1.10           X
1   1.2.0            X
1   1.2.1            X

I tried with this query but am unable get the solution.

select * 
  from TCD_EX_Tab 
 where id=1 
 order by Filling_s_no ASC;

Answer 1

The following query will do what you need:

SELECT ID, FILLING_S_NO, NAME
  FROM TCD_EX_TAB
  where id=1 
  order BY TO_NUMBER(REGEXP_SUBSTR(filling_s_no, '[0-9]+', 1, 1)),
           TO_NUMBER(REGEXP_SUBSTR(filling_s_no, '[0-9]+', 1, 2)),
           TO_NUMBER(REGEXP_SUBSTR(filling_s_no, '[0-9]+', 1, 3));

The problem to be solved is that the components of the text string need to be converted to numbers in order to sort as you want them to. We use a regular expression to pick out each of the sub-elements of the string, then convert those sub-elements to type NUMBER for sorting purposes.

Best of luck.