mySQL natural sort

[RMorton]

Hi everyone.
I have two tables in the same DB. They both contain alphanumeric codes like "XX123". (one looks up the other) I need to do a natural sort on these in both tables. In one table if I use the trick +0 it works perfectly :

SELECT * FROM  `thistable` ORDER BY  `thiscode` + 0 ASC

However in the other table

SELECT * FROM `product_master_file` ORDER BY `thiscode`+ 0 ASC  

produces no obvious sort at all. However I know the syntax is ok as leaving out the +0 gives the expected result (which is not a natural search)

I can only think there is some difference in the tables? The field in question is a varchar 15 characters in both and the second one has been populated by the first. The only difference I can see is in the one that works NULL=No Default = blank and in the non working one NULL=Yes and Default=Null but I cant see why this would affect it

I don't have much hair but this is causing the remaining to fall out!
your help most appreciated

[Atli]

Hey.

When you say: "XX123", are the X characters actual "X" string characters, or do they represent something else?
If they are just strings, then doing +0 would (should) not yield any usable results, as it would always return 0. (Doing arithmetic calculations on a string, who's first char is not a number, will always return 0.)

Try looking at the actual value it is being sorted on. You should be able to just add it to the SELECT, like:

SELECT *, `thiscode` + 0 AS 'sort_order' ...

[RMorton]

Thanks so much for your insight. Perhaps I can approach solving my problem by asking my question in a differnt way. The field is a VARCHAR. Examples of values are SG1, SGO1, SG147a, SG12 (ie all start with an alpha, but then a mix of alpha/number.

How would you do a natural sort on this? Would, for example ORDER BY CASE('thisfield' AS SIGNED) be the correct approach. Many thanks

[Atli]

MySQL has no built in support for this sort of natural sorting. It sorts strings in a very linear way; one char at a time starting at index 0. - The is the main reason why it is recommended that you don't store codes in such a way, but rather separated into their individual parts.

However, if your code is in a fairly standard format, you can "simulate" natural sorting (up to a point). - For example, if your code always starts with two letters, followed by a number, followed by a mix of alpha-numeric characters, you can sort the strings by the letters and numbers like so:

SELECT * FROM `tbl`
ORDER BY
    LEFT(`code`, 2),
    CAST(SUBSTR(`code`, 3) AS SIGNED);

This is of course very limited. Anything after (and including) the first alpha character after the first numeric character would be ignored.

You may be better of doing this sort of sorting on the front-end or by creating a MySQL function.

The ideal solution would of course be to find a way to separate the values the code is made out of and store them in individual columns. That would effectively eliminate this problem.

[RMorton]

Thank you very much for your time and trouble. You answered my question clearly and concisely and were most helpful, I really appreciate it. Regards