Jump to content

Archived

This topic is now archived and is closed to further replies.

d_barszczak

Case Sensitive

Recommended Posts

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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
Post the table structure -- you might be using a case-sensitive collation on this column/table.

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.