From: Date: March 23 2004 8:31pm
Subject: Re: Sorting with an alpha numeric field.
List-Archive: http://lists.mysql.org/mysql/162354
Message-Id: <20040323193149.GG2492@dan.emsphone.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

In the last episode (Mar 23), Jacque Scott said:
> I have something like A001C, B689, B1001 etc...

Aah.  This question pops up about once a year, usually under the
subject of "Natural Sorting".  There's no builtin function for doing
this, but one solution would be to write a UDF that finds runs of
numeric characters, and zero-pads them out to, say, 5 digits.  Then
using ORDER BY padnumbers(myfield), your three sample values will look
like

A00001C
B00689
B01001

, and mysql will sort them right.

-- 
	Dan Nelson
	dnelson@stripped