Is the LIKE operator case-sensitive with MSSQL Ser

问题:

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.

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

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

回答3:

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.

回答4:

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;

回答5:

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

回答6:

Try running,

SELECT SERVERPROPERTY('COLLATION')

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

回答7:

You can change from the property of every item.

回答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"