How to ignore symbols in SQL LIKE

Question

I have a search module with SQL query like this:

SELECT FROM trilers WHERE title '%something%'

And when I search for keyword for example like "spiderman" it returns not found, but when I search for "spider-man" it returns my content (original row in MySQL is "spider-man").

How can I ignore all symbols like -, #, !, : and return content with "spiderman" and "spider-man" keywords at the same time?

Answer 1

What you can do is replace the characters you don't care about before the search takes place.

First iteration would look like this:

SELECT * FROM  trilers WHERE REPLACE(title, '-', '') LIKE '%spiderman%'

This would ignore any '-'.

Next you would rap that with another REPLACE to include '#' like this:

SELECT * FROM  trilers WHERE REPLACE(REPLACE(title, '-', ''), '#', '') LIKE '%spiderman%'

For all 3 ('!','-','#') you would just increase the Replace with another Replace like this:

SELECT * FROM  trilers WHERE REPLACE(REPLACE(REPLACE(title, '-', ''), '#', ''),'!','') LIKE '%spiderman%'

Answer 2

You could try something like

SELECT * FROM trilers WHERE replace(title, '-', '') LIKE '%spiderman%'