sort numerically according to substring extracted from varchar field

Question

I have a table which contains 2 columns { Names of cities and Info}

Name    Info
Qandahar    {"Population": 237500}
Kabul    {"Population": 1780000}
Herat    {"Population": 186800}
Mazar-e-Sharif    {"Population": 127800}
Amsterdam    {"Population": 731200}
Rotterdam    {"Population": 593321}

and so on. I tried to sort it by Population but this does not work

SELECT Name,Info ORDER BY CAST(SUBSTRING(Info, 16, LENGTH(Info)-16) AS INT) FROM city

but it outputs nothing. I get ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INT) FROM city' at line 1.

try this SELECT Name, CAST(SUBSTRING(Info, 16, LENGTH(Info)-16) AS INT) as info_cast FROM city order by info_cast — nimour pristou, Dec 17 '16 at 10:19
@adikinzor Please [edit] you question to include the error message. — Андрей Беньковский, Dec 17 '16 at 10:33
try this `SELECT Name, CAST(SUBSTRING(Info, 16, LENGTH(Info)-16)) as info_cast FROM city order by info_cast` — nimour pristou, Dec 17 '16 at 10:46
Possible duplicate of [How to order by specific format from a column?](http://stackoverflow.com/questions/41182865/how-to-order-by-specific-format-from-a-column) — Bernd Buffen, Dec 17 '16 at 10:50

score 0 · Accepted Answer · edited May 23 '17 at 12:30

0

You can't cast as INT

These are the following data type you can cast

BINARY[(N)]

CHAR[(N)]

DATE

DATETIME

DECIMAL[(M[,D])]

SIGNED [INTEGER]

TIME

UNSIGNED [INTEGER]

You need to use this:

SELECT 
  Name, 
  Info,
  CAST(SUBSTRING(Info, 16, LENGTH(Info)-16) AS UNSIGNED) as info_cast 
FROM city 
order by info_cast

Answer is taken from: Cast from VARCHAR to INT - MySQL

edited May 23 '17 at 12:30

Community

1
1

answered Dec 17 '16 at 10:49

Mahedi Sabuj

2,894
3
14
27

score 0 · Answer 2 · answered Dec 17 '16 at 12:32

You have deleted the symbols from the left, but you need to delete a } symbol from the right before casting. Like this:

CAST(TRIM(TRAILING '}' FROM SUBSTRING(Info,16,LENGTH(Info)-16)) AS INT)

By the way, why do you use such a table structure? Wouldn't it be better to have an integer column to keep integers?

CREATE TABLE Info ( NAME VARCHAR(20), POPULATION INT)

sort numerically according to substring extracted from varchar field

2 Answers2