DevPaper.NET

Storing case-sensitive information in a SQL database can be a bit of a challenge. Traditionally, if you’re going to store information that needs to be retrieved based on the case, you need to encrypt it. But what happens when you are brought in on a project and a data set has been given to you, which has been created over a long period of time? Sure, you can create an application that goes through and encrypts the data, then go through thousands of lines of code, to ensure that every reference to that field is changed to use your encryption method. But maybe that’s not an option.

Since the string-based SQL objects are not case-sensitive, you won’t ever be able to specify that it be called in such a manner. Over the next few weeks, I’m going to explore several options and try to make a series out of this post. I do believe that SQL is a very important language for all programmers to learn, so I highly recommend doing more research than what I may suggest in any articles relating to it.

SQL Strings and Case-Sensitivity

For today’s post, I’m going to show you a quick method for converting your strings to Binary. The advantage here is that capital and lower-case characters differ in the world of Binary.

Let’s use the following bit of data:

ID	SpecialCode
1	DXbvcf?L^D

In this example, I have ID set as an Auto-Incrementing INT and SpecialCode is set as an NCHAR with a length of 10.

If we wanted SpecialCode to only be accessible when someone properly specifies the correct case, we can’t just use a basic SELECT statement. We would get results for the following statements:

  --Proper Casing:
  SELECT *
  FROM My_Table
  WHERE SpecialCode = 'DXbvcf?L^D'

  --Incorrect Casing:
  SELECT *
  FROM My_Table
  WHERE SpecialCode = 'dxBVCF?l^d'

Notice the casing on the string

It’s unfortunate, but at the same time, SQL couldn’t possibly make it case-sensitive. Would you want to try to create a login control, where 10,000 people would have to remember how they capitalized their username? You’d get requests all day long for people who “can’t login”.

Casting a SQL String to SQL Binary

This brings in our solution for telling SQL to make your strings case-sensitive: VARBINARY.

The purpose of VARBINARY is to cast a string to a binary value. The VAR should be used, since you aren’t going to be 100% sure how long you want this to be. (We’ll re-address this in a moment, but for now, we’ll pretend like we don’t know.) So your SQL statement has to be altered a bit:

  DECLARE @StringToConvert NCHAR(10)
  SET @StringToConvert = 'DXbvcf?L^D'

  SELECT *
  FROM My_Table
  WHERE CAST(SpecialCode AS VARBINARY) = CAST(@StringToConvert AS VARBINARY)

That seems simple enough, but look at the first two lines again.

  DECLARE @StringToConvert NCHAR(10)
  SET @StringToConvert = 'DXbvcf?L^D'

SQL objects need to be explicitly declared! Basically, if you put your code to validate inline, the binary value comes out differently. I can’t, for the life of me figure out why this is, but all I can tell you is that it does! You will save yourself from a huge headache if you can just remember this. It doesn’t even appear to matter what data type you use, either. I’ve changed NCHAR to NVARCHAR and the results came out alright.

Conclusion

As stated before, I’m going to try to do some more research on ways to help you make your SQL data case-sensitive. If you have any suggestions, please feel free to leave a comment and I’ll share them in future posts.

~Derek Torrence