I’m sure that every SQL Server developer has had the experience of waking up at 3:00 AM, in a cold sweat, wondering “How can I execute a case sensitive query when my server uses a case insensitive collation?”
Okay, who am I kidding. I bet almost no one has lost sleep over this issue. Nevertheless, I bet most SQL Server installations use a case insensitive collation because that seems to be the default. For example, on SQL Server 2005 the default collation for the English Canada locale is Latin1_General_CI_AS, where CI stands for “case insensitive”. Similarly, the default collation for the English United States locale is SQL_Latin1_General_CP1_CI_AS. In fact, every locale seems to use a case insensitive collation by default.
If you use a case insensitive collation, there will always be the possibility that one day you might need to execute a case sensitive query. For example, consider the simple design for a Person table, shown in Figure 1. Yes, it’s contrived but it is good enough to illustrate the concept.
Fig. 1 - Simple Person Table Design
Users being users, undoubtedly, one of them will enter in a person’s name with incorrect capitalization. For example, consider the three different capitalizations of the first name “John” in Figure 2. The first names “john” and “JOHN” would probably not look great on a mailing label. I hate it when that happens to me. Maybe I’m picky?
Fig. 2 - Person Table Sample Data
But how can you quickly find such bad capitalizations of “John” if your database uses a case insensitive collation? As shown in Figure 3, a simple query such as select * from dbo.person where first_name = ‘JOHN’ will not return only the records with “JOHN”. Unfortunately, you will also get records with “John” and “john”.
Fig. 3 - Case Insensitive Person Query
In this contrived case, I suppose you could do an update replacing any capitalization of “John” with “John” but that is just a brute-force bandaid. Besides, what if you just wanted to get a count of the records with the capitalization of “JOHN”? I actually had a situation recently that required such a count.
So how do you do a case insensitive query in SQL Server 2005? It’s simple. You just specify a case sensitive collation for each condition in your query. For example, as shown in Figure 4, you could use this query: select * from dbo.person where first_name = ‘JOHN’ collate SQL_Latin1_General_CP1_CS_AS.
Fig. 4 - Case Sensitive Person Query
With the case insensitive collation specified in the query, you get the expected results. That is, when you query for ‘JOHN’ you get only ‘JOHN’ records.
If this tip helped you, please leave a comment!
