Re: Ignoring special characters in Select search
Actually, if you think that there may be occurences of other charcters, you could also use the logical operator OR like this:
SELECT * FROM Lyrics
WHERE REPLACE(Song, "'", "") = "were going down"
OR REPLACE(Song, '"', "") = "were going down";
The second REPLACE() removes all occurences of a double-quote. Incidentally, another method would be to put the special character in single-quotes and put a back-slash in front of it to escape it (e.g., '\'').
I can't imagine that your text would have any other odd characters in the middle. However, if you think the names of songs might end with a punctuation mark like a period or something, you could use a regular expression like this:
SELECT * FROM Lyrics
WHERE REPLACE(col1, '\'', '') REGEXP 'were going down[.!?]*'
OR REPLACE(col1, '\"', '') REGEXP 'were going down[.!?]*';
You would put all possible characters between square-brackets like you see here (i.e., a period, an exclamation point, and a question-mark), followed by an asterisk. The asterisk indicates zero or more of any of the characters contained in the brackets. You could just put LIKE 'were going down%', but the wild card % would allow for letters and would not limit it to one character only. You could also use the LIKE with the wild card ? to limit it to one character, but that would allow for a letter in addition to punctuation marks.
Russell Dyer
Author of "MySQL in a Nutshell" (O'Reilly 2005).