CODEWITHSUNDEEP
mysqlstring
Tom
Tom

Reputation: 143

MySQL compare string with national characters

I got a small trouble:

SELECT * FROM tableName tn WHERE LOWER(tn.name) = LOWER('place_string_here');

It's ok, when it does a perfect fit:

tn.name = orange_tree

place_string_here = Orange_Tree

But my issue is: national characters, eg.:

Hästgård

I wish "Hästgård" to fit: Hästgård, Hästgard, Hastgård and/or Hastgard

Is there any smart way to do that?

Upvotes: 4

Views: 603

Answers (2)

Tom
Tom

Reputation: 143

I had the latin1_swedish_ci COLLATION on that field, which causes the error. After changing collation to utf8_general_ci (of the "name" field), It started working properly :-)

Upvotes: 0

Justin
Justin

Reputation: 9724

You can use:

Your query:

SELECT * 
FROM tableName tn 
WHERE tn.name COLLATE 'utf8_general_ci' = 'place_string_here'

SQLFIDDLExample

VALUES
('Hästgård', '[email protected]'),
('Twitter', '@sqlfiddle');

Query:

select id, type
from t
where type COLLATE 'utf8_general_ci' = 'hastgard'

Result:

| ID |     TYPE |
-----------------
|  1 | Hästgård |

Upvotes: 2

Related Questions