Order By Clause

I need to come up with a way of ordering a varchar field of the form
1.x.x.x numerically .
The number of fields after the x may be dynamic (0 or more).
So for instance I may have :
1.0
1.01
1.10
1.101
2.0
2.01
2.2
11.0
20.0

Does anyone have any idea how I might be able to do this?

--

Regards,

*Mark*

mark@love81.freeserve.co.uk
MSN Messengermark_love@hotmail.co.uk

Get Thunderbird


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Order By Clause

Hi,

Mark Love wrote:
> I need to come up with a way of ordering a varchar field of the form
> 1.x.x.x numerically .
> The number of fields after the x may be dynamic (0 or more).
> So for instance I may have :
> 1.0
> 1.01
> 1.10
> 1.101
> 2.0
> 2.01
> 2.2
> 11.0
> 20.0
>
> Does anyone have any idea how I might be able to do this?
>
It does not work as long as you do not right-align the data.

Your problem is that you do not know the length of a VARCHAR field.

Can you change the design to a fixed field length?

Erich

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

RE: Order By Clause

How about something like:

SELECT FORMAT( `fieldname`, 2) AS `sortfield`=20
FROM `tablename` ORDER BY `sortfield` ASC

Obviously, this would drop off any additional decimal points (like in an
IP address.)

http://dev.mysql.com/doc/mysql/en/miscellaneous-functions.ht ml


-Kevin


> -----Original Message-----
> From: Mark Love [mailto:mark@love81.freeserve.co.uk]=20
> Posted At: Thursday, March 17, 2005 3:58 AM
> Posted To: MySQL
> Conversation: Order By Clause
> Subject: Order By Clause
> Importance: Low
>=20
>=20
> I need to come up with a way of ordering a varchar field of the form=20
> 1.x.x.x numerically .
> The number of fields after the x may be dynamic (0 or more).
> So for instance I may have :
> 1.0
> 1.01
> 1.10
> 1.101
> 2.0
> 2.01
> 2.2
> 11.0
> 20.0
>=20

--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org