I ran into an interesting situation today while writing a query in Microsoft SQL Server’s query analyzer. I have a table with people’s information in it. One of the columns is blood type. In this column you have one of the following values: O,o,A,a,B,b,C,c. In another table I have a map of various ways of showing someone is O Negative. The main way we tend to show it is O- or O+ or A- or A+ etc. Well, when I joined the two tables and attempted to use one of the mapped columns to represent the blood type the number of records doubled.
What on earth is going on? After some playing I found that joining the tables was creating doubles because the query was seeing O the same as o (one being uppercase and one being lowercase.) It was ignoring the case of the values. Apparently there are some settings to make your SQL server case sensative or not case sensative when you install SQL Server. I don’t remember that part, but I wan’t about to go changing any settings and risk the other databases on the server freaking out and causing havok.
So after some Googling I found the following to do a case sensative join since my server is ignoring it:
Select blah, blah, blah
FROM Donor D left outer join abomap A on convert(binary(3),d.btype) = convert(binary(3),a.id)
The key is using the convert function to convert the blood types to binary which you end up with different values from an uppercase O and a lowercase o and your join turns out the way it should.
Pretty cool huh? Well, at least me and the gorilla did. :)