In my previous post (#354)on implementing case sensitive search in a database that was case insensitive, I had presented one possible implementation approach that used the COLLATE keyword to temporary convert the column of interest to a case sensitive collation.

I had mentioned that the mechanism internally used the CONVERT() function with the style parameter = 0, which means that it was performing a binary to character conversion and then executing the comparison, resulting in a performance overhead to the system.

As I was asking for feedback from friends and colleagues, I received a couple of interesting ideas, the top 2 being:

• Regular Expressions
• PATINDEX

The reason that these caught my attention is quite simple – logic would say that they have to be natively case sensitive – one is a regular expression (which is case sensitive in other programming languages) and the other looks for a pattern within the supplied sting. However, there were serious doubts on whether the underlying column collation will have any effect on the behaviour of these options or not. I therefore decided to test out both the options.

Generating the Test Data

Let us create the same test table that was used in the previous post and also allow it to use the same case insensitive collation as the AdventureWorks2012 database.

USE AdventureWorks2012;
GO

--Step 01: Create test data
IF OBJECT_ID('dbo.Product','U') IS NOT NULL
    DROP TABLE dbo.Product;
GO
CREATE TABLE dbo.Product 
    (ProductId INT NOT NULL IDENTITY(1,1),
     ProductName VARCHAR(100) NOT NULL,
     VendorName VARCHAR(100)
    );
GO

--Add some test data
INSERT INTO dbo.Product (ProductName, VendorName)
VALUES ('Cycles','Contosso'),
       ('cBike', 'AdventureWorks'),
       ('SomeOtherProduct','SomeOtherVendor');
GO

Using Regular Expressions

Now, the regular expression to execute a search for all products whose name begins with “c” (small case) is “[c]%”.

Let me try to use this regular expression in my search query:

USE AdventureWorks2012;
GO
SELECT ProductId,
       ProductName,
       VendorName
FROM dbo.Product 
WHERE ProductName LIKE '[c]%';
GO

The result is shown in the screenshot below. Records for all products beginning with the letter “c” were returned – irrespective of the case.

Using PATINDEX

The second search mechanism that I will be evaluating is the PATINDEX, which basically returns the index or the position where a particular character pattern is found in a given string. If the pattern is not found, the value returned is 0.

USE AdventureWorks2012;
GO
SELECT ProductId,
       ProductName,
       VendorName
FROM dbo.Product 
WHERE PATINDEX('c%',ProductName) > 0;
GO

Again the result is as shown in the screenshot below – a case in-sensitive search was performed by SQL Server.

Conclusion

The collation of a particular column determines whether a search on a column will be case sensitive or  case in-sensitive. This is not dependent upon the mechanism used for the comparison (i.e. a simple query v/s regular expressions v/s pattern matching).

Do you have any ideas on implementing  case sensitive search on a case in-sensitive database column? If so, please share in the blog comments.

Further Reading

• Using Regular Expressions in CHECK constraints: http://beyondrelational.com/modules/2/blogs/77/Posts/19603/0285-sql-server-using-regular-expressions-with-check-constraints.aspx
• Identify records with special characters using Regular expressions: http://beyondrelational.com/modules/2/blogs/77/Posts/19775/0314-sql-server-identify-records-with-special-characters-using-regular-expressions.aspx

Until we meet next time,
Be courteous. Drive responsibly.

Searching data from a column is a very common requirement – but with unique variants in each application. Recently, I came across one such requirement:

1. Underlying database, table and columns use a case in-sensitive (CI) collation
2. Application must be able to switch between a case sensitive v/s a  case insensitive comparison

The ability to switch between a case sensitive and case insensitive comparison is the challenge because it needs to be done on a database that inherently does not discriminate between the two.

In this post, I will present one method of implementing this requirement. If you have any others that you would like to share, please feel free to do so in the comments area.

Temporarily using Case Sensitive Collations

The most common implementation pattern to realize the case sensitive search requirement is to temporarily use a collation that is case sensitive as shown below.

To begin with, let us create a test table and insert some fictional test data.

USE AdventureWorks2012;
GO

--Step 01: Create test data
IF OBJECT_ID('dbo.Product','U') IS NOT NULL
    DROP TABLE dbo.Product;
GO
CREATE TABLE dbo.Product 
    (ProductId INT NOT NULL IDENTITY(1,1),
     ProductName VARCHAR(100) NOT NULL,
     VendorName VARCHAR(100)
    );
GO

--Add some test data
INSERT INTO dbo.Product (ProductName, VendorName)
VALUES ('Cycles','Contosso'),
       ('cBike', 'AdventureWorks'),
       ('SomeOtherProduct','SomeOtherVendor');
GO

Next, let us fetch the collation of the database where our test table resides. You may also want to use the system stored procedure (sp_help) to study the collation of the table columns (which by default would be same as that of the database in which the table resides). On my server, the collation was – [SQL_Latin1_General_CP1_CI_AS] which is case in-sensitive (notice the CI in the collation name).

--Step 02: Check the database collation
USE AdventureWorks2012;
GO
SELECT DATABASEPROPERTYEX(N'AdventureWorks2012',
                          N'Collation');
GO
--For AdvenutreWorks2012 
--SQL_Latin1_General_CP1_CI_AS

--Check the collation of the table columns
sp_help [dbo.Product];
GO

Now, attempt to query the table for all products whose names begin with the letter ‘c’ (small case). To do so, we need to temporarily COLLATE the column – dbo.Product.ProductName to a case sensitive collation (in my case, this is SQL_Latin1_General_CP1_CS_AS).

USE AdventureWorks2012;
GO
SELECT ProductId,
       ProductName,
       VendorName
FROM dbo.Product 
WHERE ProductName 
    COLLATE SQL_Latin1_General_CP1_CS_AS
    LIKE 'c%';
GO

An observation

As can be seen from the image below, desired results are obtained. However, if one has the actual execution plan opened up, an interesting observation can be made if we look at the properties of the SELECT operation.

We can observe that SQL Server internally uses the CONVERT function with a style of 0. Per Books On Line (see references below), this indicates a binary to character conversion. This conversion is performed for all records in the table, which in-turn affects performance.

Conclusion

Because we have the overhead of applying a conversion on all rows of a particular table, this method should be used only when the column cannot be modified to permanently use a case sensitive collation and a business need exists to provide both case sensitive & insensitive search.

Further Reading

• The COLLATE Keyword: https://msdn.microsoft.com/en-us/library/ms184391.aspx
• Refer Binary Styles in CAST and CONVERT: https://msdn.microsoft.com/en-us/library/ms187928.aspx
• System Stored Procedure sp_help: https://msdn.microsoft.com/en-us/library/ms187335.aspx

Until we meet next time,
Be courteous. Drive responsibly.