How To do Case Sensitive String Match in SQL Server

-

How To do Case Sensitive String Match in SQL Server 

 
In this post we will try to understand how can we perform case sensitive search/ string match in SQL server. As most of us continue to use SQL server with the default setup where the regular string match is case-insensitive. 

So, to understand the concept let's take an example. 

Let say we have a table with 2 columns ID and UserName, i.e., like this. 

CREATE TABLE TestTable ( 

    Id int, 

[UserName] varchar(255) 

);

 
Also let's add some data to our table using the following script. 

insert into TestTable Values (1,'Kumar'); 

insert into TestTable Values (2,'Prateek'); 

insert into TestTable Values (3,'Kumar Prateek'); 

insert into TestTable Values (4,'KUMAR'); 

insert into TestTable Values (5,'PRATEEK'); 

insert into TestTable Values (6,'KUMAR PRATEEK'); 


And so, the data in our table looks like this. 

Id UserName 

1 Kumar 

2 Prateek 

3 Kumar Prateek 

4 KUMAR 

5 PRATEEK 

6 KUMAR PRATEEK  

 
Now let's try to do some searches. 

Query #1: 

select * from TestTable where [UserName] ='Kumar' 

Response: 

Id UserName 

1 Kumar 

4 KUMAR  

 

Query #2: 

select * from TestTable where [UserName] ='KUMAR' 

Response: 

Id UserName 

1 Kumar 

4 KUMAR  

 
As we can see that in both Query #1 and #2 the response is same even though we are trying to look for a user with username as "Kumar" or "KUMAR", which may not be true all the time as we might want to keep our data (usernames in this example) case sensitive in some cases. 

 
So, the solution to this is to append our query with COLLATE Latin1_General_CS_AS for whichever column we wanted to perform the case sensitive search. 

So, let’s now try to use it and see the difference. 


Updated Query #1: 

select * from TestTable where [UserName] ='Kumar' COLLATE Latin1_General_CS_AS  

Response: 

Id Name 

1 Kumar 


Updated Query #2: 

select * from TestTable where [UserName] ='KUMAR' COLLATE Latin1_General_CS_AS  

Response: 

Id Name 

4 KUMAR 

Also, if we want to do this similarly on queries with IN condition we can do that to like:

select * from TestTable where [UserName] IN ('Kumar', 'KUMAR') COLLATE Latin1_General_CS_AS  

So, we got what we were looking for as an output. Now interesting thing is that we can do this at SQL server level, Database level and even at column level when we define them respectively over and above the way we saw on how to use it in the SQL queries.  By the way the default COLLATE which we continue to use SQL server is SQL_Latin1_General_CP1_CI_AS. 


Hope you liked the post, do provide inputs in the comment, also if you think the article will be helpful for any of your friends and relatives then please do share the article with them. 

And don't forget to subscribe for the new post available so that you get notified on your mailbox. 

Happy Learning. 📚  

Comments

Post a Comment

Popular posts from this blog

Disaster Recovery of Azure API Management (APIM)

-
Image
Disaster Recovery of Azure API Management (APIM)     In this post, we will try to understand how we can apply a disaster recovery approach for Azure API Management aka. APIM. So, first of all, we will have to understand the importance of it. It's very important for an application using APIM to have a strong disaster recovery approach, as in case of any issue because of disaster reported, the system using the APIs underneath should be accessible for the users/ systems, and since the APIM is used as the fore front of the applications (mostly)  it's  important to restore the APIM service ASAP in case of any disaster reported, so that the application consuming the APIs underneath it  is  not having issue connecting to the APIs.       There are 2 broader options which one can think of:   Have a stand-by service in one or more regions   Service back-up and restore features    Let's  talk about both the approaches (one at a ...
Read more

API Aggregation Using Azure API Management

-
  API Aggregation Using Azure API Management         There are cases where we need to call multiple APIs from the client at the same time in order to proceed further with the processing from the client application. Now as oppose to calling those APIs individually  i.e.,  making separate HTTP requests for all those multiple APIs what if we can make one HTTP request and get all the responses aggregated in one response as opposed to multiple response isn't it going to be super cool.       So,  in this post we will try to understand how we can aggregate/ merge multiple API responses from the backend and provide one single request and response from the Azure API Management (APIM) to the calling client application.       To do this first of all we need to understand our APIs request body which we want to merge using APIM, so let say we want to call 2 APIs and  their   URI  are like this.       ...
Read more

API Response Compression in .Net Core

-
Image
API Response Compression in .Net Core If you are here, I am sure you must have used (consumed/ produced) APIs and indeed its been the case in modern web/ mobile application where APIs are used very frequently.  Data transfer size in case of APIs is a very big concern as the volume of data which can be transferred via APIs are not really meant to have huge data as because of following reasons: Data transfer time over network can be a big constraint if the size of data is huge APIs response time are expected to be fast and quick and if the volume of the data is huge obviously the response time of the API will be impacted as well, and no client application consuming the APIs will expect the response time of an API to be beyond a few seconds (at max). So how do we really solve this problem. Well we all understand that API is not really meant to perform huge data transfers but just in case we still have to do how to do it effectively. And there comes API compression frameworks at our re...
Read more