Re: Sorting MySQL queries

On 11/5/06, Roman Neuhauser <neuhauser@sigpipe.cz> wrote:

    If you used PostgreSQL I'd suggest a functional index and ordering
    on the function... Does MySQL have anything like this?

    CREATE FUNCTION fn(TEXT)
    RETURNS TEXT
    IMMUTABLE STRICT
    LANGUAGE SQL
    AS $$
      SELECT regexp_replace($1, '^(?:(?:a|the)[[:space:]]+)', '', 'i');
    $$;

    CREATE INDEX ON table(fn(subject));

Thats rather nifty, i dont think you can do that with mysql, but you could achieve something close to that with a trigger for insert/update to apply the regex to a different field that is used for sorting. The other option would make a policy to put ", The" or ", A" at the end of the string a simple function to render the text without the comma at the end would be simple to do. Curt