Re: Ignoring special characters in Select search

[Russell Dyer]

You could use the REPLACE() function to strip out all occurences of the single-quote character.

SELECT * FROM Lyrics
WHERE REPLACE(Song, "'", "") = "were going down";

The REPLACE() function will take the value given as the first parameter and strip out the value given for the second one and replace it with the third. In this case, for the second value I've put a single quote between two double-quotes. For the third value here I've given a pair of quotes with nothing in between. So, any single quotes found for the column Song will be replaced with nothing. By the way, this won't change the value in the table.

Russell Dyer

Author of "MySQL in a Nutshell" (O'Reilly 2005).