Order by

foo21 comes before foo1 - does it have to be this way?

[lorax]

ooops error in the title of this post but you get the idea...

I've got a MySQL db with varchar field that has product titles like foo1 - foo100. I want to order the products by the number immediately after the 'foo' part of the product name.

At this point I've stripped the 'foo' part off of the string in my ORDER BY clause like so:

ORDER BY SUBSTRING(product,3)

Now I'm been trying to find a function with in MySQL that will let me convert the numeric portion of the sting into an integer so I can sort it. Does it exist? And no, I don't want to have to add another column to sort on. ;)

[john_k]

Try CAST or CONVERT. These are used in MS SQL Server, and at least one of them (maybe even both!) should be standard SQL.

[lorax]

I was looking at those but the manual references character encoding so I'm not sure what the syntax would be.

[john_k]

ORDER BY CAST(SUBSTRING(product,3) AS int)

The mySql syntax may vary. Essentially you just need to substitute SUBSTRING(product,3) for the expression placeholder in your manual.

[lorax]

I thought that might be too easy - the server didn't like that at all.

[Timotheos]

I've never used this but the manual says:

The type can be one of the following values:
BINARY
CHAR
DATE
DATETIME
SIGNED [INTEGER]
TIME
UNSIGNED [INTEGER]

So I'm thinking you need to use UNSIGNED rather then INT.

[coopster]

I've often wished there was a natural case sort feature in MySQL. There are some ways you can workaround this...CAST [dev.mysql.com] has already been mentioned, but probably not available on your version of MySQL (MySQL >= 4.0.2). A quick and easy workaround prior to that version is to multiply by 1.

ORDER BY SUBSTRING(product,3) * 1;

Relative thread:

Outing numbers in Order? [webmasterworld.com]