SQL Server's LIKE Operator: Case Sensitivity Explained

SQL Server's LIKE operator provides robust string matching. However, its case sensitivity isn't inherent to the operator itself. Instead, it's determined by the column's collation.

Collation: The Key to Case Sensitivity

A column's collation dictates how character comparisons are handled. SQL Server supports various collations, each specifying the character set, alphabet, and crucially, case-sensitivity rules.

Determining Collation

To check a database's collation:

SELECT DATABASEPROPERTYEX('databasename', 'collation') AS SQLCollation;

For a specific column:

SELECT COLLATION_NAME
FROM sys.columns
WHERE object_id = OBJECT_ID('TableName') AND name = 'ColumnName';

Collation's Impact on LIKE Results

Different collations produce different LIKE results. For example:

• sql_latin1_general_cp1_ci_as: Case-insensitive, accent-sensitive.
• sql_latin1_general_cp1_cs_as: Case-sensitive, accent-sensitive.

Modifying Collation (Use with Caution)

While generally discouraged, you can alter a column's collation:

ALTER TABLE TableName ALTER COLUMN ColumnName VARCHAR(10) COLLATE new_collation;

Alternatively, you can temporarily override case sensitivity within a query (not recommended for production):

SELECT column1 COLLATE sql_latin1_general_cp1_ci_as AS column1
FROM table1;

In Summary

Accurate string matching with SQL Server's LIKE operator hinges on understanding the impact of collation. Careful collation selection ensures queries behave as expected, regardless of case sensitivity requirements.