CODEWITHSUNDEEP
mysql
Nathan Robb
Nathan Robb

Reputation: 193

MySQL Varchar Sorting as Int

Here is my problem. I have a table such as the following:

+-----------+--------+
| data      | number | 
+-----------+--------+
| Something | NULL   |
| Test      | 1      |
| Another   | 5      |
| Testy     | 22     |
| Tester    | NULL   |
| Test2     | 3      |
+-----------+--------+

The Number field is of type VARCHAR, and I need to sort by this column. I can't do a blanket CAST because I need to preserve the NULL values; I don't want to turn them into 0.

Here is what I've tried so far:

IF ( number != NULL, CAST( number AS UNSIGNED ), number ) as int_number

+

ORDER BY int_number

But this doesn't work, I presume because CAST can only effect a column as a whole, not each value on an individual basis.

I can't convert this column to an INT field for reasons that I can't explain here.

Please test any answers you come up with. The answer I mark correct won't be the first answer I see unless it has already been tested and works.

Upvotes: 0

Views: 64

Answers (3)

Bernd Buffen
Bernd Buffen

Reputation: 15057

use this

IF ( number  IS NOT NULL, CAST( number AS UNSIGNED ), NULL ) AS int_number

Note:

NULL values cannot be used with most comparison operators. For example, =, >, >=, <=, <, or != cannot be used, as any comparison with a NULL always returns a NULL value, never true (1) or false(0)

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 176244

NULL should appear before all numbers

One easy way to achieve it is just use CAST:

SELECT * FROM tab ORDER BY CAST(number AS UNSIGNED)

SqlFiddleDemo

Output:

╔════════════╦════════╗
║   data     ║ number ║
╠════════════╬════════╣
║ Something  ║ (null) ║
║ Tester     ║ (null) ║
║ Test       ║ 1      ║
║ Test2      ║ 3      ║
║ Another    ║ 5      ║
║ Testy      ║ 22     ║
╚════════════╩════════╝

You should consider changing datatype to INT if it is possible.

Another way is to use implicit conversion as Abhik Chakraborty proposed in comment:

SELECT * FROM tab ORDER BY number + 0

Upvotes: 1

Shadow
Shadow

Reputation: 34294

CAST() and CONVERT() functions do not convert nulls to 0, you need to use IFNULL() or COALESCE() for that. So, you can do an order by cast(fieldname as signed integer) to leave null values intact.

Upvotes: 1

Related Questions