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 · 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

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

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

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 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

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

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

You can change from the property of every item

[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