Matching the first alphanumeric character blank (The | An?)

Question

I have a list of artists, albums and tracks that I want to sort using the first letter of my name. The problem arises when I want to ignore the words "The", "A", "An" and other various characters other than alphanumeric characters (telling you "Weird Al" Yankovic and [dialog]). Django has a good start to '^ (An? | The) +', but I want to ignore those and a few others of my choice.

I do this in Django using MySQL db with utf8_bin setting.

EDIT

Well, my mistake is not to mention this, but the database I am accessing is pretty much ready. It was created and maintained by Amarok , and I cannot change it without any problem. That The Chemical Brothersbeing said, the artist table has the specified as The Chemical Brothers, so I think I'm stuck here. It will probably be slow, but it is not so much for me as for a personal project.

Answer 1

, , , , . , . , .. .

, . TRIM(LEADING ... FROM ...), , , , .

SELECT *
FROM song
WHERE SUBSTRING(TRIM(LEADING 'The ' FROM TRIM(LEADING 'A ' FROM title)), 1, 1) = 'B'
ORDER BY TRIM(LEADING 'The ' FROM TRIM(LEADING 'A ' FROM title))

:

'The Bar'   -- "The" is ignored when sorting.
'Baz A'    

:

CREATE TABLE song (title NVARCHAR(100) NOT NULL);
INSERT INTO song (title) VALUES
('The Bar'),
('Baz A'),
('Foo'),
('Qux'),
('A Quux');

---

, ORDER BY , , , . , (a, an ..) . , , .

Answer 2

PostgreSQL , :

SELECT title
FROM  albums
ORDER BY    
  CASE 
    WHEN title ~* '^The ' THEN substring(title from 5)
    WHEN title ~* '^An '  THEN substring(title from 4)
    WHEN title ~* '^A '   THEN substring(title from 3)
    ELSE title
  END asc;

, MySQL beasties.