Case Sensitive QUERY

[johnrb87]

Hi

 

I have a database table which holds the names of people, some names require ascents and special characters, so we have stored the data as VARBINARY and with NO "Collation" as this seems to work the best at storing the original data.

 

I then do a QUERY like

 

SELECT * FROM `names` WHERE `surname` = 'Zams'

 

and it works and I get 8 results back

 

If I then do a QUERY like

 

SELECT * FROM `names` WHERE `surname` = 'zams'

 

and switch the keyword to lowercase (zams), then the QUERY comes back and fails

 

I have been told to put

 

COLLATE SQL_Latin1_General_CP1_CI_AS

 

just before

 

= 'zams'

 

but that doesn't work and comes back with problems

 

can anyone help?

 

thanks

[fugix]

what you can do is make sure that the user input will always have the first letter of the string capitalized so your query doesnt fail... you can use ucwords() or ucfirst()

[mikosiko]

have you tried one of the MYSQL functions LOWER() or UPPER() ?    (in your case since that you are storing fields as VARBINARY you should combine them with CONVERT() )

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_lower