Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Long-time reader, first-time poster here.

I'm trying to figure out how to sort a list of artists for a music app I'm writing.

To help understand the database structure: Rather than having a relational system where each song in the songs table has an artist ID that references a row in the artists table, I simply have a list of songs with the artist's name as a string in a column. I then use GROUP BY artist in a MySQL query to return a list of individual artists.

My app retrieves this data from my server in the form of 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 results with artists with names like &i, +NURSE, and 2007excalibur2007 being sorted before the alphabetical results (such as AcousticBrony, ClaireAnneCarr, d.notive, etc.).

What I need is the artists whose names begin with numbers and symbols returned after the alphabetically-sorted artist list.

The solution can be PHP-based, but I'd prefer the elegance of it being done in the MySQL query.

like image 636
Jacob Pritchett Avatar asked Jul 25 '12 01:07

Jacob Pritchett


People also ask

How do I sort by alphabetical order in MySQL?

Use the ORDER BY clause to sort the result set by one or more columns. Use the ASC option to sort the result set in ascending order and the DESC option to sort the result set in descending order.

How do I sort MySQL results?

The ORDER BY keyword is used to sort the result-set in ascending or descending order. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.

How do I sort by last character in SQL?

SELECT *FROM yourTableName ORDER BY RIGHT(yourColumnName,3) yourSortingOrder; Just replace the 'yourSortingOrder' to ASC or DESC to set the ascending or descending order respectively. Here is the query to order by last 3 chars. Case 1 − Get the result in ascending order.


2 Answers

You can add an extra ORDER BY clause that puts the items that start with a non-alphabetic character last, like so:

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

This should move every artist that doesn't start with A-Z or a-z to the end of your ordering.

like image 27
O. Jones Avatar answered Sep 25 '22 14:09

O. Jones


This will put all the artists who's names begin with a letter in a-z before those that don't:

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

See it working online: sqlfiddle


But you might prefer to store a second column with the 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

The values for simplified_name cannot be easily generated in MySQL, so you may want to use a general purpose programming language to pull out all the artists, transform them to simplified names, then populate the database with the results.

Once this is done, you can use this query:

SELECT DISTINCT artist
FROM artists
ORDER BY simplified_name
like image 97
Mark Byers Avatar answered Sep 25 '22 14:09

Mark Byers