Sorting String Column Containing Numbers in SQL

Sorting string column containing numbers in SQL?

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)

Sorting string column containing numbers in MSSQL?

A SQL Server version of above-mentioned MySQL query might look like

SELECT *
  FROM table1 
 ORDER BY LEFT(name, CHARINDEX(' ', name)),
          CAST(RIGHT(name, LEN(name) - CHARINDEX(' ', name) + 1) AS INT)

Here is SQLFiddle demo

We never saw your sample data but if you just have numeric values with leading and/or trailing spaces in that column you can just do

SELECT *
  FROM table1
 ORDER BY CAST(name AS INT)

Here is SQLFiddle demo

How to sort string which contains numbers and alphabets?

The best scenario is creating two extra columns, one for the alphabetic part, one for the numeric part; then it is as simple as ORDER BY alpha_part ASC, num_part ASC. If you have a joint index on those two columns, it will also be very fast.

If you absolutely have to parse the column at query time, that takes time - and also makes indices useless, which makes everything so much slower. But you can do this:

...
ORDER BY
  REGEXP_REPLACE(qt_no, '\d+', '') ASC, 
  CAST(REGEXP_REPLACE(qt_no, '\D+', '') AS INTEGER) ASC

EDIT: I'm very sorry, but I have no idea how to do it on 5.7 except like this:

SELECT qt_no FROM t
ORDER BY
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(qt_no, '0', ''), '1', ''), '2', ''), '3', ''), '4', ''), '5', ''), '6', ''), '7', ''), '8', ''), '9', '') ASC, 
CAST(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(qt_no, 'A', ''), 'B', ''), 'C', ''), 'D', ''), 'E', ''), 'F', ''), 'G', ''), 'H', ''), 'I', ''), 'J', ''), 'K', ''), 'L', ''), 'M', ''), 'N', ''), 'O', ''), 'P', ''), 'Q', ''), 'R', ''), 'S', ''), 'T', ''), 'U', ''), 'V', ''), 'W', ''), 'X', ''), 'Y', ''), 'Z', '') AS UNSIGNED) ASC;

Sort a VARCHAR column in SQL Server that contains numbers?

This should work

select name_subagent
from Subagent
order by CAST(LEFT(name_subagent, PATINDEX('%[^0-9]%', name_subagent + 'a') - 1) as int)

How to sort string with numbers in sql redshift in descending order?

The simplest method for your data is to use len() along with the name:

order by len(name) desc, name desc;

This assumes that the prefix before the numbers is always the same length (and that the numbers are integers that are not zero-padded).

---

Related Topics