Re: SQL question - Sorting by release/version numbers.

It's not real pretty but it works (mostly). The problem will be with the betas. Anyway, this may give you some ideas.

CREATE TABLE mytable(version_info VARCHAR2(30));

INSERT INTO mytable VALUES ('7.1.7.6');
INSERT INTO mytable VALUES ('7.1.4.6');
INSERT INTO mytable VALUES ('7.1.4.7');
INSERT INTO mytable VALUES ('7.1.4');
INSERT INTO mytable VALUES ('7.1.4.beta1');
INSERT INTO mytable VALUES ('7.1.4.10');

SELECT version_info
  FROM mytable
 ORDER BY
LPAD(SUBSTR(version_info,1,INSTR(version_info||'.....','.',1,1)-1),10,'0'),

LPAD(SUBSTR(version_info,INSTR(version_info||'.','.',1,1)+1,INSTR(version_info||'..','.',1,2)-INSTR(version_info||'.....','.',1,1)-1),10,'0'),

LPAD(SUBSTR(version_info,INSTR(version_info||'..','.',1,2)+1,INSTR(version_info||'...','.',1,3)-INSTR(version_info||'.....','.',1,2)-1),10,'0'),

LPAD(SUBSTR(version_info,INSTR(version_info||'...','.',1,3)+1,INSTR(version_info||'....','.',1,4)-INSTR(version_info||'.....','.',1,3)-1),10,'0'),

LPAD(SUBSTR(version_info,INSTR(version_info||'....','.',1,4)+1,INSTR(version_info||'.....','.',1,5)-INSTR(version_info||'.....','.',1,4)-1),10,'0')

/

VERSION_INFO

7.1.4
7.1.4.6
7.1.4.7
7.1.4.10
7.1.4.beta1
7.1.7.6

6 rows selected.

Reg wrote:

> I have a need to sort by release number where the release is in the
> following time honored style of our industry;
>
> n.n.nn.n... etc.
>
> example 7.3.4 which is less than 7.3.4.1
>
> This isn't JUST for Oracle, I need to do it for other products that have
> similar release numbers. Confident in the assumption that this has been
> done before, I am asking for HELP. Yes, I can add a column for a sort
> key and could load it from an external function of some kind.... am I on
> the wrong track already ?
>
> BTW, there could be non-numerics, e.g. 6.0.38beta.1 or somesuch.
>
> rgds,
>
> Reg
Received on Sat Jul 18 1998 - 11:05:29 CDT