Is the LIKE operator case-sensitive with SQL 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.

Corky

It depends on the collation of your column (or database). If it is case sensitive, then LIKE is case sensitive, if it's not, then LIKE is not – Forby

Check the documentation about SQL-Server collations msdn.microsoft.com/en-us/library/ms144250%28v=sql.105%29.aspx – Adon

What is your goal? Do you want it to be case sensitive, or not case sensitive? – Sapphism

Case sensitivity defaults to the collation on the column which defaults to the one on the database. It is get roundable mostly, which way do you want to go? – Oldster

Answer 1

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

Maltreat

It seems the range of characters like [A-Z] is always case-insensitive. [ABCDEFGHIJKLMNOPQRSŠTUVWXYZŽÅÄÖ] however seems to obey collation. – Shulamith

Also, you can query the case sensitivity of a particular column with something like this: select COLLATION_NAME, iif(cast(COLLATIONPROPERTY(COLLATION_NAME, 'ComparisonStyle') as int) & 1 = 0, 'case sensitive', 'case insensitive') from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'exampletable' and COLUMN_NAME = 'examplecolumn' – Caliper

@Shulamith I added your suggestion to the answer. – Anabatic

@JeppeStigNielsen I added your suggestion to the answer. – Anabatic

Answer 2

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

Herculean

Because this isn't sargable. Your example uses a variable and a leading wildcard. But against a column indexed with a case insensitive collation like 'a%' could use the index and the upper version couldn't. – Distill

The question was whether or not like operator is case sensitive. – Shulamith

You need to know the collation, otherwise doing this could be pointless. For instance, if the column being queried against uses Latin1_General_CI_AS, then doing UPPER(@@VALUE) NOT LIKE '%SOMETHING%' or @@COLUMN NOT LIKE '%SOMETHING%' is irrelevant: result would be the same. – Ence

Answer 3

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;

Sapphism

Beware in the last query if you use WHERE bar COLLATE Latin1_General_CS_AS LIKE '[j-k]%' it will return John as in this collation capital J is between lowercase j and lowercase k. It's like aAbBcC...jJkKlLmM... which is not obvious. It seems Latin1_General_BIN is more predictable with range searches w/ LIKE operator. – Cartload

This is the answer that helped ME – Inquisitorial

Answer 4

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

Location

Can you reproduce the error against a table that is NOT a system object like INFORMATION_SCHEMA.TABLES? – Sapphism

@AaronBertrand . . . Yes I can. Is the database broken ;) ? – Location

Don't know, I'm on a mobile device right now and can't spin up a Windows VM. I just don't know that your entire description is technically accurate. – Sapphism

Answer 5

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.

Antichlor

Answer 6

Try running,

SELECT SERVERPROPERTY('COLLATION')

Then find out if your collation is case sensitive or not.

Chopstick

Answer 7

You can change from the property of every item.

Joell

Answer 8

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"

Cristinacristine