Is the Like Operator Case-Sensitive with SQL Server

Is the LIKE operator case-sensitive with SQL Server?

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

Perform a Case insensitive Like query in a case sensitive SQL Server database

You can use UPPER or LOWER functions to convert the values to the same case. For example:

SELECT *
FROM YourTable
WHERE UPPER(YourColumn) = UPPER('VALUE')

Alternatively, you can specify the collation manually when comparing:

SELECT *
FROM YourTable
WHERE YourColumn = 'VALUE' COLLATE SQL_Latin1_General_CP1_CI_AI

SQL Like query is case sensitive even with collation of CI_AI

Found the answer for anyone else who finds themselves in the same situation as me.
Greg was right, it was the column being CS even though the table and database was set to CI.

I had made the table through visual studio and when I reviewed the script it had COLLATE Latin1_General_CS_AS

Redefined the columns without it so it was the table default and all works now.

How do I perform a case-sensitive search using LIKE?

Try using COLLATE Latin1_General_BIN rather than COLLATE Latin1_General_CS_AS

SQL Server LIKE operator with character range and case sensitive column

No it's not a bug.

The range in the pattern syntax is not a regular expression. It defines a range under the collation sort order.

Those collations sort AaBbCcDd...YyZz (with upper case and lower case intermingled) so this is an expected result.

Binary collations sort AB .... YZ .... ab ... yz (with upper case and lower case separated) which is why it works correctly for those.

SQL Server Like Query not case sensitive

Problem:

Query not case sensitive

Cause: Column 'Name' has a case-insensitive (CI) collation.

Solution: You have to use a CS collation: SELECT * FROM fn_helpcollations() WHERE description LIKE N'%case-sensitive%'.

Note: There is a database collation and column level collation. And, there is, also, a server level collation.

SELECT  DATABASEPROPERTYEX(DB_NAME(), 'Collation') AS DatabaseCollation
/*
-- Sample output (my database)
DatabaseCollation
----------------------------
SQL_Latin1_General_CP1_CI_AS
*/

SELECT  col.collation_name AS ColumnCollation
FROM    sys.columns col
WHERE   col.object_id = OBJECT_ID(N'dbo.Table_2') 
AND     col.name = N'Name'
/*
-- Sample output (my database)
ColumnCollation
----------------------------
SQL_Latin1_General_CP1_CI_AS
*/

Simply changing database collation will NOT change the collation for existing user tables and columns:

This statement does not change the collation of the columns in any
existing user-defined tables. These can be changed by using the
COLLATE clause of ALTER TABLE.

Source

After changing database collation, the output of above queries will be:

/*
DatabaseCollation -- changed
----------------------------
SQL_Latin1_General_CP1_CS_AS
*/

/*
ColumnCollation -- no change
----------------------------
SQL_Latin1_General_CP1_CI_AS
*/

and, as you can see the collation of column Name remains CI.

More, changing database collation will affect only the new created tables and columns.
Thus, changing database collation could generate strange results (in my opinion) because some [N][VAR]CHAR columns will be CI and the new columns will be CS.

Detailed solution #1: if just some queries for column Name need to be CS then I will rewrite WHERE clause of these queries thus:

SELECT  Name 
FROM    dbo.Table_2
WHERE   Name LIKE 'Joe' AND Name LIKE 'Joe' COLLATE SQL_Latin1_General_CP1_CS_AS

This will give a change to SQL Server to do an Index Seek on column Name (in there is an index on column Name). Also, the execution plan will include an implicit conversion (see Predicate property for Index Seek) because of following predicate Name = N'Joe' COLLATE SQL_Latin1_General_CP1_CS_AS.

Detailed solution #2: if all queries for column Name need to be CS then I will change the collation only for column Name thus:

-- Drop all objects that depends on this column (ex. indexes, constraints, defaults)
DROP INDEX IX_Table_2_Name ON dbo.Table_2

-- Change column's collation
ALTER TABLE dbo.Table_2
ALTER COLUMN Name VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CS_AS
-- Replace VARCHAR(50) with proper data type and max. length
-- Replace COLLATE SQL_Latin1_General_CP1_CS_AS with the right CS collation

-- Recreate all objects that depends on column Name (ex. indexes, constraints, defaults)
CREATE INDEX IX_Table_2_Name ON dbo.Table_2 (Name)

-- Test query
SELECT  Name 
FROM    dbo.Table_2
WHERE   Name LIKE 'Joe'

How to do a case sensitive search in WHERE clause (I'm using SQL Server)?

Can be done via changing the Collation. By default it is case insensitive.

Excerpt from the link:

SELECT 1
FROM dbo.Customers
WHERE   CustID = @CustID COLLATE SQL_Latin1_General_CP1_CS_AS
    AND CustPassword = @CustPassword COLLATE SQL_Latin1_General_CP1_CS_AS

Or, change the columns to be case sensitive.

LIKE with case sensitive wildcards

Try this instead:

LIKE '%[A-Z][A-Z][A-Z]%' COLLATE Latin1_General_Bin

SQL- Ignore case while searching for a string

Use something like this -

SELECT DISTINCT COL_NAME FROM myTable WHERE UPPER(COL_NAME) LIKE UPPER('%PriceOrder%')

or

SELECT DISTINCT COL_NAME FROM myTable WHERE LOWER(COL_NAME) LIKE LOWER('%PriceOrder%')

How can I write a case sensitive WHERE CLAUSE in SQL Server?

Normally SQL Server is not case sensitive. So 'ABC'='abc' is true in a where clause.

To make a where clause case sensitive, you can use COLLATE. Please try the below query instead:

WHERE
        [Address] = UPPER([Address]) COLLATE SQL_Latin1_General_CP1_CS_AS
        OR
        [Address] = LOWER([Address]) COLLATE SQL_Latin1_General_CP1_CS_AS

---

Related Topics