Handle special characters in mysql search e.g. Sigur Rós

Question

My database features names with special characters e.g. Sigur Rós

The problem is that if you search for the name with regular characters e.g. "Sigur Ros", no results will be returned.

What's the best way to handle this? Is there a tool which I can use, or would I have to find and replace these manually with PHP?

Turns out that the database is using html characters - e.g. "Sigur Rós" — fxfuture, Mar 29 '14 at 06:39

score 1 · Accepted Answer · answered Mar 29 '14 at 04:39

1

In your database, if you set the collation on the column that contains this data to utf8_unicode_ci, then the sorting and comparisons will ignore accents.

You can alter the table column definition to allow the collation to be automatic:
```
ALTER TABLE yourTable MODIFY lastName VARCHAR(30) COLLATE utf8_unicode_ci
```
(See http://dev.mysql.com/doc/refman/5.0/en/charset-column.html)
Alternatively, you can specify the collation when querying the table:
```
SELECT * from yourTable WHERE lastName COLLATE utf8_unicode_ci = 'ROS' 
```
(See http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html)

answered Mar 29 '14 at 04:39

Todd Phillips

121
8

Thanks but doesn't work. I checked the database and it's stored as "Sigur Rós" – fxfuture Mar 29 '14 at 06:37
1

HTML entities in the database will limit your options severely. I suggest you write a script to convert the relevant fields to UTF-8 using PHP's html_entity_decode() function. Then make sure that any code which updates the table also calls that function to clean up new data before inserting it in the table. Once you've done that, then the collation settings should work. – Todd Phillips Mar 29 '14 at 22:11
Thanks Todd, works beautifully – fxfuture Apr 05 '14 at 01:36

Handle special characters in mysql search e.g. Sigur Rós

1 Answers1