When you are required to sort your list alphabetically like listed below
(Note: 'The' is leading some title but not considered in sorting)
Dual Alibi
Duane Hopwood
Dubarry
Dubeat-E-O
The Duchess and the Dirtwater Fox
The Duchess of Duke Street
The Duchess of Idaho
Duck Season
Duck Soup
DuckTales the Movie: Treasure of the Lost Lamp
The Dude Bandit
Dude Ranger
Use the following query to generate results as sorted like above
SELECT m.title,
CASE WHEN SUBSTRING( m.title, 1, 4 ) = 'The '
THEN SUBSTRING( m.title, 5, LENGTH( title ) -4 )
WHEN SUBSTRING( m.title, 1, 3 ) = 'An '
THEN SUBSTRING( m.title, 4, LENGTH( title ) -3 )
WHEN SUBSTRING( m.title, 1, 2 ) = 'A '
THEN SUBSTRING( m.title, 3, LENGTH( title ) -2 )
ELSE title
END AS TempTitle
FROM movies m
WHERE < your logic >
ORDER BY TempTitle
The above query is resulting you with leading articles in the title field, and
without leading articles (The,An,A) in the field TempTitle so you can order by Asc or Desc your TempTitle and show the Title
I hope It will be solve your issue when sorting needed like this way.
(Thanks to our Tech Mr.Varadha)
