How to do Case Sensitive Searches in SQL Server

By its very nature, all searches in SQL Server are case sensitive which in most cases can be very useful but there are times when its a hinderance. An example of where it can be a hinderance is is when searching for at astronomy objects. A capital 'B' in a name refers to a star whereas a small 'b' refers to a planet in orbit round a star.

If we want all the first planets discovered orbiting a star from this list.

SQL to create the table above.

Create table SpaceObjects ( Name varchar(20), Object varchar(20) )

Insert into SpaceObjects
    values ('HD 1235A','Star'),
        ('HD 1235Ab','Exoplanet'),
        ('HD 1235B','Star'),
        ('HD 1235Bb','Exoplanet'),
        ('HD 1235Bc','Exoplanet'),
        ('HD 1235Bd','Exoplanet')

We could just search on Object but that defeats the purpose of this example. As for the star, I "made" up the data. If you're curious, the real star data can be found at HIP 1307.

If we only want the exoplanets that were discovered first, that is those that end with a small 'b', we could try :-

Select * from SpaceObjects where Name like '%B'

But the problem would be that it would bring back a star so to avoid the star without using the Object field, we use the Collate command as below.

Select * from SpaceObjects where Name COLLATE Latin1_General_CS_AS like '%b'

And there we go, we only bring back to the two exoplanets that we need so to make a field search case sensitive use COLLATE Latin1_General_CS_AS as above. The CS as you've guessed is Case Sensitive. You can use the Collate command in searches and joins where necessary.

Select * from SpaceObjects SO left join Inhabitents I on SO.Name COLLATE Latin1_General_CS_AS SO.Name = I.PlanetName

Other Articles of Interest

Next Article : How to count the number of distinct field values in a table in SQL Server
Previous Article : How to write protect or unprotect a file using C# or VB.NET

Tags - SQL

Comments and Questions

There's no register feature and no need to give an email address if you don't want to. All messages will be reviewed before being displayed. Comments may be merged or altered slightly, such as if it contains an email or website address.

You can decline to give a name; if that is the case, the comment will be attributed to a random star. A name is preferred, even if it's a random made-up one by yourself.

If you give an email address, you may receive an email notifying you when someone else has added a comment to the same page. In the email will be a link to unsubscribe to further notifications.