mysql find and replace strings

Question

I have a procedure that searches through the database and returns the results based on the condition passed as an argument. For instance;

CALL myProcs('Björn');

This query will return all users that has 'björn' in their names. However, I'd like to retrieve the same results when the parameter is passed as 'bjorn' (notice the 'ö' and 'o'). If it was only one character then there would be no problem but I'd like to achieve the same thing for :

i => ı, ö => o, ü => u, ë => e etc etc...

What is the easiest approach to achieve this goal? I am using php 5+ and Mysql.

Bogdan · Accepted Answer · 2011-10-09 11:55:05Z

2

You could set a specific collation for your database tables (http://dev.mysql.com/doc/refman/5.0/en/charset-unicode-sets.html) which would allow for correct comparison between ignorable characters. For ex:

Ä = A ; Ö = O ; Ü = U

utf8_general_ci and utf8_unicode_ci both have the desired effect.

Cheers

answered Oct 9, 2011 at 11:55

Bogdan

44.7k12 gold badges130 silver badges130 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Aurelio De Rosa · Accepted Answer · 2011-10-09 11:53:38Z

2

Take a look to this url:

I think this is the better way unless you want to use an array for the replacement.

answered Oct 9, 2011 at 11:53

Aurelio De Rosa

22.2k8 gold badges51 silver badges71 bronze badges

Comments

Siva Charan · Accepted Answer · 2011-10-09 12:04:21Z

2

Basically, while you are reading the parameter values. Use them as "UTF-8" Format.

Refer Tips for Supporting UTF-8 in Your PHP5 Applications

edited Oct 9, 2011 at 12:04

answered Oct 9, 2011 at 11:57

Siva Charan

18.1k10 gold badges64 silver badges95 bronze badges

Collectives™ on Stack Overflow

mysql find and replace strings

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related