List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:March 2 2003 10:48pm
Subject:Re: case sensitivity in a delete query
View as plain text  
At 17:51 -0500 3/2/03, Sunfire wrote:
>hi i have a delete query that will delete a record based on the company
>name:
>delete from members where company='$company';
>the query works fine and all but there is a problem because if there is more
>than 1 company listed but with different use of case in their name then
>allrecords that match $company are deleted.. so:
>BLACK
>black
>Black
>BlacK and so on are all considered the same entry by delete and will wipe
>them all even if all i wanted to do was to delete BLACK..
>
>so was just wondering how to fix that if it is even possible.. i fear that
>the same problem will happen with my update statements also and want them
>changed too because if BLACK needs to be updated because the phone number
>changes then i dont need BlacK to inherrit the same phone number
>
>anybody know how to fix?

If your column is CHAR, VARCHAR, or TEXT, use CHAR BINARY, VARCHAR BINARY,
or BLOB instead.

Or just change the query to ... where company = BINARY '$company'.

Comparisons of binary strings are case sensitive.

Best to read the sections in the manual that describe the string columns
to get an idea of how they work.  It'll help you avoid problems like this
in the future.
Thread
case sensitivity in a delete querySunfire2 Mar
  • Re: case sensitivity in a delete queryPaul DuBois2 Mar