Re: Sorting MySQL queries

# dotancohen@gmail.com / 2006-11-05 15:13:19 +0200: > I have a list of subjects, such as "Linux", "Open Source", and "the > World Wide Web". The subjects are stored in a database and being > retrieved via php. I currently organize them alphabetically with SQL's > ORDER BY ASC argument, however, if there is a preceding "the " or "a " > then that is considered as part of the alphabetical order. Thus, all > the subjects starting with "the " are grouped together, as are the > subjects starting with "a ". How can I order by ascending, without > taking the preceding "the " or "a " into account? > Current code: > $query = "SELECT subject FROM table ORDER BY subject asc"; > $result = mysql_query($query); 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)); SELECT subject FROM table ORDER BY fn(subject) ASC; -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991