Just a quick post to highlight a MySQL function I’ve created that I’m finding to be very useful (perhaps others will as well).
I love it when sorting ignores prefixes of ‘The’ or ‘A’ – definite or indefinite articles, if you want the correct terms. To get this happening within a SQL
SELECT request is a bit tricky, but inspired by someone else’s solution (Ok, so I pretty much copied their code), I’ve created the following function to make it a lot easier:
CREATE FUNCTION ANTIDEFINITE(field VARCHAR(1024)) RETURNS VARCHAR(1024) DETERMINISTIC RETURN CASE WHEN SUBSTRING_INDEX(field, ' ', 1) IN ('a', 'an', 'the') THEN CONCAT(SUBSTRING(field, INSTR(field, ' ') + 1), ', ', SUBSTRING_INDEX(field, ' ', 1)) ELSE field END;
It works in MySQL 5.0 and 5.1 – I’m sure something similar could be concocted for Postgres.
To use, just call the function on whichever fields you want in your order clause, such as:
SELECT * FROM posts ORDER BY ANTIDEFINITE(subject);
Or, via Rails:
Post.find :all, :order => 'ANTIDEFINITE(subject)'
I’m not sold on the function name, though – it’s a bit long, but at least it’s clear. If you’ve got a better suggestion, let me know.