CODEWITHSUNDEEP
mysqlsqlvarchar
Fredrik
Fredrik

Reputation: 3293

MySQL Sort varchar by its integer

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.

Upvotes: 1

Views: 291

Answers (3)

Madhivanan
Madhivanan

Reputation: 13700

or

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

Upvotes: 0

SIDU
SIDU

Reputation: 2278

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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

Upvotes: 3

Related Questions