Case-sensitive Querying in SQL Server

Case Sensitive Queries in SQL Server

Can you filter/sort/manipulate SQL Server data in a case sensitive manner? The answer is of course, yes. But first, let me brief you as to why this is a question at all.

SQL Server, like most database systems have a collation setting, which by default is case insensitive. Most of us are quite used to SQL Server with this setting (for some of us subconsciously it has been set in our heads that SQL Server is case insensitive). True to that, most applications that we develop usually also do not demand case sensitivity. The dilemma occurs when suddenly there’s a requirement to perform a case sensitive filter on a table, or perhaps retrieve unique records taking case sensitivity into consideration.

The solution to this is quite simple. We need to use collations. Collations determine case sensitivity among other things. The default collation in SQL Server is SQL_Latin1_General_CP1_CI_AS. This collation deals with English characters, and the CI in it indicates case insensitivity; which means the values mango, Mango and MANGO are considered to be the same.

I’ll now show you some scenarios where we can perform case sensitive operations on data such as that. Consider the following table which I will use for the scenarios.

ID Name Category Price
1 Mango FR 40.00
2 MANGO FR 42.00
3 Banana FR 12.00
4 BANANA FR 13.50
5 mango FR 50.00
6 Banana GR 15.00

Scenario 1 – Retrieving unique records

1 -- Unique case insensitive data (with default settings) 2  SELECT DISTINCT [Name] 3  FROM dbo.Product 4 5  -- Unique case sensitive data (using collations) 6 SELECT DISTINCT [Name] COLLATE SQL_Latin1_General_CP1_CS_AS 7 FROM dbo.Product 8 9 -- Results 10 /* 11 12 Name 13 ---------- 14 Banana 15 mango 16 17 (2 row(s) affected) 18 19 ---------- 20 BANANA 21 Banana 22 MANGO 23 Mango 24 mango 25 26 (5 row(s) affected) 27 */

Scenario 2 – Sorting

1 -- Sorted data (with default settings), but no sorting within 'mango' and 'banana' 2 SELECT [Name] 3 FROM dbo.Product 4 ORDER BY [Name] 5 6 -- Sorted data (using collations), data sorted also within the 'mango' and 'banana' groups 7 SELECT [Name] 8 FROM dbo.Product 9 ORDER BY [Name] COLLATE SQL_Latin1_General_CP1_CS_AS 10 11 -- Results 12 /* 13 14 Name 15 ---------- 16 Banana 17 BANANA 18 mango 19 Mango 20 MANGO 21 22 (5 row(s) affected) 23 24 Name 25 ---------- 26 BANANA 27 Banana 28 MANGO 29 Mango 30 mango 31 32 (5 row(s) affected) 33 */

Scenario 3 – Filtering

1 -- Filtered data (with default settings) 2 SELECT * 3 FROM dbo.Product 4 WHERE [Name] = 'mango' 5 6 -- Filtered data (using colltions) 7 SELECT * 8 FROM dbo.Product 9 WHERE [Name] = 'mango' COLLATE SQL_Latin1_General_CP1_CS_AS 10 11 -- Results 12 /* 13 14 ID Name Category Price 15 ----------- ---------- -------- --------------------- 16 1 Mango FR 40.00 17 2 MANGO FR 42.00 18 5 mango FR 50.00 19 20 (3 row(s) affected) 21 22 ID Name Category Price 23 ----------- ---------- -------- --------------------- 24 5 mango FR 50.00 25 26 (1 row(s) affected) 27 */

When using collations to manipulate data, chose the appropriate one. For instance if the default collation is SQL_Latin1_General_CP1_CI_AS, then choose the corresponding case sensitive collations: SQL_Latin1_General_CP1_CS_AS. Note that some collations do not have corresponding case related collations.

If your server, database or table columns are already configured with a case sensitive collation, then you only need to write the queries as usual.

Advertisements

One thought on “Case-sensitive Querying in SQL Server

  1. Someone essentially help to make significantly posts I might state. This is the first time I frequented your web page and so far? I surprised with the research you made to create this actual submit amazing. Magnificent activity! ecdkcecdbdbe

    Like

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