Ethan Allen Ethan Allen - 15 days ago 5
MySQL Question

How do I do natural sorting with a MySQL query where the thing I want sort by starts with a letter?

I have the following MySQL:

SELECT scott, title FROM stampitemdetails WHERE scott REGEXP '^RW[0-9]+$' ORDER BY CAST(scott AS UNSIGNED), scott;


The problem is that this query is bringing back my results ordered like this:

RW1
RW10
RW11
RW2


When they should be like this:

RW1
RW2
RW10
RW11


How do I do natural sorting when my value starts with a character and ends with a number?

What if the length of the
RW
can fluctuate.... meaning it could be
S
, or
REN
, or some other characters of unknown length before the number? Keep that in mind.

By the way, I have the RegEx in the query because in my PHP code the
RW
can be replaced with other characters.

Answer

You should extract the numerical part

  SELECT scott, title 
  FROM stampitemdetails 
  WHERE scott REGEXP '^RW[0-9]+$' 
  ORDER BY substr(scott, 1,2),  CAST(substr(scott, 3, 100)  AS UNSIGNED);
Comments