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
– Martin Smith, Commented Feb 8, 2011 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
– Martin Smith, Commented Feb 8, 2011 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
– Ish, Commented Feb 8, 2011 at 23:27
Alternate solution: SELECT * FROM mytable ORDER BY length(name), name; good on performance :) — Ish
– Ish, Commented Feb 9, 2011 at 18:15

Ish · Accepted Answer · 2012-04-30 14:08:06Z

38

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)

edited Apr 30, 2012 at 14:08

Ish

29.8k11 gold badges62 silver badges78 bronze badges

answered Feb 8, 2011 at 23:08

Brad Christie

102k16 gold badges160 silver badges200 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Brad Christie Over a year ago

@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.

Ish Over a year ago

thanks for more options :), Your original solution served my purpose. And I learned about MySQL's CAST abilities ... cheers

Eric Leschinski · Accepted Answer · 2014-11-03 19:00:45Z

2

You can try this:

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

edited Nov 3, 2014 at 19:00

Eric Leschinski

155k96 gold badges423 silver badges337 bronze badges

answered Dec 19, 2013 at 14:00

tzviki

313 bronze badges

Comments

Jim Brissom · Accepted Answer · 2011-02-08 23:05:15Z

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 8, 2011 at 23:05

Jim Brissom

33.2k4 gold badges41 silver badges33 bronze badges

2 Comments

Ish Over a year ago

got mysql error right syntax to use near 'INTEGER) ASC LIMIT 0, 30

Brad Christie Over a year ago

@Ish: Due to the same thing I forgot about. Have to use SIGNED/UNSIGNED when using CAST/CONVERT.

Andriy M · Accepted Answer · 2011-02-09 07:43:23Z

0

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.

answered Feb 9, 2011 at 7:43

Andriy M

78k18 gold badges100 silver badges157 bronze badges

Comments

Hussain · Accepted Answer · 2013-06-19 13:09:28Z

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, 2013 at 13:09

Hussain

2231 gold badge4 silver badges18 bronze badges

Collectives™ on Stack Overflow

Sorting string column containing numbers in SQL?

5 Answers 5

2 Comments

Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related