ORDER BY using REGEXP in MYSQL

Question

I am fiddling around REGEXP in MySQL, and I was wondering how the ORDER BY command could be used with REGEXP in a situation like e.g. this, where we would normally order by the last 3 chars of a column using ORDER BY RIGHT(COL,3).

I tried this query

SELECT COL FROM TABLE ORDER BY (COL REGEXP "\w{3}$") ASC

but it doesn't work. Can I fix it somehow to get the equivalent of RIGHT(COL,3) ?

Answer 1

col REGEXP '...' return a true/false (actually 1/0) value. To ORDER BY col REGEXP '...'` you get the rows that don't match first (in arbitrary order), followed by the ones that do match. It does not sort by the last 3 characters.

Use ORDER BY RIGHT(col, 3).

Aliasing is a convenience, not a performance issue, here.

If you are looking for the com at the end of a domain, keep in mind that not all are exactly 3 characters. So, instead, use

ORDER BY  SUBSTRING_INDEX(domain, '.', -1)

Answer 2

You can alias a column and then order by the aliased column. Your query would look like:

SELECT COL, (COL REGEXP "\w{3}$") AS MyOrderedCol FROM TABLE ORDER BY MyOrderedCol ASC