Ignore parentheses in MYSQL Like query

Question

I have a sample MYSQL table with the following rows:

name
------
Oppo A71 (2018)
Plum Flipper (2018)
Lenovo K5 Note (2018)
Huawei Y9 (2018)
Huawei Y7 (2018)

I want to let the user search through the database. The problem is that in most cases, a user would search for the keyword 2018 without the parentheses like samsung a8 2018 for example instead of samsung a8 (2018).

So for the following query:

SELECT * FROM phones WHERE name LIKE '% 2018 %'

It will return nothing because 2018 is stored in the db with brackets around it.

If I switch the query to:

SELECT * FROM phones WHERE name LIKE '%2018%'

It will display the results correctly but I don't want the query to match the text if it's part of the string or that would return thousands of irrelevant rows. The words must be actually words found not part of words. So I must have spaces around the year.

Is there a way when running LIKE to ignore the brackets so that 2018 for example matches rows with (2018) as word?

Answer 1

One method is regular expressions. But with like, you can replace the parens before matching:

SELECT *
FROM phones
WHERE REPLACE(REPLACE(name, '(', ' '), ')', ' ') LIKE '% 2018 %'