0
Hello All,
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;

Can anyone help me? 

Edited by sasikrishnasamy

2
Contributors
1
Reply
11
Views
2 Years
Discussion Span
Last Post by skaa
0
select * from
(
  select t2.col1, t2.coln, SUBSTR(t2.colr, 1, INSTR(t2.colr, '.') - 1) as coln2, SUBSTR(t2.colr, INSTR(t2.colr, '.') + 1) as coln3 from
  (
         select t.Filling_s_no, SUBSTR(t.Filling_s_no, 1, INSTR(t.Filling_s_no, '.') - 1) as coln, SUBSTR(t.Filling_s_no, INSTR(t.Filling_s_no, '.') + 1) as colr from TCD_EX_Tab t
  ) t2
) t3
order by to_number(coln), to_number(coln2), to_number(coln3);
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.