insanebear insanebear - 14 days ago 6
MySQL Question

MySQL ORDER BY remove non numeric characters

I have old database where some values are inserted like this:

blablabla2008
blablabla2010
blablabla2011


...an some other (newest) values are inserted as numeric:

2013
2014


Is there a way to sort this in sql query?

Answer

If the numbers you care about are the last four characters (as in your examples), then this is easy:

order by right(col, 4)

Otherwise, the problem is much harder because MySQL doesn't offer a way to find or work with character classes. One method is something like this:

order by (case when substring(col, -2, 1) not between '0' and '9'
               then right(col, 1) + 0
               when substring(col, -3, 1) not between '0' and '9'
               then right(col, 2) + 0
               when substring(col, -4, 1) not between '0' and '9'
               then right(col, 3) + 0
               when substring(col, -5, 1) not between '0' and '9'
               then right(col, 4) + 0
               . . .
          end)

That is, check each position for a non-digit character.

Comments