0

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.

2
  • I'm not sure if the special character would count as an "accent", but you could check the collation on your database, and see if it's accent-insensitive. This would not convert values to something else, but, would treat them (in terms of searches and character matching) as identical; just like a case-insensitive collation would allow a search for 'I%' to match both Indiana and ice cream.
    – RDFozz
    Apr 5 '17 at 14:46
  • I found the answer to my problem stackoverflow.com/questions/35689157/… using the nlssort function
    – roel
    Apr 6 '17 at 7:28
0

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

2
  • That would mean, I have to know and replace every special character that could be used. I 'm hoping there is a more simpler/easier solution.
    – roel
    Apr 5 '17 at 12:33
  • If you query the database with a hardcoded value including a special character do you get the same result as a user-entered one? Apr 5 '17 at 13:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.