Riduidel Riduidel - 18 days ago 5
SQL Question

Select according to string ending

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?

Answer

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