Sort SQL ORDER BY query without slashes in front of escaped characters

Question

I am trying to figure out how to sort an SQL query by Last Name. This is working except in the case of where a customers last name has a special character.

For example O'Maley.

If this last name is sorted with a last name of "Omar" for example the order should be:

O'Maley Omar

BUT since I use addslashes() in PHP before adding it into the database the sort is actually happening on O\'Maley and so the sort ends up being.

Omar O'Maley

Of course the \ is removed with stripslashes() before being displayed to the user.

How can I make this properly sort?

Answer 1

Look into using either the REPLACE() or TRANSLATE() sql functions. For example:

ORDER BY REPLACE(REPLACE(last_name, '\\', ''), ''', '')

The above REPLACE() functions will remove the backslashes and single quotes before doing the order by.

ORDER BY TRANSLATE(last_name, '\\'', '')

The above TRANSLATE() function will also remove the backslashes and single quotes. This might be a better format for specifying many more characters to remove from a string.

Note: You don't mention a database, so I'm assuming Oracle. Most databases have equivalent functions.