On 13 Dec, 11:36, "Luuk" <l...@invalid.lan> wrote:
> "Captain Paralytic" <paul_laut...@yahoo.com> schreef in berichtnews:c7c65ef2-ddb4-4a8a-9c1a-5ca44f50e805@s8g2000prg.googlegroups.com...
>
>
>
> > On 13 Dec, 11:12, "Luuk" <l...@invalid.lan> wrote:
> >> "Captain Paralytic" <paul_laut...@yahoo.com> schreef in
> >> berichtnews:b40fb92a-83ae-486e-841e-53f6645ab3dd@t1g2000pra.googlegroups.com...
>
> >> > On 13 Dec, 08:21, "pzal...@gmail.com" <pzal...@gmail.com> wrote:
> >> >> Hi!
> >> >> I have a query/view that is part of a larger program where I want to
> >> >> compare 2 version numbers to determine which is the "higher" version.
> >> >> Examples would be
> >> >> 4.2.5a.sarge9
> >> >> 4.2.5a.sarge10
>
> >> >> in most situations the < > symbols work great from mysql because from
> >> >> sarge1 to sarge9 the alphanumeric comparison works fine. However, as
> >> >> the example above shows, i need something that will indicate that
> >> >> sarge10 is actually a higher version than sarge9 except because the
> >> >> alphanumeric comparison sees 9 vs 1 it puts the 9 as the higher
> >> >> version. Can anyone suggest a painless way to achieve my goal?
>
> >> >> thanks!
> >> >> Pat
>
> >> > If you can install the REGEX extensions, you could remove all the non-
> >> > numeric characters and then do the comparison.
>
> >> but:
> >> 4259 will still be before 42510
> >> or, am i missing something?
>
> > The OP wants 9 to come before 10 (10 is generally taken to be larger/
> > newer than 9)
>
> hmmm, must have been busy typing
> SELECT version REGEXP '[0-9]' from test;
> and seeing only 0's and 1's...
>
> this is confusing to me, and i don't see how i can remove non-numeric
> characters....

You can't with MySQL's standard REGEXP operator.
That is why I said "f you can install the REGEX extensions..."

http://groups.google.co.uk/group/com...db5991ecf7b9fd