SEO

#1 (**permalink**) 02-28-2008, 10:30 AM

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

#2 (**permalink**) 02-28-2008, 10:30 AM

On Dec 13, 8:21 am, "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

This might work:

SELECT *
FROM `versions`
ORDER BY version +0

#3 (**permalink**) 02-28-2008, 10:30 AM

"strawberry" <zac.carey@gmail.com> schreef in bericht
news:5b2f7a09-6681-4bc9-a7e4-7b7178e22b02@b1g2000pra.googlegroups.com...
> On Dec 13, 8:21 am, "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
>
> This might work:
>
> SELECT *
> FROM `versions`
> ORDER BY version +0

i think
SELECT *
FROM `versions`
ORDER BY version +length(version)
will have more success...

but will also not be correct.... ;-(

#4 (**permalink**) 02-28-2008, 10:30 AM

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.

#5 (**permalink**) 02-28-2008, 10:30 AM

"Captain Paralytic" <paul_lautman@yahoo.com> schreef in bericht
news: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?

#6 (**permalink**) 02-28-2008, 10:30 AM

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)

#7 (**permalink**) 02-28-2008, 10:30 AM

"Captain Paralytic" <paul_lautman@yahoo.com> schreef in bericht
news: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....

#8 (**permalink**) 02-28-2008, 10:30 AM

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

#9 (**permalink**) 02-28-2008, 10:30 AM

On Dec 13, 6:52 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
> 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...e_frm/thre...- Hide quoted text -
>
> - Show quoted text -

Would there be a way to install the regex module without recompiling
mysql? The company uses debian packages and generally prepers to stick
to standard package installations rather than customized ones... can
the library be installed separately?

(thanks for all your ideas everyone)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Greater than/Less than with numeric strings