Home/ Questions/Q 7946
Next
In Process
Question
matheo
  • 0
Explorer
Asked: 2017-07-14T12:56:17+03:00 In:

How to order by number inside of character for a query like ’25 AB’ ,’30 MT’ ?

  • 0

Hi,

I have set of values and I want to order by as follows :

we scan left to right . for example for values like ’25 AB’ ,’30 MT’.

25 comes before 30.

[code]

create table test_table(COL1 varchar2(240));

insert into test_table values (‘50000’);
insert into test_table values (‘AS740TRN’);
insert into test_table values (‘QAS897ZCS’);
insert into test_table values (‘3125′);
insert into test_table values (’50’);
insert into test_table values (‘test’);
insert into test_table values (‘oraask’);
insert into test_table values (’25 AB’);
insert into test_table values(’30 AB’);
insert into test_table values(‘application2’);

[/code]

RESULT EXPECTED
————————-
25 AB
30 AB
50
3125
50000
AS740TRN
QAS897ZCS
application2
oraask
test

thanks in advance.

How toorder byquerysql
Share

    Related Questions

    1 Answer

    1. Junior ahmedshmes21@gmail.com
      This answer was edited.

      Use REGEXP_SUBSTR 

      Syntax :
REGEXP_SUBSTR( string, pattern [, start_position [, nth_appearance [, match_parameter [, sub_expression ] ] ] ] )
code :
SELECT t.*, REGEXP_SUBSTR (COL1, '(d+)') as sort_by_col
  FROM test_table t
Order by sort_by_col ;
Result :
application2
25 AB
30 AB
3125
50
50000
AS740TRN
QAS897ZCS
oraask
test
      • 0

    Leave an answer

    You must login to add an answer.

    Need An Account,