Is it possible to sort varchar data in ascending order that have mix value?

Question

SQL FIDDLE

I have it's table definition :

CREATE TABLE `table2` (
  `id` INT NOT NULL,
  `varchardata` VARCHAR(45) NULL,
  `intdata` INT(10) NULL,
  PRIMARY KEY (`id`));

Data :

INSERT INTO `table2` (`id`, `varchardata`, `intdata`) VALUES ('1', '12', '12');
INSERT INTO `table2` (`id`, `varchardata`, `intdata`) VALUES ('2', 'keyur', '7');
INSERT INTO `table2` (`id`, `varchardata`, `intdata`) VALUES ('3', '3', '3');
INSERT INTO `table2` (`id`, `varchardata`, `intdata`) VALUES ('4', '13', '13');
INSERT INTO `table2` (`id`, `varchardata`, `intdata`) VALUES ('5', '6', '6');

when I am executing query :

SELECT * FROM table2 order by intdata asc;

it gives result :

Is it possible to sort data in some order that it will take it and give o/p as

3
6
12
13
keyur

It's varchar data so I exactly don't know how to do so or is it actually possible or not ? So suggestion please that is it possible ?

Answer 1

Another way of doing it to correctly treat 0 (distinguish it from non-numeric values)

SELECT id, varchardata, intdata
  FROM 
(
  SELECT id, varchardata, intdata, varchardata REGEXP '[0-9]' is_numeric
    FROM table2 
) q
  ORDER BY is_numeric DESC, 1 * varchardata

Output:

| ID | VARCHARDATA | INTDATA |
|----|-------------|---------|
|  3 |           3 |       3 |
|  5 |           6 |       6 |
|  1 |          12 |      12 |
|  4 |          13 |      13 |
|  2 |       keyur |       7 |

Here is SQLFiddle demo

Answer 2

Your intdata contains varchar - there for its an alphanumeric sort

SELECT * 
FROM table2 
ORDER BY CAST(intdata as int) asc;

Answer 3

Try this:

SELECT * 
FROM table2 
ORDER BY IF(CAST(varchardata AS SIGNED) = 0, 99999, CAST(varchardata AS SIGNED));

Check the SQL FIDDLE DEMO

OUTPUT

| ID | VARCHARDATA | INTDATA |
|----|-------------|---------|
|  8 |           3 |       3 |
| 10 |           6 |       6 |
|  6 |          12 |      12 |
|  9 |          13 |      13 |
|  7 |       keyur |       7 |