[SOLVED] Sorting Issue with If Statement and Substring

[jroman00]

I made a movie database for my local library and ran into an issue with sorting the movies.  The librarians don't want to include the word "The" when sorting the dvds by name (i.e. "The Bank Job" should be sorted as starting with "B" and displayed as "Bank Job, The").  I don't have a problem changing its appearance, however when I try to sort, an issue arises.

 

My MySQL Statement:

SELECT *,

IF (LEFT( title, 4 ) = 'The ', CONCAT( SUBSTRING( title FROM 4 ), ", The"), title) AS new_sort

FROM movies

ORDER BY new_sort

 

The result set first lists all movies that have the first word being "The" in proper order and then sorts the rest of the movies (i.e. the ones that don't start with the word "The") in correct order.

 

I have been testing this statement and realized that if I remove the SUBSTRING function, it sorts them correctly.

 

For Example:

SELECT *,

IF (LEFT( title, 4 ) = 'The ', CONCAT( "a", "B"), title) AS new_sort

FROM movies

ORDER BY new_sort

 

Yields the correct result set with all the movies up to "A.I. Artificial Intelligence" being listed followed by all the movies with the new name "aB," and then finally"ABC Pronunciary: American English Pronunciation Dictionary."

 

My question is, does the SUBSTRING function do something to the strings causing them to sort differently (e.g. change the collation from say UTF8 to ASCII)?

[jroman00]

Never mind. I figured out what I was doing wrong.  I was right that it had to do with the SUBSTRING function but I was wrong about the root cause of the problem.

 

To figure it out, I checked the ASCII code for the first letter and it was returning 32 (ASCII code for space).  I realized I wasn't getting the correct substring.  The SUBSTRING function should be SUBSTRING(title FROM 5) not SUBSTRING(title FROM 4), giving me:

 

SELECT *,
IF (LEFT( title, 4 ) = 'The ', CONCAT( SUBSTRING( title FROM 5 ), ", The"), title) AS new_sort
FROM movies
ORDER BY new_sort

 

Anway, if anyone else is looking to do something like this (i.e. sort titles of movies or books), here is the final code that I used to get rid of the beginning words "A ", "The ", and "An ":

 

SELECT *,
IF (LEFT( title, 4 ) = 'The ', CONCAT( SUBSTRING( title FROM 5 ), ", The"),
IF (LEFT( title, 2 ) = 'A ', CONCAT( SUBSTRING( title FROM 3 ), ", A"),
IF (LEFT( title, 3 ) = 'An ', CONCAT( SUBSTRING( title FROM 4 ), ", An"),
title))) AS new_sort
FROM movies
ORDER BY new_sort