ordering strings containing numbers

[mickyginger]

Hello,

Consider, if you will, the following product codes:
BK1
BK1a
BK2
BK3
BK11
BK22

That would be that 'natural' order of things as far as a human is concerned.

MySQL would order them thusly:
BK1
BK11
BK1a
BK2
BK22
BK3

I can understand that logic, but how can I convince MySQL to come round to my way of thinking?

Many thanks,
M

[r937]

try

ORDER BY 0+SUBSTRING(productcode FROM 2)

it's a hack, but it works

[mickyginger]

Hey r937,

Thanks for your reply. Unfortunately, just to make things easier, product codes do not necessarily have 2 letters at the start:

DVD1a
BK2PB
DVD1S
UMW16

are all examples of product ids...

I'm wondering if I should change the table:

CREATE TABLE `catalogue` (
`prefix` VARCHAR(3) NOT NULL,
`suffix` VARCHAR(5) NOT NULL,
...
PRIMARY KEY(`prefix`, `suffix`)

Then the query would something like:

SELECT `prefix`+`suffix` AS `id` FROM `catalogue` ORDER BY `prefix` AND `suffix`

What do you think?

[mickyginger]

... well more like

SELECT CONCAT(`prefix`, `suffix`) AS `id` FROM `catalogue` ORDER BY `prefix`, 0+`suffix`

which works a treat!

[r937]

which works a treat!

my job here is done