Organizational Research By

Surprising Reserch Topic

sorting string column containing numbers in sql


sorting string column containing numbers in sql  using -'sql,mysql,sql-order-by'

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?
    

asked Sep 15, 2015 by NevaGunnjcf
0 votes
16 views



Related Hot Questions

4 Answers

0 votes

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)
answered Sep 15, 2015 by NQLXJ
0 votes

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 Sep 15, 2015 by EzrStiner
0 votes

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 Sep 15, 2015 by Ben4092
0 votes

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 Sep 15, 2015 by FloQKW

...