Match similar special character in filters

collationoraclestring-searching

I have to perform a search on a table with Turkish city names.

The users would enter the first 4 characters of a city and I need to fetch the results from SQL.

For example, user wants to find the entries for Istanbul, so he would enter ista or Ista or ISTA

Problem is that Istanbul is written with special character. It is Ìstanbùl. So my query

where UPPER(city) like 'UPPER(<entered value>)%'

will not find it. It only finds it when the user enters Ìsta (with the special character).

Is there a way of converting similar special character to 1 character. So for example ìíî will be converted to i. This way a user can find Ìnstanbùl with whatever variant of i they enter.

Best Answer

The replace function will find a character in a string and replace it. Use: REPLACE(str, find_string, replace_with)

in your case: where UPPER(REPLACE(city, 'Ì', 'I') like 'UPPER()%'

Hope this helps

http://www.w3resource.com/mysql/string-functions/mysql-replace-function.php

Related Question