Sorting string column containing numbers in SQL?

Question

I am trying to sort string column (containing numbers).

// SELECT `name` FROM `mytable` ORDER BY `name` ASC
+----------+
+-- name --+
+----------+
+-- a 1 ---+
+-- a 12 --+
+-- a 2 ---+
+-- a 3 ---+

You see natural sorting algorithm of Mysql is placing a 12 after a 1 (which is ok for most apps), But I have unique needs, so I want result should be sorted like this.

+----------+
+-- name --+
+----------+
+-- a 1 ---+
+-- a 2 ---+
+-- a 3 ---+
+-- a 12 --+

Is it possible with just SQL, or I have to manipulate result-set at application level?

Is the alphabetic prefix always a constant length for all rows? — Martin Smith, Feb 08 '11 at 23:04
So something like `order by left(name,1), cast(SUBSTRING(name,2) as int)` wouldn't work then. Can you provide some more varied data that illustrates your exact needs? — Martin Smith, Feb 08 '11 at 23:08
@Martin MySQL casting doesn't work with `int` have to use `signed` .... But even though your comment is `very precious` ... because you used dual sorting for two substring. — Ish, Feb 08 '11 at 23:27
Alternate solution: `SELECT * FROM mytable ORDER BY length(name), name;` good on performance :) — Ish, Feb 09 '11 at 18:15
See the answer this post: https://stackoverflow.com/questions/11808573/sql-order-string-as-number/11808592#11808592?newreg=21bfcf3195e4430fbd32b9fa495c900b — Bob Brown, Aug 29 '16 at 12:54

score 38 · Accepted Answer · edited Apr 30 '12 at 14:08

Going on the assumption it's always WORD_space_NUMBER this should work:

SELECT   *
FROM     table
ORDER BY CAST(SUBSTRING(column,LOCATE(' ',column)+1) AS SIGNED)

Use POSITION to find the space, SUBSTRING to grab the number after it, and CAST to make it a comparable value.

If there is a different pattern to the column, let me know and I'll try to devise a better work-around.

EDIT Proven to work:

mysql> INSERT INTO t (st) VALUES ('a 1'),('a 12'),('a 6'),('a 11');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t ORDER BY st;
+----+------+
| id | st   |
+----+------+
|  1 | a 1  |
|  4 | a 11 |
|  2 | a 12 |
|  3 | a 6  |
+----+------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM t ORDER BY CAST(SUBSTRING(st,LOCATE(' ',st)+1) AS SIGNED);
+----+------+
| id | st   |
+----+------+
|  1 | a 1  |
|  3 | a 6  |
|  4 | a 11 |
|  2 | a 12 |
+----+------+

mysql> INSERT INTO t (st) VALUES ('b 1'),('b 12'),('b 6'),('b 11');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t ORDER BY CAST(SUBSTRING(st,LOCATE(' ',st)+1) AS SIGNED);
+----+------+
| id | st   |
+----+------+
|  1 | a 1  |
|  5 | b 1  |
|  3 | a 6  |
|  7 | b 6  |
|  4 | a 11 |
|  8 | b 11 |
|  2 | a 12 |
|  6 | b 12 |
+----+------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM t ORDER BY LEFT(st,LOCATE(' ',st)), CAST(SUBSTRING(st,LOCATE(' ',st)+1) AS SIGNED);
+----+------+
| id | st   |
+----+------+
|  1 | a 1  |
|  3 | a 6  |
|  4 | a 11 |
|  2 | a 12 |
|  5 | b 1  |
|  7 | b 6  |
|  8 | b 11 |
|  6 | b 12 |
+----+------+
8 rows in set (0.00 sec)

ignore my lame table/column names, but gives me the correct result. Also went a little further and added double sort to break letters prefix with numeric.

Edit SUBSTRING_INDEX will make it little more readable.

ORDER BY SUBSTRING_INDEX(st, " ", 1) ASC, CAST(SUBSTRING_INDEX(st, " ", -1) AS SIGNED)

@ish: No problem. You can also add a `LEFT(column,LOCATE(' ',column))` in the order by to group it by the letter(s) first, then the numbers. — Brad Christie, Feb 08 '11 at 23:20
thanks for more options :), Your original solution served my purpose. And I learned about MySQL's `CAST` abilities ... cheers — Ish, Feb 08 '11 at 23:23

score 2 · Answer 2 · edited Nov 03 '14 at 19:00

2

You can try this:

ORDER BY CASE
  WHEN ISNUMERIC(column) THEN cast(column as int) 
  else ascii(column[1,1]) 
end

edited Nov 03 '14 at 19:00

Eric Leschinski

146,994
96
417
335

answered Dec 19 '13 at 14:00

tzviki

31
3

score 1 · Answer 3 · answered Feb 08 '11 at 23:05

1

Have a look at the MySQL CAST / Convert functions.

SELECT name FROM mytable ORDER BY CAST(name AS INTEGER) ASC;

Edit: I read:

I am trying to sort string column (containing numbers).

...but just took a look at the result set. Is the a actually also part of the contents? If so, you can use functions like MID to extract only the numeric value and cast that.

But if all rows contain just a without variation, you might as well omit it...

answered Feb 08 '11 at 23:05

Jim Brissom

31,821
4
39
33

1

got mysql error `right syntax to use near 'INTEGER) ASC LIMIT 0, 30` – Ish Feb 08 '11 at 23:10
1

@Ish: Due to the same thing I forgot about. Have to use SIGNED/UNSIGNED when using CAST/CONVERT. – Brad Christie Feb 08 '11 at 23:12

score 0 · Answer 4 · answered Jun 19 '13 at 13:09

0

Here i found another solution with following query using Convert

select * from tablename where columnname like '%a%' order by Convert(smallint,Replace(columnname,'a',''))

answered Jun 19 '13 at 13:09

Hussain

223
1
4
18

score 0 · Answer 5 · answered Feb 09 '11 at 07:43

Another option could be to pad the string with spaces to the left of the number (i.e. add spaces between the word and the number) and use the resulting string for sorting, comething like this:

ORDER BY INSERT(
  column,
  LOCATE(' ', column),
  0,
  SPACES(20 - LENGTH(column) + LOCATE(' ', column))
)

The string is assumed to be of the pattern 'word followed by space(s) followed by number', and the number is assumed to be non-negative (or it would sort incorrectly). The hardcoded 20 is chosen arbitrarily and is supposed to be the string's number part's maximum possible length.

Sorting string column containing numbers in SQL?

5 Answers5

Linked

Related