This comes up a lot, and I can see it's come up on StackOverflow for XSLT, Ruby and Drupal but I don't see it specifically for SQL.
So the question is, how do you sort titles correctly when they begin with "The", "开发者_如何学Go;A", or "An"?
One way is simply to TRIM() those strings:
ORDER BY TRIM(
LEADING 'a ' FROM
TRIM(
LEADING 'an ' FROM
TRIM(
LEADING 'the ' FROM LOWER( title )
)
)
)
which was suggested on AskMeFi a while back (does it need that LOWER()
function?).
I know I've also seen some kind of Case/Switch implementation of this but it's a little hard to Google for.
Obviously there are a number of possible solutions. What would be good is SQL gurus weighing in on which have performance implications.
One approach I've seen was to have two columns - one for display and the other for sorting:
description | sort_desc
----------------------------
The the | the, The
A test | test, A
I, Robot | i, Robot
I haven't done any real world testing, but this has the benefit of being able to use an index and doesn't require string manipulation every time you want to order by the description. Unless your database supports materialized views (which MySQL doesn't), implementing the logic as a computed column in a view wouldn't provide any benefit because you can't index the computed column.
I've been using this for years, but can't remember where I found it:
SELECT
CASE
WHEN SUBSTRING_INDEX(Title, ' ', 1) IN ('a', 'an', 'the')
THEN CONCAT( SUBSTRING( Title, INSTR(Title, ' ') + 1 ), ', ', SUBSTRING_INDEX(Title, ' ', 1) )
ELSE Title
END AS TitleSort,
Title AS OriginalTitle
FROM yourtable
ORDER BY TitleSort
Yielding:
TitleSort | OriginalTitle
------------------------------------------------------
All About Everything | All About Everything
Beginning Of The End, The | The Beginning Of The End
Interesting Story, An | An Interesting Story
Very Long Story, A | A Very Long Story
For Postgres specifically, you can use regexp_replace to do the work for you:
BEGIN;
CREATE TEMPORARY TABLE book (name VARCHAR NOT NULL) ON COMMIT DROP;
INSERT INTO book (name) VALUES ('The Hitchhiker’s Guide to the Galaxy');
INSERT INTO book (name) VALUES ('The Restaurant at the End of the Universe');
INSERT INTO book (name) VALUES ('Life, the Universe and Everything');
INSERT INTO book (name) VALUES ('So Long, and Thanks for All the Fish');
INSERT INTO book (name) VALUES ('Mostly Harmless');
INSERT INTO book (name) VALUES ('A book by Douglas Adams');
INSERT INTO book (name) VALUES ('Another book by Douglas Adams');
INSERT INTO book (name) VALUES ('An omnibus of books by Douglas Adams');
SELECT name FROM book ORDER BY name;
SELECT name, regexp_replace(lower(name), '^(an?|the) (.*)$', '\2, \1') FROM book ORDER BY 2;
SELECT name FROM book ORDER BY regexp_replace(lower(name), '^(an?|the) (.*)$', '\2, \1');
COMMIT;
I can only speak for SQL Server: you use LTRIM within CASE statements. No LOWER function is needed because selections are not case sensitive by default. However, if you want to ignore articles then I would suggest you use a noise word dictionary and set up a full text indexing catalog. I am unsure if other implementations are SQL support this.
LOWER
is needed. While SELECT
is not case-sensitive, ORDER BY
is.
Try the following:
ORDER BY replace(replace(replace(YOURCOLUMN,'THE',''),'a\'',''),'an','')
Not tested!
精彩评论