Ordering strings containing numbers

Databases
1

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

2

try

ORDER BY 0+SUBSTRING(productcode FROM 2)

it’s a hack, but it works

3

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?

4

… well more like


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

which works a treat!
:slight_smile:

5

my job here is done

:wink: