Select according to string ending

Question

Hi, In my DB, I store various version numbers, like the following :

OBJNAME
Fix_6.0.0a.1
Fix_6.0.0a.2

I would like to sort them not according to last version element (the number behind the last .character. How do I write such SQL statement ?

I guess it's something like

SELECT SUBSTR(INSTR(OBJNAME, ".", -1)) as LAST_VERSION, OBJNAME from MY_TABLE order by LAST_VERSION

But what is the exact syntax ?

EDIT This code is to be run on Oracle 11g.

Answer 1

i dont know which sql-software you are using, but you should have a look at substr and instr parameters. in you case you are passing 3 parameters to instr and 1 parameter in substr. but substr normally requires more. insert some blanks to get an overview of your statement, especially for the substr.... as LAST_VERSION. then you will see. wrong param count.

Answer 2

The correct version is

select TO_NUMBER(SUBSTR(OBJNAME,INSTR(OBJNAME,'.',-1)+1,LENGTH(OBJNAME))) as LAST_VERSION, OBJNAME from MY_TABLE order by LAST_VERSION