ORDER BY alphabet first then follow by number

Question

I looking for some tweak in mysql ordering , I normally select record from table and then order the record by Name(varchar) ASC but the number is always come first

here some example of my question (note. mysql sort the record with 0-9 first)

SELECT name FROM list ORDER BY name ASC
record returned:
1 star
2 star
9 slice
Ape
Age
Beg
Bell
Fish
Zoo

What i want is the alphabet order come first then follow by number

Desired output

Ape
Age
Beg
Bell
Fish
Zoo
1 star
2 star
9 slice

I tried to make question simple and neat , please let me know if i should provide more information , cheers ! — Leon Armstrong, Jul 02 '13 at 05:49

score 34 · Accepted Answer · answered Jul 02 '13 at 05:52

34

Use the following ORDER BY clause:

ORDER BY IF(name RLIKE '^[a-z]', 1, 2), name

answered Jul 02 '13 at 05:52

Barmar

741,623
53
500
612

1

`RLIKE` or LIKE **?** didn't get – Grijesh Chauhan Jul 02 '13 at 05:55
1

@GrijeshChauhan R stand for regex – Leon Armstrong Jul 02 '13 at 05:57
1

Wow!! It saved my lot of time. Thank you so much :) – Ravi Dhoriya ツ Jan 29 '15 at 18:13
1

How do you sort descending? Eg. 9,2,1, Zoo,Fish,Bell? – friek108 Oct 12 '18 at 01:34
1

Add `DESC`: `ORDER BY IF(name RLIKE '[a-z]'), 1, 2) DESC, name DESC` – Barmar Oct 12 '18 at 16:07

score 10 · Answer 2 · edited May 23 '17 at 12:26

10

Ref this

SELECT name FROM list ORDER BY name * 1 ASC

Edited

SELECT name FROM list ORDER BY name * 1, name ASC

edited May 23 '17 at 12:26

Community

1
1

answered Jul 02 '13 at 05:55

Salil

46,566
21
122
156

That ignores all the letters. – Barmar Jul 02 '13 at 05:58
@barmar:- yes, agree with you – Salil Jul 02 '13 at 06:17
This worked for me. Barmar's answer for some reason did not – Matthew Zourelias Jun 12 '17 at 18:27

score 0 · Answer 3 · answered Jul 02 '13 at 05:52

0

You can try something like this:

SELECT 
    name 
FROM 
    list 
ORDER BY 
    IF(name REGEXP '^[0-9]', CONCAT('zz',name),name) ASC

So if your name start with a digit you concatenate 'zz' in the beginning (so that it will be last)

answered Jul 02 '13 at 05:52

Stephan

8,000
3
36
42

Won't that make numbers go between `v` and `w`? – Barmar Jul 02 '13 at 05:54
@Barmar your right... i meant to put the last letter of the alphabet but even so it will not solve all the cases like when you have a name : 'zzabc' – Stephan Jul 02 '13 at 05:56
Which is why my solution is best. – Barmar Jul 02 '13 at 05:57
Nice hack too , but i'm working on a large dictionary table , so i'll will stick to @Barmar solution , anyway thanks! – Leon Armstrong Jul 02 '13 at 05:58
yes , we can all agree that the best/cleanest solution was offered by @Barmar – Stephan Jul 02 '13 at 06:01

score 0 · Answer 4 · answered Sep 02 '21 at 12:11

0

This is what worked for me in Rails with a POSTGRESQL DB

.order(Arel.sql("SUBSTRING(LOWER(name), '^[A-Za-z].*'), SUBSTRING(LOWER(name), '^[0-9]+')::FLOAT"))

answered Sep 02 '21 at 12:11

Surbhit Rao

625
6
14

score -2 · Answer 5 · answered Jul 02 '13 at 05:59

-2

Try this..

It simple one to get your answer

SELECT name  from list ORDER BY (name +0) ASC ,name ASC

answered Jul 02 '13 at 05:59

Ganesh Rengarajan

2,006
13
26

ORDER BY alphabet first then follow by number

5 Answers5

Linked