1

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?

Todd
  • 11
  • 1

1 Answers1

0

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.

John Fowler
  • 3,173
  • 3
  • 18
  • 31