Jump to content


Photo

Case Sensitive


  • Please log in to reply
6 replies to this topic

#1 d_barszczak

d_barszczak
  • Members
  • PipPipPip
  • Advanced Member
  • 188 posts
  • LocationBradford - UK

Posted 11 August 2006 - 12:18 PM

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.

When all else fails - Try reading the manual.

Integrated IT Systems Ltd


#2 effigy

effigy
  • Staff Alumni
  • Advanced Member
  • 3,600 posts
  • LocationIL

Posted 11 August 2006 - 01:39 PM

MySQL is case insensitive, unless there is a configuration option I'm unaware of.

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

select * from table where name like binary 'david%' should keep 'david' lowercase, resulting in davidA, davidB, davidc, etc.
Regexp | Unicode Article | Letter Database
/\A(e)?((1)?ff(?:(?:ig)?y)?|f(?:ig)?)\z/

#3 d_barszczak

d_barszczak
  • Members
  • PipPipPip
  • Advanced Member
  • 188 posts
  • LocationBradford - UK

Posted 14 August 2006 - 11:48 AM

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.

When all else fails - Try reading the manual.

Integrated IT Systems Ltd


#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 14 August 2006 - 02:56 PM

Post the table structure -- you might be using a case-sensitive collation on this column/table.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 d_barszczak

d_barszczak
  • Members
  • PipPipPip
  • Advanced Member
  • 188 posts
  • LocationBradford - UK

Posted 16 August 2006 - 07:57 AM

Ok here is the table in question.

+-------+-------------+-----------+------+-----+---------+-------+----------------------+---------+
| 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 |         |
+-------+-------------+-----------+------+-----+---------+-------+----------------------+---------+


When all else fails - Try reading the manual.

Integrated IT Systems Ltd


#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 16 August 2006 - 03:56 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#7 d_barszczak

d_barszczak
  • Members
  • PipPipPip
  • Advanced Member
  • 188 posts
  • LocationBradford - UK

Posted 16 August 2006 - 10:00 PM

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

When all else fails - Try reading the manual.

Integrated IT Systems Ltd





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users