String search in MySQL query

[srinivembu]

Hi,

I am trying to port my application from SQLite to MySQL database. I believe MySQL by default searches string in case insensitive manner while using '=' and 'like' operators in SELECT statement. I have changed the default collation of the MySQL server to ''latin1_genera l_cs" from "latin1_swedish _ci".

After doing this, the string search is case sensitive for both '=' and 'like' operators. But I would like to search string by case sensitive in '=' operator and case insensitive in 'like' operator (as SQLite does). Any way to make this possible?

Any help would be appreciated.

Regards,
Srini.

[Atli]

Whether a search is case-sensitive or case-insensitive will depend on the collation in use, but it will apply to all methods of searching.
That is, using a collation that ends with "cs" will make all searches case-sensitive, no matter if you use = or LIKE.
A collation that ends with "ci" will make them insensitive.

You could always get around this by using a case-sensitive collation and use the LOWER function to make all strings lower case when searching.

Might cause some performance issues but it should work.

[labmonkey111]

I'm not sure if MySQL supports it, but the ILIKE keyword is a case insensitive LIKE.

[Atli]

Another thing just occurred to me.

You can specify the collation for each search, so you could do something like:
[code=mysql]
SELECT * FROM myTable
WHERE value = 'a' COLLATE latin1_general_ cs
OR value LIKE 'a' COLLATE latin1_general_ ci;
[/code]
That particular query is obviously rather redundant, but you get the point.

The first search would be case-sensitive, while the second one would be case-insensitive.

[Atli]

I'm not sure if MySQL supports it, but the ILIKE keyword is a case insensitive LIKE.

I'm not sure MySQL has such a keyword. I can't find it in the documentation in any case.

[labmonkey111]

I figured that was a long shot. That may only be a PostgreSQL thing.

[srinivembu]

Thanks for the reply.

Yes, I knew about the LOWER() functionality. Since there may some performance issue, I was looking for other options.

I have figured out another method to achieve this ie., cast() option.

select * from SAMPLE where cast(NAME as char) like 'John'

This results JOHN and john. Is this recommended?

Regards,
Srini

[Atli]

This results JOHN and john. Is this recommended?

I would consider this to be pretty much the same as using the LOWER function.
Both of them are actually altering the data to make it case-insensitive.

Using the COLLATE method I showed earlier would probably be better, as the data would remain unchanged. Only the method used to search the data would change.

Without actually having done much testing, I would assume that the COLLATE method would be much faster.