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.

Name	Object
HD 1235A	Star
HD 1235Ab	Exoplanet
HD 1235B	Star
HD 1235Bb	Exoplanet
HD 1235Bc	Exoplanet
HD 1235Bd	Exoplanet

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

Tags - SQL

Last Modified : June 2023