Ordering mixed numeric / alphanumeric list

[Newsgroup_User]

I have a list of "lotnumbers" in a varchar mySQL field
Data is mixed 103, 11, A3, G5, etc

Before the Lettered entries got dropped in the mix,
I was using "ORDER by CAST(lotNumber as SIGNED) "
which put the numeric entries into numeric order (1, 2, 3, etc)
but now that I have the letters in there, this is understandably
screwy... the mixed entries seem to be in no particular order
(D8, B8, C1, etc ) then the numeric entries below those in perfect order as before

the SQL "Order by LotNumber"
gives me the usual 1, 10, 100, 101, 102, etc, with the alpha-combo entries at the end, in order A1, A2, A3, etc... this is as
expected and I have left it in place for now

What is the correct "order by" syntax to get all of the numeric entries listed numerically, with the mixed alphanumeric entries
either before or after those (doesn't matter), in regular alphabetical order?

TIA

--

Michael Evangelista
Evangelista Design
www.mredesign.com
--

[Dan_Bracuk]

The way you did it seems to do what you want, as long as you want the alphas after the numbers.

[paross1]

With SQL Server, you can do something like this.....

SELECT LotNumber,
CASE WHEN ISNUMERIC(LotNumber) = 1
THEN CAST(LotNumber AS int)
ELSE 0 END AS sortval
FROM your_table
ORDER BY 2, 1

But, I can't find a function in MySQL that is equivalent to ISNUMERIC() in SQL Server.....

Phil

[Newsgroup_User]

Hi Phil -
thanks for the reply.
I did a bit of googling on 'mysql isnumeric()'
and the best suggestion seemed to be to use a
regular expression, i.e.
SELECT * FROM myTable WHERE myField REGEXP ('[0-9]')
or something like
=====
SELECT fieldName*1=0 as IsNumeric;
You'll get a 0 for any text, a 1 for any numbers (except if fieldName contains 0); you could watch for that by getting more
complicated:
SELECT if(fieldName = 0, 1, fieldName*1=0) as IsNumeric;
======

This seems somewhat similar to what you initially proposed,
but I am still not sure how to write that into my original query...
any suggestions?


--

Michael Evangelista
Evangelista Design
www.mredesign.com
--



"paross1" <webforumsuser@macromedia.com> wrote in message news:elppi9$qt1$1@forums.macromedia.com...
> With SQL Server, you can do something like this.....
>
> SELECT LotNumber,
> CASE WHEN ISNUMERIC(LotNumber) = 1
> THEN CAST(LotNumber AS int)
> ELSE 0 END AS sortval
> FROM your_table
> ORDER BY 2, 1
>
> But, I can't find a function in MySQL that is equivalent to ISNUMERIC() in SQL
> Server.....
>
> Phil
>

[paross1]

Hmmmmm.... I'm not a MySQL user, so this is a total SWAG. Something like this may allow you to sort your entries with alpha characters first, then the numerics. In other words, if LotNumber contains an alpha (LotNumber*1 = 0) or is not the number 0, then the second column in your query is a 0, otherwise LotNumbr is cast as a number. SInce you are sorting by the 2nd column first, then the forst column, als columns with alphas wil be sorted in alpha order (since the sortval column will be 0, and you sort on that column first), then you will sort on the numeric fields numerically.... at least I think that is how it will work.

SELECT LotNumber,
CASE WHEN (LotNumber*1 = 0 AND LotNumber <> 0)
THEN 0
ELSE CAST(LotNumber AS signed) END AS sortval
FROM your_table
ORDER BY 2, 1;

Phil

[Newsgroup_User]

Hi Phil -
thanks again - looked good to me, but this time, I get the ol'
"you have an error in your SQL near... " generic-type error.

However, I also posted this on Experts-Exchange.com,
and got a great reply - though a bit complex - that is doing exactly what I need.

For reference and possible future use by others, here it is... this is the full reply I got, from a user called 'novadenizen' -
works perfectly.

==============

There's no simple way to do it. MySQL sorts in string-fashion or numerical fashion, not a combination.

Here's a complex way to do it. I'm assuming lotnumbers are either numerical or one capital letter followed by a number, and all
numbers are positive and less than 20 digits.

ORDER BY
IF(lotNumber REGEXP '^[A-Z]',
CONCAT(
LEFT(lotNumber, 1),
LPAD(SUBSTRING(lotNumber, 2), 20, '0')),
CONCAT(
'@',
LPAD(lotNumber, 20, '0')))

This transforms a number like '123' to '@00000000000000000123' and an alphanumeric like 'A22' to 'A00000000000000000022'. These
strings will sort in the fashion you want.

================



--

Michael Evangelista
Evangelista Design
www.mredesign.com
--



"paross1" <webforumsuser@macromedia.com> wrote in message news:elrqhg$9j6$1@forums.macromedia.com...
> Hmmmmm.... I'm not a MySQL user, so this is a total SWAG. Something like this
> may allow you to sort your entries with alpha characters first, then the
> numerics. In other words, if LotNumber contains an alpha (LotNumber*1 = 0) or
> is not the number 0, then the second column in your query is a 0, otherwise
> LotNumbr is cast as a number. SInce you are sorting by the 2nd column first,
> then the forst column, als columns with alphas wil be sorted in alpha order
> (since the sortval column will be 0, and you sort on that column first), then
> you will sort on the numeric fields numerically.... at least I think that is
> how it will work.
>
> SELECT LotNumber,
> CASE WHEN (LotNumber*1 = 0 AND LotNumber <> 0)
> THEN 0
> ELSE CAST(LotNumber AS signed) END AS sortval
> FROM your_table
> ORDER BY 2, 1;
>
> Phil
>