Question 21186 in Mysql Question posted in
The official MySQL documentation can be found here.

Using a SQL wildcard ignoring non-alphanumeric characters in a column – Mysql

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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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.


  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search