How to sort a varchar in oracle

Question

SELECT CM.*
FROM EMPLOYEE CM 
ORDER BY CM.ROW_ID

The row_id is varchar type and I want to retrive the sorting order as follows

In the above query ROW_ID is varchar2 in Database, and I want to retrive the values like an integer.

I have tried with ORDER BY TO_NUMBER(CM.ROW_ID) but it is giving error like invalid number

EDIT:-

My sample data is:-

1
1.1
1.5
1.6
2.5
2.6
1.7
1.8
1.9
1.10
1.11
1.12
1.2
1.1.1
1.3
1.4
1.1.1.1
1.13
2
2.1
2.2
2.3
2.4
1.16
1.14
1.15
1.17

I want to retrive like:-

1
1.1
1.1.1
1.1.1.1
1.2
1.3
1.4
1.5
1.6
1.7
1.8
1.9
1.10
1.11
1.12
1.13
1.14
1.15
1.16
1.17
2
2.1
2.2
2.3
2.4
2.5
2.6

Edit your question and show sample data. Clearly a column that you think only contains "numbers" contains something else. — Gordon Linoff, Aug 19 '17 at 14:58
Look like version numbers to me, Maybe you could write a stored function which formats the numbers to fix length like "002.001". and "002.015" etc. and sort by that strings — Turo, Aug 19 '17 at 15:44

APC · Answer 1 · 2017-08-21T06:26:18.867

" I want to retrive the values like an integer"

The problem is, 3.1.2 is not a number. Sure, it features characters which can be categorised as numeric, but the multiple full-stops means it is a string.

So, to sort it numerically you first need to tokenize the string into its digits. Then you need to weight the elements so that the 3.0 is sorted before 3.1,3.1.1is sorted before3.2, and 3.2 is sorted before 3.12.

This solution uses a sub-query to split your row_id into the individual elements. The outer query aggregates these numbers, using power() function to apply the weighting:

select row_id
       , sum(nvl(no# * (power(1000,(cnt+1) - lvl)),0)) as sort_order
from (
    select row_id
           , level as lvl
           , x.cnt
           , to_number(regexp_substr(cte.row_id, '[^.]+', 1, level)) as no#
    from component_master cte
         cross join ( select max(regexp_count(row_id, '\.')) as cnt from component_master) x
    connect by level <= x.cnt
    and row_id = prior row_id
    and prior sys_guid() is not null
)
group by row_id
order by 2
/

Notes:

If row_id is not unique then sort_order will be wrong
The weights are powers of 1000. This allows you to have individual elements up to 999. If your elements are larger you will need to increase the base value. But...
The degree of nesting is calculated using the regexp_count() function. So you can create a row_id of 1.1.1.1.1.1 without having to re-write the query. But...
The generated sort_order cannot exceed Oracle's limit of number(38). So your combination of base value and nested depth must generate numbers less than that.
If row_id contains characters besides 0123456789. all bets are off.

Here is the output of my query against some sample data:

ROW_ID                          SORT_ORDER
-------------------- ---------------------
1                         1000000000000000
1.1                       1001000000000000
1.2                       1002000000000000
1.2.12                    1002012000000000
1.10                      1010000000000000
1.10.1                    1010001000000000
1.10.20                   1010002000000000
1.11.1                    1011001000000000
1.12                      1012000000000000
2                         2000000000000000
2.1                       2001000000000000
2.1.1                     2001001000000000
2.2                       2002000000000000
2.10                      2010000000000000
2.10.1                    2010001000000000
2.10.2                    2010002000000000
2.10.10                   2010010000000000
2.11                      2011000000000000
2.12                      2012000000000000
2.12.1                    2012001000000000
3                         3000000000000000
3.1                       3001000000000000
3.1.1.1.1.                3001001001001000
3.2                       3002000000000000
3.5                       3005000000000000
3.11.1.1.1.               3011001001001000
3.12.1.1.1.               3012001001001000
4.6                       4006000000000000
5.2                       5002000000000000
5.512.1                   5512001000000000

30 rows selected.

SQL>

will it handle only for 1.1 and 1.0 or every 2.1 and 2.10 ...etc.., — Murali, Aug 21 '17 at 06:05
Well it did for me: fifth row in the result set. So either you didn't"t run my exact query, in which case you need to post what you actually ran. Or your data is different from what you said it is; if so, please post some data which demonstrates the problem so I can reproduce it. — APC, Aug 21 '17 at 09:12
My column is varchar type and I need orderby of that column eighter in numeric or charecter format https://stackoverflow.com/questions/45793526/how-to-sort-an-varchar2-column-in-oracle?noredirect=1#comment78545298_45793526 The order of data is posted in that question as I cannot able to post that data here. — Murali, Aug 21 '17 at 09:58
for refference this is my plunker please access the plunker https://plnkr.co/edit/dlG6bJcBP8jaxhVEZ4wq?p=preview with this date you can get the format — Murali, Aug 21 '17 at 10:04
@Murali - even with your latest question edit, this query gets the result in the order you showed. It really isn't clear what you are actually doing differently. (Or what the plunker has anything to do with this) — Alex Poole, Aug 21 '17 at 10:24
@alexpoole - don't Google "plunker" or if you do, don't read the Urban Dictionary definition which turns up in the results: it will put you right off your lunch. — APC, Aug 21 '17 at 10:31

Gordon Linoff · Answer 2 · 2017-08-19T15:39:00.137

If the character field "looks" like numbers and they are only integers and not randomly padded with zeros or spaces, you can effectively do the sort as:

order by length(cm.row_id), cm.row_id

Edit:

Your values are not numbers. If you know the maximum depth, you can use regexp_substr():

order by cast(regexp_substr(col, '[^.]+', 1, 1) as number),
         cast(regexp_substr(col, '[^.]+', 1, 2) as number),
         cast(regexp_substr(col, '[^.]+', 1, 3) as number),
         cast(regexp_substr(col, '[^.]+', 1, 4) as number)

Here is a rextester. And a working query with your sample data:

select *
from (select '4.6' as col from dual union all
      select '5.2' as col from dual union all
      select '3.1' as col from dual union all
      select '5.3' as col from dual union all
      select '3.5' as col from dual union all
      select '5.4' as col from dual union all
      select '2.11' as col from dual union all
      select '2.12' as col from dual union all
      select '2.13' as col from dual union all
      select '2.14' as col from dual union all
      select '2.15' as col from dual union all
      select '5.5' as col from dual union all
      select '5.6' as col from dual union all
      select '5.7' as col from dual union all
      select '2.17' as col from dual union all
      select '5.8' as col from dual union all
      select '5.9' as col from dual
     ) x
order by cast(regexp_substr(col, '[^.]+', 1, 1) as number),
         cast(regexp_substr(col, '[^.]+', 1, 2) as number),
         cast(regexp_substr(col, '[^.]+', 1, 3) as number),
         cast(regexp_substr(col, '[^.]+', 1, 4) as number)

it's giving me `cast(regexp_substr(CM.ROW_ID, '[^.]+', 1, 1) as number);` invalid number — Murali, Aug 19 '17 at 15:25
That probably means your `row_id` contains values which aren't a digit or a fullstop. — APC, Aug 20 '17 at 10:50
@APC yeah you are correct for the I wrote a query.That query is ignoring the aphabets,etc.. In that there is a problem for me, here 1.1 and 1.10 is same as they are numerics, Hence it is returning rowid's as 1.1 and 1.1 , But I want 1.1 and 1.10 's are differenct.I will post my answer and please Tell me how can I get 1.1 and 1.10 differently. — Murali, Aug 21 '17 at 05:57
@Murali - check my answer, it handles `1.1` and `1.10` as you would expect. — APC, Aug 21 '17 at 06:04

score 2 · Accepted Answer · edited Aug 21 '17 at 11:18

@Murali you can try with this

create table tt (sno varchar2(10));
insert into tt values('1');
insert into tt values('1.1.1');
insert into tt values('1.1.1.2');
insert into tt values('1.1');
insert into tt values('1.2');
insert into tt values('1.3');
insert into tt values('1.4');
insert into tt values('1.5');
insert into tt values('1.6');
insert into tt values('1.7');
insert into tt values('1.8');
insert into tt values('1.9');
insert into tt values('1.10');
insert into tt values('1.11');
insert into tt values('1.12');
insert into tt values('1.13');
insert into tt values('1.14');
----insert into tt values('1.15');
insert into tt values('2');
insert into tt values('2.1');
insert into tt values('2.2');
insert into tt values('2.3');
insert into tt values('2.4');

insert into tt values('1.16');

Then:

select sno
from tt
order by     to_number(regexp_substr(sno, '[[:digit:]]+')),
    to_number(regexp_substr(sno, '[[:digit:]]+', 1, 2)) nulls first,
    to_number(regexp_substr(sno, '[[:digit:]]+', 1, 3)) nulls first

This is basically the same as @gordonlinoff solution except that it only handles three elements, and so doesn't sort `1.1.1.20` and `1.1.1.3` properly. If sorting to a depth of four or more elements didn't matter why did you include them in your sample data??? — APC, Aug 21 '17 at 13:11

Tony Andrews · Answer 4 · 2017-08-21T12:16:04.977

One way to solve this would be using an Oracle type. This might be appropriate if you need to manipulate these strings often. Assuming the individual parts between '.'s only go up to 999, and there are a maximum of 5 then you could create this type:

create or replace  type t_row_id is object 
  ( n1 integer, n2 integer, n3 integer, n4 integer, n5 integer,
    constructor function t_row_id (p_string varchar2) return self as result,
    map member function map return integer
  );
/

create or replace type body t_row_id is

  constructor function t_row_id (p_string varchar2) return self as result 
  is
    -- NOTE: I'm using a handy pre-defined Oracle type and function for
    -- splitting strings.  You could use something else if you prefer.
    l_tab apex_t_varchar2;
  begin
    l_tab := apex_string.split (p_string, '.');
    if l_tab.count >= 1 then
      self.n1 := l_tab(1);
    end if;
    if l_tab.count >= 2 then
      self.n2 := l_tab(2);
    end if;
    if l_tab.count >= 3 then
      self.n3 := l_tab(3);
    end if;
    if l_tab.count >= 4 then
      self.n4 := l_tab(4);
    end if;
    if l_tab.count >= 5 then
      self.n5 := l_tab(5);
    end if;
    return;
  end;

  map member function map return integer
  is
  begin
    return nvl(self.n1,0)*1000000000000 + nvl(self.n2,0)*1000000000
         + nvl(self.n3,0)*1000000 + nvl(self.n4,0)*1000 + nvl(self.n5,0);
  end;
end;
/

Now that can be applied to your data like this to return the correctly sorted results:

select row_id 
  from employee
 order by t_row_id(row_id).map();

Obviously you can amend the type definition to support more or longer parts to the row_id, provided they don't get too long (even then you could change the map function to return a varchar2 and overcome that).

Hi, I am using oracle 11g. it is giving missing or invalid option — Murali, Aug 21 '17 at 10:29
Oh I see - I had copied the code from 12C and it included "editionable" keyword. I've removed that, and also fixed a typo on the last `nvl`. — Tony Andrews, Aug 21 '17 at 12:17

score 0 · Answer 5 · answered Aug 19 '17 at 15:06

0

You want a sorted list. It is not important where the list is sorted, just that it gets sorted. Don't sort it in the database; sort it with java.

Load the list (unsorted) from the database.
Sort the list with Java using a custom Comparator.

answered Aug 19 '17 at 15:06

DwB

37,124
11
56
82

I am already sorting in java `rootItems.stream() .sorted(Comparator.comparing(MyBean::getRowId)) .collect(Collectors.toList());` but it is also giving same output like a string – Murali Aug 19 '17 at 15:10
Pay attention to my answer. Implement a custom comparator that understands the data format. What you did was use an existing comparator (not that existing in java != custom) and to prove to yourself that you need a custom comparator. – DwB Aug 19 '17 at 15:13
2

If it can be sorted in the database - and it can - why not sort it in the database? – APC Aug 20 '17 at 11:47

score 0 · Answer 6 · answered Aug 19 '17 at 15:28

0

You can try this.

order by substr(col,1,1),cast(replace(col,'.','') as number)

answered Aug 19 '17 at 15:28

Vamsi Prabhala

48,685
4
36
58

ORA-00904: "COL": invalid identifier 00904. 00000 - "%s: invalid identifier" *Cause: *Action: Error at Line: 1 Column: 1,075 – Murali Aug 19 '17 at 15:31
change `col` to the appropriate column name. – Vamsi Prabhala Aug 19 '17 at 15:31
substr(CM.ROW_ID,1,1),cast(replace(CM.ROW_ID,'.','') as number) – Murali Aug 19 '17 at 15:33

How to sort a varchar in oracle

6 Answers6