Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

MySQL alternative for Oracle select query

MaahjoorMay 20 2015 — edited May 20 2015

Dear all,

i need the alternative for the following query which i issue against Oracle Database 10g.


SELECT * FROM num
    ORDER BY to_number(regexp_substr(a, '[^.]+', 1, 1)) NULLS FIRST,
                to_number(regexp_substr(a, '[^.]+', 1, 2)) NULLS FIRST,
              to_number(regexp_substr(a, '[^.]+', 1, 3)) NULLS FIRST;


MySQL doesnt support to_number function. how to change this query to mysql query?


my tables structure is below


create table num (a char);

insert into num values ('1');

insert into num values('1.1');

insert into num values('1.2');

insert into num values('1.3');

insert into num values('1.4');

insert into num values('1.5');

insert into num values('1.6');

insert into num values('1.7');

insert into num values('1.8');

insert into num values('1.9');

insert into num values('1.10');

insert into num values('2');

insert into num values('2.1');

commit;

THank you

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 17 2015
Added on May 20 2015
2 comments
492 views