Geek asks and answers

How to sort MySQL results with letters first, last characters?

The long-awaited reader, the first poster is here.

I am trying to figure out how to sort the artist list for the music application that I am writing.

To understand the structure of the database: instead of having a relational system in which each song in the song table has an artist identifier that refers to a line in the artist table, I just have a list of songs with the artist name as a line in the column. Then I use GROUP BY artistMySQL in the query to return a list of individual artists.

My application retrieves this data from my server as a JSON encoded array, which is the result of the following MySQL query:

SELECT artist FROM songs GROUP BY artist ORDER BY artist ASC

However, this query causes artists with names like & i, + NURSE and 2007excalibur2007 to sort before alphabetical results (such as AcousticBrony, ClaireAnneCarr, d.notive, etc.).

I need artists whose names begin with numbers and characters returned after a list sorted in alphabetical order.

The solution may be based on PHP, but I would prefer that its elegance be executed in a MySQL query.

+5
source share
4 answers

This will put all artists whose names begin with a letter in az to those who do not:

SELECT DISTINCT artist
FROM songs
ORDER BY artist REGEXP '^[a-z]' DESC, artist

See how it works on the web: sqlfiddle

But you may prefer to keep the second column with a simplified name so that you can put them in an order that makes more sense:

artists

artist            | simplified_name
------------------------------------
&i                | i
+NURSE            | nurse
2007excalibur2007 | excalibur

simplified_name MySQL, , , , .

, :

SELECT DISTINCT artist
FROM artists
ORDER BY simplified_name
+11

ORDER BY, , , :

    SELECT artist
      FROM songs
  ORDER BY artist REGEXP '^[^A-Za-z]' ASC, artist

, A-Z a-z .

+2
ORDER BY ASCII(SUBSTR(artist, 1, 1)) NOT BETWEEN 65 AND 122, artist

, , , .

Note that due to the way ascii [\] and _ `will be considered alphabetical. If that matters, you can split it into two Boolean expressions to make uppercase and lowercase letters separately.

Or maybe:

ORDER BY ASCII(UPPER(SUBSTR(artist, 1, 1))) NOT BETWEEN 65 AND 90, artist

Remember that this will only work for ascii characters. Letters that are part of other character sets will not be recognized as such.

0
source

if you want to sort by character first

  • ★★★ Symbol ★★★
  • 101 Hair Care
  • Abc
  • Protection

then use below request

ORDER BY artist REGEXP '^[^A-Za-z0-9]' DESC, artist ASC
0
source

More articles:


All Articles