MySQL Sort varchar by its integer

Question

How do I sort a column (VARCHAR) like this:

[CSGO] Bot #1 [CSGO] Bot #2 [CSGO] Bot #3 ... [CSGO] Bot #10

My Query results in:

[CSGO] Bot #2 [CSGO] Bot #23 [CSGO] Bot #5 [CSGO] Bot #6

Query:

SELECT bot_id, name, username FROM bots ORDER BY ABS(REPLACE(name, '[CSGO] #', '')) ASC

Without the ABS() and REPLACE(), gives basically the same result.

Looks like your argument in the replace is missing 'Bot'. It looks the the REPLACE function returns an unmodified value of name. — spencer7593
– spencer7593, Commented Jan 20, 2016 at 5:37

Gordon Linoff · Accepted Answer · 2016-01-20 04:04:24Z

3

A simple way to do this assuming the prefixes are the same length:

order by length(name), name

If you just want to go by the portion after the #:

order by substring_index(name, '#', -1) + 0

answered Jan 20, 2016 at 4:04

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

Thanks a ton! Went with order by substring_index(name, '#', -1) + 0

SIDU · Accepted Answer · 2016-01-20 04:09:49Z

SELECT bot_id, name, username FROM bots ORDER BY substring_index(name, '#', 1), substring_index(name, '#', -1) + 0

if your column name always starts with '[CSGO] Bot #', then do this:

SELECT bot_id, name, username FROM bots ORDER BY substr(name, 13) + 0

Madhivanan · Accepted Answer · 2016-01-20 05:37:06Z

0

or

 ORDER BY ABS(REPLACE(name, '[CSGO] Bot #', ''))*1 ASC

answered Jan 20, 2016 at 5:37

13.7k1 gold badge28 silver badges30 bronze badges