Is the LIKE operator case-sensitive with MSSQL Server

Question

In the documentation about the LIKE operator  nothing is told about the case-sensitivity of it  Is it  How to enable disable it   I am querying varchar n  columns  on an Microsoft SQL Server 2005 installation  if that matters

User · Accepted Answer

It is not the operator that is case sensitive, it is the column itself.

When a SQL Server installation is performed a default collation is chosen to the instance. Unless explicitly mentioned otherwise (check the collate clause bellow) when a new database is created it inherits the collation from the instance and when a new column is created it inherits the collation from the database it belongs.

A collation like sql_latin1_general_cp1_ci_as dictates how the content of the column should be treated. CI stands for case insensitive and AS stands for accent sensitive.

A complete list of collations is available at https://msdn.microsoft.com/en-us/library/ms144250(v=sql.105).aspx

(a) To check a instance collation

select serverproperty('collation')

(b) To check a database collation

select databasepropertyex('databasename', 'collation') sqlcollation

(c) To create a database using a different collation

create database exampledatabase
collate sql_latin1_general_cp1_cs_as

(d) To create a column using a different collation

create table exampletable (
    examplecolumn varchar(10) collate sql_latin1_general_cp1_ci_as null
)

(e) To modify a column collation

alter table exampletable
alter column examplecolumn varchar(10) collate sql_latin1_general_cp1_ci_as null

It is possible to change a instance and database collations but it does not affect previously created objects.

It is also possible to change a column collation on the fly for string comparison, but this is highly unrecommended in a production environment because it is extremely costly.

select
  column1 collate sql_latin1_general_cp1_ci_as as column1
from table1

User · Answer

You have an option to define collation order at the time of defining your table  If you define a case-sensitive order  your LIKE operator will behave in a case-sensitive way  if you define a case-insensitive collation order  the LIKE operator will ignore character case as well   CREATE TABLE Test       CI Str VARCHAR 15  COLLATE Latin1 General CI AS -- Case-insensitive     CS Str VARCHAR 15  COLLATE Latin1 General CS AS -- Case-sensitive      Here is a quick demo on sqlfiddle showing the results of collation order on searches with LIKE

User · Answer

The like operator takes two strings   These strings have to have compatible collations  which is explained here   In my opinion  things then get complicated   The following query returns an error saying that the collations are incompatible   select   from INFORMATION SCHEMA TABLES where  abc  COLLATE SQL Latin1 General CP1 CI AS like  ABC  COLLATE SQL Latin1 General CP1 CS AS   On a random machine here  the default collation is SQL Latin1 General CP1 CI AS   The following query is successful  but returns no rows   select   from INFORMATION SCHEMA TABLES where  abc  like  ABC  COLLATE SQL Latin1 General CP1 CS AS   The values  abc  and  ABC  do not match in a case-sensitve world   In other words  there is a difference between having no collation and using the default collation   When one side has no collation  then it is  assigned  an explicit collation from the other side    The results are the same when the explicit collation is on the left

User · Answer

If you want to achieve a case sensitive search without changing the collation of the column   database   server  you can always use the COLLATE clause  e g   USE tempdb  GO CREATE TABLE dbo foo bar VARCHAR 32  COLLATE Latin1 General CS AS   GO INSERT dbo foo VALUES  John     john    GO SELECT bar FROM dbo foo    WHERE bar LIKE  j    -- 1 row  SELECT bar FROM dbo foo    WHERE bar COLLATE Latin1 General CI AS LIKE  j    -- 2 rows  GO     DROP TABLE dbo foo    Works the other way  too  if your column   database   server is case sensitive and you don t want a case sensitive search  e g   USE tempdb  GO CREATE TABLE dbo foo bar VARCHAR 32  COLLATE Latin1 General CI AS   GO INSERT dbo foo VALUES  John     john    GO SELECT bar FROM dbo foo    WHERE bar LIKE  j    -- 2 rows  SELECT bar FROM dbo foo    WHERE bar COLLATE Latin1 General CS AS LIKE  j    -- 1 row  GO DROP TABLE dbo foo

User · Answer

You can change from the property of every item

User · Answer

You can easy change collation in Microsoft SQL Server Management studio    right click table -  design  choose your column  scroll down i column properties to Collation  Set your sort preference by check  Case Sensitive

User · Answer

All this talk about collation seem a bit over-complicated  Why not just use something like   IF UPPER   VERSION  NOT LIKE   AZURE     Then your check is case insensitive whatever the collation

User · Answer

Try running   SELECT SERVERPROPERTY  COLLATION     Then find out if your collation is case sensitive or not

[sql] Is the LIKE operator case-sensitive with MSSQL Server?

Examples related to sql

Examples related to sql-server-2005

Examples related to varchar

Examples related to sql-like