# dotancohen@xxxxxxxxx / 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
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
