Using a SQL wildcard ignoring non-alphanumeric characters in a column-CodePudding

I have a two-column table:

product        name
cars           ["bmw", "mazda", "ford", "dodge"]
fruit          ["lemon", "orange", "lime", "apple"]

I'm using a wildcard to search the product's name column. My question is, is there a way to search a column only by alphanumeric characters and ignore the " and [ ]?

For example, if the user searched bmw the query would be: LIKE '%bmw%' and it would return cars, however if the user searches bmw" and the query is: LIKE '%bmw"% or they enter dodge"] and the query would be LIKE '%dodge"]%" it would want it to not return any results.

My current query:

SELECT product, name FROM `test1` WHERE name LIKE '%bmw%'

It doesn't need to be a wildcard basically, I am after the query only providing the product if the exact name is used but because of the format of the name column it's giving me problems.

CodePudding user response：

You might want to clean the data before it's being added to the query.

e.g.You can use regex to replace unwanted characters / only allow certain characters in a text. Then you can add that "cleaned" data as a parameter to that query.

CodePudding user response：

As suggested JSON_SEARCH() is what I needed to achieve my desired outcome.

See: https://database.guide/json_search-find-the-path-to-a-string-in-a-json-document-in-mysql/

Specifically Example 5 – Wildcards.