Freelancing Gods 2014

God
10 Oct 2007

Anti-Definite

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.

10 Oct 2007

Link: AS Workshop ยป How to sort MySQL results ignoring definite and indefinite articles (the, a, an)

A not-too-ugly method of sorting while ignoring 'The', 'A', etc

22 Jun 2006

Link: Standardista Table Sorting (A client-side JavaScript Table Sorting module)

RssSubscribe to the RSS feed

About Freelancing Gods

Freelancing Gods is written by , who works on the web as a web developer in Melbourne, Australia, specialising in Ruby on Rails.

In case you're wondering what the likely content here will be about (besides code), keep in mind that Pat is passionate about the internet, music, politics, comedy, bringing people together, and making a difference. And pancakes.

His ego isn't as bad as you may think. Honest.

Here's more than you ever wanted to know.

Ruby on Rails Projects

Other Sites

Creative Commons Logo All original content on this site is available through a Creative Commons by-nc-sa licence.