CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Riduidel
Riduidel

Reputation: 22292

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?

Upvotes: 0

Views: 119

Answers (2)

Riduidel
Riduidel

Reputation: 22292

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

Upvotes: 1

Erhard Dinhobl
Erhard Dinhobl

Reputation: 1826

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.

Upvotes: 0

Related Questions