Case Sensitive

[d_barszczak]

Hi,

I know that MySql is case sensitive but is there any way to ignore case sensitivity so in a table full of names i could search david% and all names begining with David or david or dAvId would be returned.

Thanks.

[effigy]

MySQL is case [b]in[/b]sensitive, unless there is a configuration option I'm unaware of.

[tt]select * from table where name like 'david%'[/tt] should give you what you want, including dAvidA, DAVIDb, daviDc, etc.

[tt]select * from table where name like binary 'david%'[/tt] should keep 'david' lowercase, resulting in davidA, davidB, davidc, etc.

[d_barszczak]

Hmmm,

Think i may have applyed a setting then as if i perform the query "WHERE name like 'd%'" i get nothing but if i do this "WHERE name like 'D%'" i get the results im after.

Thanx.

[fenway]

Post the table structure -- you might be using a case-sensitive collation on this column/table.

[d_barszczak]

Ok here is the table in question.

[code]
+-------+-------------+-----------+------+-----+---------+-------+----------------------+---------+
| Field | Type        | Collation | Null | Key | Default | Extra | Privileges          | Comment |
+-------+-------------+-----------+------+-----+---------+-------+----------------------+---------+
| name  | varchar(40) | ascii_bin | YES  |    |        |      | select,insert,update |        |
| age  | int(11)    |          | YES  |    |        |      | select,insert,update |        |
| sex  | varchar(1)  | ascii_bin | YES  |    |        |      | select,insert,update |        |
+-------+-------------+-----------+------+-----+---------+-------+----------------------+---------+

[/code]

[fenway]

Well, those look like binary collations, meaning they're case sensitive -- that's not the default, so your DB must have a different character set?  You can either change the collation on the table, or simply collate as part of your query.

[d_barszczak]

ahh so thats my problem.

I have always used an web hosting company to manage my mysql server but this is my test server that i have at home.

I changed it to ascii_bin as i think the default was something like latin_sweedish or something.

So it's the collation.

Shortly after i made the post i realised that i have used case-insensitive queries before.

Thank you for your help