S h o r t S t o r i e s

// Tales from software development

Case sensitive comparisons in MySQL

leave a comment »

Read Codes are used to describe patient test results in some of the health systems that our software interfaces with. Our interfaces perform a mapping of a particular Read Code value in the data being imported from an interface to a data item in our system.

Read Codes are case sensitive and normally this isn’t an issue because the mapping is performed in program code and is case sensitive. However, from time to time I do some maintenance or analysis of the data in a system using the MySQL Query Browser and, usually, the comparisons on VARCHAR fields are case insensitive which can produce misleading query results.

For example, when a new mapping of a Read Code value to an data item is added it’s important that the mapping doesn’t already exist as the duplicate will cause our software to fail when it loads the Read Codes into a dictionary. So, I want to use a query like this to check for duplicate mappings:

SELECT ReadCode FROM Codes GROUP BY ReadCode HAVING COUNT(*) > 1;

 
Typically, MySQL treats Read Codes that differ only in casing as though they were the same value. So, for example, the Read Codes ’44A1.’ and ’44a1.’ will be treated as the same value although they are not.

Fortunately, MySQL provides the BINARY keyword to force case sensitive comparison:

SELECT ReadCode FROM Codes GROUP BY BINARY ReadCode HAVING COUNT(*) > 1;

 
Similarly, a simple SELECT statement using the ‘=’ operator for comparison can also be qualified with the BINARY keyword:

SELECT * FROM Codes WHERE ReadCode = BINARY '44A1.';

 
This will return the row for ’44A1.’ but not the row for the ’44a1.’ Read Code.

Advertisements

Written by Sea Monkey

December 14, 2010 at 8:00 pm

Posted in Development

Tagged with

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: