jokesonhiltionhotel jokesonhiltionhotel - 6 months ago 10
SQL Question

Search within Mysql with special chars

If I have a table

files
and it has a column
title
, and some of the titles are in this format:

google: and facebook
stack: overflow


Now I'm trying to add search functionality in my app, which executes a
LIKE '%word%'
query. But if people search
google and facebook
it doesn't find anything, unless they specifically search for
google: and facebook
.
I know what
LIKE
does and why it doesn't give the results I'm looking for, I'm just asking if there's a way to search in mysql table and ignoring special chars like
: ' - . , "
etc.

Thanks.

Answer

Use REPLACE function prior to comparison.

... REPLACE( fieldname, ':', '') LIKE %word%