1

I'm trying to find a collation in MySQL (my version is 5.0) where strings that differ in case are considered the same but there're no other rules like:

á = a

and so on.

I tried to find the proper collation here: http://www.collation-charts.org/mysql60/by-charset.html but it seems that the collation I'm looking for doesn't exist.

I can't use in SQL query: SELECT ... WHERE lower(column1) = lower(column2) because indices on columns column1 and column2 are not used then and my query is terrible slow.

Thanks for any suggestion!

MartyIX
  • 27,828
  • 29
  • 136
  • 207

4 Answers4

1

I was given an advice: simply have table like this: id, word, word_in_lowercase.. it's true that data are redundant but otherwise it fulfils all my needs.

Automatic update of word_in_lowercase may be done via trigger or some additional programming.

MartyIX
  • 27,828
  • 29
  • 136
  • 207
  • according to http://stackoverflow.com/questions/234591/upper-vs-lower-case/234751#234751, the simple 'lowercase' comparison can be incorrect for some languages – akavel Nov 10 '09 at 11:06
0

Which type of collation set in the tables that in question? I'm currently using a lot of tables with utf8_hungarian_ci because of this one is case-insensitive.

fabrik
  • 14,094
  • 8
  • 55
  • 71
  • I need it for Czech language but it's not important because the only thing I need is that A = a and so on but á is not equal A and so on. – MartyIX Aug 10 '09 at 12:52
0

http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html indicates that nonbinary strings are case insensitive by default. Have you tested to see that it is not working properly without using lower()?

Cahlroisse
  • 536
  • 2
  • 5
  • if the string is nonbinary I need to set a collation and the question is what collation it should be if I don't want the rules like á = a and so on. – MartyIX Aug 10 '09 at 12:55
  • I see your point. I was not aware that this occurred in the default character set. – Cahlroisse Aug 10 '09 at 12:57
0

Why don't you use the full text search functions of MySQL for your search query?

For tasks like yours I am using the MATCH AGAINST function.

Read the Specifications at mysql.com to make it clear - Link

One example:

SELECT * FROM customer WHERE status = 1 AND MATCH (person, city, company, zipcode, tags) AGAINST ('".$searchstring."' IN BOOLEAN MODE)

And this will be executed case insensitive.

ChrisBenyamin
  • 1,718
  • 4
  • 22
  • 39
  • You're welcome. The only thing i have not tried is if mysql "ignores" charactrs with e.g. an acent like "a" and "á". Would be nice if you'll share your experiences. ps. dont forget to mark your accepted answer ;-) – ChrisBenyamin Aug 10 '09 at 13:13