|
Home > Archive > MySQL ODBC Connector > January 2006 > How can I isolate the integer part of a varchar field and use it in an ORDER BY?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
How can I isolate the integer part of a varchar field and use it in an ORDER BY?
|
|
| Nicolas Verhaeghe 2006-01-24, 3:23 am |
| A client of mine sells motorcycle parts and the motorcycle models are
for instance:
YZ85
YZ125
WRF450
YZF450
Etc...
If you know motorcycles, you know that the number is always the
displacement in cc.
What I am looking to do here is make it so that the models are sorted
properly according to their displacement and not their alphanumerical
order.
Currently they are sorted like this:
WRF450
YZ125
YZF450
YZ85
I would like them sorted like this:
YZ85
YZ125
WRF450
YZF450
The displacement is not always at the end, sometimes it's at the
beginning, for instance:
125SX
250EXC
(Yes, those are Yamahas and KTMs, for those who are into that type of
vehicles).
How can I achieve this goal without creating a specific field in the
database?
I tried converting the field to integer, which is something that I can
do with MS SQL (converting a varchar field to integer "extracts" the
integer part, if any) but the CAST and CONVERT are not the same
functions and I have looked for 30 minutes for something that could work
with no success.
Thanks a lot for your help!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Nicolas Verhaeghe 2006-01-24, 9:23 am |
| Oh yeah, I forgot to tell... I still use 3.23. Cannot upgrade for the
moment.
So no SP...
Is this possible at all with 3.23?
Thanks for your help!
-----Original Message-----
From: Gleb Paharenko [mailto:Gleb.Paharenko@ensita.net]
Sent: Tuesday, January 24, 2006 3:41 AM
To: mysql@lists.mysql.com
Subject: Re: How can I isolate the integer part of a varchar field and
use it in an ORDER BY?
Hello.
The "brute force" way is to create and UDF or store function which can
extract the numeric part from the string and ORDER BY the results of
this function. See:
http://dev.mysql.com/doc/refman/5.0...-functions.html
http://dev.mysql.com/doc/refman/5.0...procedures.html
http://dev.mysql.com/doc/refman/5.0...-functions.html
Nicolas Verhaeghe wrote:
> A client of mine sells motorcycle parts and the motorcycle models are
> for instance:
>
> YZ85
> YZ125
> WRF450
> YZF450
>
> Etc...
>
> If you know motorcycles, you know that the number is always the
> displacement in cc.
>
> What I am looking to do here is make it so that the models are sorted
> properly according to their displacement and not their alphanumerical
> order.
>
> Currently they are sorted like this:
> WRF450
> YZ125
> YZF450
> YZ85
>
> I would like them sorted like this:
> YZ85
> YZ125
> WRF450
> YZF450
>
> The displacement is not always at the end, sometimes it's at the
> beginning, for instance: 125SX
> 250EXC
>
> (Yes, those are Yamahas and KTMs, for those who are into that type of
> vehicles).
>
> How can I achieve this goal without creating a specific field in the
> database?
>
> I tried converting the field to integer, which is something that I can
> do with MS SQL (converting a varchar field to integer "extracts" the
> integer part, if any) but the CAST and CONVERT are not the same
> functions and I have looked for 30 minutes for something that could
> work with no success.
>
> Thanks a lot for your help!
>
--
For technical support contracts, goto
https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ Gleb.Paharenko@stripped
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql? unsub...e
cho.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| George Law 2006-01-24, 9:23 am |
| Nicolas, =20
Not sure when the "replace" function was introduced into mysql, but I
think it might do...
Use "replace" in your order by, replacing a-z with null chars, leaving
just your numeric digits, then order by
select * from test;
+-----+-----------+
| uid | name |
+-----+-----------+
| 1 | george099 |
| 2 | george100 |
| 3 | george101 |
| 4 | george001 |
| 5 | 123bill |
| 6 | 100bill |
| 13 | george |
| 14 | darren |
| 15 | joe |
| 16 | bill |
+-----+-----------+
10 rows in set (0.00 sec)
mysql> select uid,name from test order by replace(name,'[a-z]','');
+-----+-----------+
| uid | name |
+-----+-----------+
| 6 | 100bill |
| 5 | 123bill |
| 16 | bill |
| 14 | darren |
| 13 | george |
| 4 | george001 |
| 1 | george099 |
| 2 | george100 |
| 3 | george101 |
| 15 | joe |
+-----+-----------+
You might need to convert 'name' to uppercase to work with all your part
numbers. =20
select uid,name from test order by replace(upper(name),
'[A-Z]','');
--
George
-----Original Message-----
From: Nicolas Verhaeghe & #91;mailto:nicolas@w
hiteecho.com]=20
Sent: Tuesday, January 24, 2006 9:13 AM
To: 'Gleb Paharenko'; mysql@lists.mysql.com
Subject: RE: How can I isolate the integer part of a varchar field and
use it in an ORDER BY?
Oh yeah, I forgot to tell... I still use 3.23. Cannot upgrade for the
moment.
So no SP...
Is this possible at all with 3.23?
Thanks for your help!
-----Original Message-----
From: Gleb Paharenko [mailto:Gleb.Paharenko@ensita.net]
Sent: Tuesday, January 24, 2006 3:41 AM
To: mysql@lists.mysql.com
Subject: Re: How can I isolate the integer part of a varchar field and
use it in an ORDER BY?
Hello.
The "brute force" way is to create and UDF or store function which can
extract the numeric part from the string and ORDER BY the results of
this function. See:
http://dev.mysql.com/doc/refman/5.0...-functions.html
http://dev.mysql.com/doc/refman/5.0...procedures.html
http://dev.mysql.com/doc/refman/5.0...-functions.html
Nicolas Verhaeghe wrote:
> A client of mine sells motorcycle parts and the motorcycle models are=20
> for instance:
>=20
> YZ85
> YZ125
> WRF450
> YZF450
>=20
> Etc...
>=20
> If you know motorcycles, you know that the number is always the=20
> displacement in cc.
>=20
> What I am looking to do here is make it so that the models are sorted=20
> properly according to their displacement and not their alphanumerical=20
> order.
>=20
> Currently they are sorted like this:
> WRF450
> YZ125
> YZF450
> YZ85
>=20
> I would like them sorted like this:
> YZ85
> YZ125
> WRF450
> YZF450
>=20
> The displacement is not always at the end, sometimes it's at the=20
> beginning, for instance: 125SX
> 250EXC
>=20
> (Yes, those are Yamahas and KTMs, for those who are into that type of=20
> vehicles).
>=20
> How can I achieve this goal without creating a specific field in the=20
> database?
>=20
> I tried converting the field to integer, which is something that I can
> do with MS SQL (converting a varchar field to integer "extracts" the=20
> integer part, if any) but the CAST and CONVERT are not the same=20
> functions and I have looked for 30 minutes for something that could=20
> work with no success.
>=20
> Thanks a lot for your help!
>=20
--=20
For technical support contracts, goto
https://order.mysql.com/?ref=3Densita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ Gleb.Paharenko@stripped
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com
--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql? unsub...br />
eecho.com
--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql? unsub...h
ere.net
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Nicolas Verhaeghe 2006-01-24, 11:23 am |
| Thanks, but unfortunately the replace function does not want to work on
a regexp in version 3.23...
I guess I'll have to create a "displacement" field and populate it from
the admin tool.
Thanks for your help. I will upgrade this server as soon as I can.
-----Original Message-----
From: George Law & #91;mailto:glaw@IONO
SPHERE.net]
Sent: Tuesday, January 24, 2006 8:14 AM
To: Nicolas Verhaeghe; mysql@lists.mysql.com
Subject: RE: How can I isolate the integer part of a varchar field and
use it in an ORDER BY?
Nicolas,
Not sure when the "replace" function was introduced into mysql, but I
think it might do...
Use "replace" in your order by, replacing a-z with null chars, leaving
just your numeric digits, then order by
select * from test;
+-----+-----------+
| uid | name |
+-----+-----------+
| 1 | george099 |
| 2 | george100 |
| 3 | george101 |
| 4 | george001 |
| 5 | 123bill |
| 6 | 100bill |
| 13 | george |
| 14 | darren |
| 15 | joe |
| 16 | bill |
+-----+-----------+
10 rows in set (0.00 sec)
mysql> select uid,name from test order by replace(name,'[a-z]','');
+-----+-----------+
| uid | name |
+-----+-----------+
| 6 | 100bill |
| 5 | 123bill |
| 16 | bill |
| 14 | darren |
| 13 | george |
| 4 | george001 |
| 1 | george099 |
| 2 | george100 |
| 3 | george101 |
| 15 | joe |
+-----+-----------+
You might need to convert 'name' to uppercase to work with all your part
numbers.
select uid,name from test order by replace(upper(name),
'[A-Z]','');
--
George
-----Original Message-----
From: Nicolas Verhaeghe & #91;mailto:nicolas@w
hiteecho.com]
Sent: Tuesday, January 24, 2006 9:13 AM
To: 'Gleb Paharenko'; mysql@lists.mysql.com
Subject: RE: How can I isolate the integer part of a varchar field and
use it in an ORDER BY?
Oh yeah, I forgot to tell... I still use 3.23. Cannot upgrade for the
moment.
So no SP...
Is this possible at all with 3.23?
Thanks for your help!
-----Original Message-----
From: Gleb Paharenko [mailto:Gleb.Paharenko@ensita.net]
Sent: Tuesday, January 24, 2006 3:41 AM
To: mysql@lists.mysql.com
Subject: Re: How can I isolate the integer part of a varchar field and
use it in an ORDER BY?
Hello.
The "brute force" way is to create and UDF or store function which can
extract the numeric part from the string and ORDER BY the results of
this function. See:
http://dev.mysql.com/doc/refman/5.0...-functions.html
http://dev.mysql.com/doc/refman/5.0...procedures.html
http://dev.mysql.com/doc/refman/5.0...-functions.html
Nicolas Verhaeghe wrote:
> A client of mine sells motorcycle parts and the motorcycle models are
> for instance:
>
> YZ85
> YZ125
> WRF450
> YZF450
>
> Etc...
>
> If you know motorcycles, you know that the number is always the
> displacement in cc.
>
> What I am looking to do here is make it so that the models are sorted
> properly according to their displacement and not their alphanumerical
> order.
>
> Currently they are sorted like this:
> WRF450
> YZ125
> YZF450
> YZ85
>
> I would like them sorted like this:
> YZ85
> YZ125
> WRF450
> YZF450
>
> The displacement is not always at the end, sometimes it's at the
> beginning, for instance: 125SX
> 250EXC
>
> (Yes, those are Yamahas and KTMs, for those who are into that type of
> vehicles).
>
> How can I achieve this goal without creating a specific field in the
> database?
>
> I tried converting the field to integer, which is something that I can
> do with MS SQL (converting a varchar field to integer "extracts" the
> integer part, if any) but the CAST and CONVERT are not the same
> functions and I have looked for 30 minutes for something that could
> work with no success.
>
> Thanks a lot for your help!
>
--
For technical support contracts, goto
https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ Gleb.Paharenko@stripped
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...e
cho.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...her
e.net
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql? unsub...e
cho.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| George Law 2006-01-24, 1:23 pm |
| Nicolas,
What about just doing your sorting within your code instead of with
mysql?
Depending on how many rows you have that you would need to sort, it
should not be too difficult to build a multidimensional array (add 2
columns, one with the alphabetical part of your key below, the other
with the numeric part), and sort based on these 2 column. =20
--
George
-----Original Message-----
From: Nicolas Verhaeghe & #91;mailto:nicolas@w
hiteecho.com]=20
Sent: Tuesday, January 24, 2006 11:09 AM
To: mysql@lists.mysql.com
Subject: RE: How can I isolate the integer part of a varchar field and
use it in an ORDER BY?
Thanks, but unfortunately the replace function does not want to work on
a regexp in version 3.23...
I guess I'll have to create a "displacement" field and populate it from
the admin tool.
Thanks for your help. I will upgrade this server as soon as I can.
-----Original Message-----
From: George Law & #91;mailto:glaw@IONO
SPHERE.net]
Sent: Tuesday, January 24, 2006 8:14 AM
To: Nicolas Verhaeghe; mysql@lists.mysql.com
Subject: RE: How can I isolate the integer part of a varchar field and
use it in an ORDER BY?
Nicolas, =20
Not sure when the "replace" function was introduced into mysql, but I
think it might do...
Use "replace" in your order by, replacing a-z with null chars, leaving
just your numeric digits, then order by
select * from test;
+-----+-----------+
| uid | name |
+-----+-----------+
| 1 | george099 |
| 2 | george100 |
| 3 | george101 |
| 4 | george001 |
| 5 | 123bill |
| 6 | 100bill |
| 13 | george |
| 14 | darren |
| 15 | joe |
| 16 | bill |
+-----+-----------+
10 rows in set (0.00 sec)
mysql> select uid,name from test order by replace(name,'[a-z]','');
+-----+-----------+
| uid | name |
+-----+-----------+
| 6 | 100bill |
| 5 | 123bill |
| 16 | bill |
| 14 | darren |
| 13 | george |
| 4 | george001 |
| 1 | george099 |
| 2 | george100 |
| 3 | george101 |
| 15 | joe |
+-----+-----------+
You might need to convert 'name' to uppercase to work with all your part
numbers. =20
select uid,name from test order by replace(upper(name),
'[A-Z]','');
--
George
-----Original Message-----
From: Nicolas Verhaeghe & #91;mailto:nicolas@w
hiteecho.com]=20
Sent: Tuesday, January 24, 2006 9:13 AM
To: 'Gleb Paharenko'; mysql@lists.mysql.com
Subject: RE: How can I isolate the integer part of a varchar field and
use it in an ORDER BY?
Oh yeah, I forgot to tell... I still use 3.23. Cannot upgrade for the
moment.
So no SP...
Is this possible at all with 3.23?
Thanks for your help!
-----Original Message-----
From: Gleb Paharenko [mailto:Gleb.Paharenko@ensita.net]
Sent: Tuesday, January 24, 2006 3:41 AM
To: mysql@lists.mysql.com
Subject: Re: How can I isolate the integer part of a varchar field and
use it in an ORDER BY?
Hello.
The "brute force" way is to create and UDF or store function which can
extract the numeric part from the string and ORDER BY the results of
this function. See:
http://dev.mysql.com/doc/refman/5.0...-functions.html
http://dev.mysql.com/doc/refman/5.0...procedures.html
http://dev.mysql.com/doc/refman/5.0...-functions.html
Nicolas Verhaeghe wrote:
> A client of mine sells motorcycle parts and the motorcycle models are
> for instance:
>=20
> YZ85
> YZ125
> WRF450
> YZF450
>=20
> Etc...
>=20
> If you know motorcycles, you know that the number is always the
> displacement in cc.
>=20
> What I am looking to do here is make it so that the models are sorted
> properly according to their displacement and not their alphanumerical=20
> order.
>=20
> Currently they are sorted like this:
> WRF450
> YZ125
> YZF450
> YZ85
>=20
> I would like them sorted like this:
> YZ85
> YZ125
> WRF450
> YZF450
>=20
> The displacement is not always at the end, sometimes it's at the
> beginning, for instance: 125SX
> 250EXC
>=20
> (Yes, those are Yamahas and KTMs, for those who are into that type of
> vehicles).
>=20
> How can I achieve this goal without creating a specific field in the
> database?
>=20
> I tried converting the field to integer, which is something that I can
> do with MS SQL (converting a varchar field to integer "extracts" the
> integer part, if any) but the CAST and CONVERT are not the same=20
> functions and I have looked for 30 minutes for something that could=20
> work with no success.
>=20
> Thanks a lot for your help!
>=20
--=20
For technical support contracts, goto
https://order.mysql.com/?ref=3Densita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ Gleb.Paharenko@stripped
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com
--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: =
http://lists.mysql.com/mysql? unsub...br />
eecho.com
--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...h
ere.net
--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql? unsub...br />
eecho.com
--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql? unsub...h
ere.net
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Nicolas Verhaeghe 2006-01-25, 3:26 am |
| Because I am currently stuck with 3.23 I have just decided to create a
"displacement" field to isolate the number. Besides, some of these bikes
escape from the rules, for instance instead of 600 for 600cc, you only
have a mere "6", "1" stands for 1000.
So all in all the displacement fields will work fine.
-----Original Message-----
From: Michael Stassen [mailto:Michael.Stassen@verizon.net]
Sent: Tuesday, January 24, 2006 10:11 PM
To: George Law
Cc: Nicolas Verhaeghe; mysql@lists.mysql.com
Subject: Re: How can I isolate the integer part of a varchar field and
use it in an ORDER BY?
George Law wrote:
> Nicolas,
>
> Not sure when the "replace" function was introduced into mysql, but I
> think it might do...
REPLACE() exists in 3.23.
> Use "replace" in your order by, replacing a-z with null chars, leaving
> just your numeric digits, then order by
Easier said than done.
> select * from test;
> +-----+-----------+
> | uid | name |
> +-----+-----------+
> | 1 | george099 |
> | 2 | george100 |
> | 3 | george101 |
> | 4 | george001 |
> | 5 | 123bill |
> | 6 | 100bill |
> | 13 | george |
> | 14 | darren |
> | 15 | joe |
> | 16 | bill |
> +-----+-----------+
> 10 rows in set (0.00 sec)
>
> mysql> select uid,name from test order by replace(name,'[a-z]','');
REPLACE doesn't accept patterns in the search string. This REPLACE is
looking for a literal occurrence of the string '[a-z]' to be replaced
with ''.
> +-----+-----------+
> | uid | name |
> +-----+-----------+
> | 6 | 100bill |
> | 5 | 123bill |
> | 16 | bill |
> | 14 | darren |
> | 13 | george |
> | 4 | george001 |
> | 1 | george099 |
> | 2 | george100 |
> | 3 | george101 |
> | 15 | joe |
> +-----+-----------+
Look again. Those are in alphabetical order, not numerical.
> You might need to convert 'name' to uppercase to work with all your
part
> numbers.
>
> select uid,name from test order by replace(upper(name),
'[A-Z]','');
REPLACE is case-sensitive, but this method just won't work.
mysql> SELECT REPLACE('123abcd45',
'[a-z]','');
+---------------------------------+
| REPLACE('123abcd45',
'[a-z]','') |
+---------------------------------+
| 123abcd45 |
+---------------------------------+
1 row in set (0.11 sec)
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| Nicolas Verhaeghe 2006-01-25, 3:26 am |
| You're very helpful, thanks.
Problem already taken care of but thanks for the lesson.
-----Original Message-----
From: Michael Stassen [mailto:Michael.Stassen@verizon.net]
Sent: Tuesday, January 24, 2006 10:55 PM
To: Nicolas Verhaeghe
Cc: mysql@lists.mysql.com
Subject: Re: How can I isolate the integer part of a varchar field and
use it in an ORDER BY?
Nicolas Verhaeghe wrote:
> Thanks, but unfortunately the replace function does not want to work
> on a regexp in version 3.23...
or 4.0, or 4.1, or ...
> I guess I'll have to create a "displacement" field and populate it
> from the admin tool.
Well, that's the right way to go. You're seeing the problem with the
current
scheme. Right now, your displacement is hidden inside the "model
number", so it
is difficult to look up the displacement. That is, the "model number"
contains
the answers to more than one question. That's usually a bad idea. It
probably
ought to be broken into separate columns.
If you make a displacement column, you ought to be able to populate it
using
sql. See below.
> Thanks for your help. I will upgrade this server as soon as I can.
Upgrading is a good idea, but it won't help here.
You can do this in sql, but it's a bit ugly. Here are the keys:
* MySQL will pull out the number if it's at the beginning of the string.
* You can change the beginning of the string with SUBSTRING().
* You can use CASE to handle the different possibilities.
Putting them together gives you something like this:
SELECT model FROM motorcycles
ORDER BY CASE WHEN model > 0 THEN model + 0
WHEN SUBSTRING(model, 2) > 0 THEN SUBSTRING(model, 2) + 0
WHEN SUBSTRING(model, 3) > 0 THEN SUBSTRING(model, 3) + 0
WHEN SUBSTRING(model, 4) > 0 THEN SUBSTRING(model, 4) + 0
END;
+--------+
| model |
+--------+
| YZ85 |
| YZ125 |
| 125SX |
| 250EXC |
| WRF450 |
| YZF450 |
+--------+
6 rows in set (0.00 sec)
The first case handles the strings which start with a number. The
second case
handles the strings which starts with 1 letter before the number. The
third
case handles the string which start with 2 letters before the number.
And so
on. If you can have more than 3 letters before the number, you'll have
to add
the corresponding conditions.
To just add and populate the displacement column, you could
ALTER TABLE motorcycles
ADD displacement INT,
ADD INDEX disp_idx (displacement);
UPDATE motorcycles
SET displacement = CASE WHEN model > 0 THEN model + 0
WHEN SUBSTRING(model, 2) > 0 THEN
SUBSTRING(model, 2)
WHEN SUBSTRING(model, 3) > 0 THEN
SUBSTRING(model, 3)
WHEN SUBSTRING(model, 4) > 0 THEN
SUBSTRING(model, 4)
END;
Then your query becomes simply
SELECT model FROM motorcycles ORDER BY displacement;
Better yet, the index on displacement can be used to speed up the
ordering.
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql? unsub...e
cho.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
|
|
|
|
|