Home / Questions / 282909
Accepted
akko
Asked: 2020-08-01 10:51:40 +0800 CST 2020-08-01 10:51:40 +0800 CST

How to replace and exclude letters from search conditional in MYSQL?

  • 772

I need to query a table productowhere there is a property called nombreto search for a similar name LIKE.

SELECT *
FROM producto
WHERE nombre LIKE '%Dulces%'

But now I need this to make the use of the Sand of indifferent Z, that is, if they search Dulcezfor the results of the word to appear Dulcesand also the results of Dulcez. Likewise, if they search for the word Dulces, the results of Dulcesand should appear Dulcez.

For the same problem, I have to ignore the use of the leta H, that is, if they search, Aorathe results of Ahora.

mysql

2 Answers

  1. what you are trying to do is possible using soundex, look for what you indicate in the following way:

    SELECT * FROM producto WHERE nombre SOUNDS LIKE 'Dulcez' ;

    SELECT * FROM producto WHERE nombre SOUNDS LIKE 'aora' ;
    • 3
  2. Best Answer

    The best way to do it is to leave both, the search string and the fields of the table in a single format, in this case transform all the 's' into 'z' and eliminate all the 'h' from both, for this it is you can use replace()as follows:

    SELECT *
FROM producto
WHERE replace(replace(nombre,'z','s'),'h','') LIKE replace(replace('%Dulces%','z','s'),'h','')

    I hope you find it useful, greetings.

    • 1