Numeric sort the values as Strings in MySQL

Question

I'm wondering if there is a way to do a numeric sort when the number is a string. Take a look at example:

The query:

SELECT * FROM t_tables t order by id_string asc

This is the list from DB:

I need to extract it in this way:

But as result I get this:

Community · Accepted Answer · 2017-05-23 12:12:02Z

2

If you know that you always have numeric values in the column you can use the CAST() operator. Also have a look at this question.

SELECT * FROM t_tables t ORDER BY CAST(id_string AS UNSIGNED) ASC

CommunityBot

11 silver badge

answered Oct 17, 2011 at 10:59

Marcus

12.6k5 gold badges50 silver badges66 bronze badges

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

Comments

DaveyBoy · Accepted Answer · 2011-10-17 11:02:20Z

2

SELECT * FROM t_tables t order by cast(id_string as signed integer) asc;

answered Oct 17, 2011 at 11:02

DaveyBoy

2,9162 gold badges19 silver badges30 bronze badges

ravnur · Accepted Answer · 2011-10-17 11:01:19Z

1

Hope it helps:

SELECT * FROM t_tables t order by cast(id_string as unsigned)

answered Oct 17, 2011 at 11:01

ravnur

2,85223 silver badges28 bronze badges