Is SQL Server's LIKE Operator Case-Sensitive?

Case sensitivity in SQL Server LIKE operator

The LIKE operator in SQL Server is a powerful tool for searching and filtering data based on string patterns. However, a common misconception is that the LIKE operator itself is case-sensitive. In fact, case sensitivity largely depends on the collation of the underlying column.

Collation and case sensitivity

Collations are properties assigned to SQL Server databases and columns that define the rules for sorting, comparing, and searching data. Collation affects how characters are interpreted and compared, including case sensitivity.

For example, if a column's collation specifies case sensitivity, such as "Latin1_General_CS_AS" (AS = accent-sensitive), then the LIKE operator's comparison will also be case-sensitive. In this case, searching for "Apple" will return different results than searching for "apple".

Check collation for case sensitivity

To determine the collation of a specific column or database, execute the following query:

<code class="language-sql">-- 检查实例排序规则
SELECT SERVERPROPERTY('collation');

-- 检查数据库排序规则
SELECT DATABASEPROPERTYEX('DatabaseName', 'collation');</code>

Modify sorting rules

If desired, you can modify the collation of a column or database to change case sensitivity. However, this may have an impact on existing data and may require column-level collation modifications.

<code class="language-sql">-- 使用特定排序规则创建表
CREATE TABLE ExampleTable (ExampleColumn VARCHAR(10) COLLATE Latin1_General_CI_AS);

-- 更改表并修改列排序规则
ALTER TABLE ExampleTable ALTER COLUMN ExampleColumn VARCHAR(10) COLLATE Latin1_General_CS_AS;</code>

Notes on dynamically changing collation

While it is technically possible to change the collation of string comparisons at runtime, this is highly discouraged in a production environment as it will impact performance. Use this method with caution only if necessary.

The above is the detailed content of Is SQL Server's LIKE Operator Case-Sensitive?. For more information, please follow other related articles on the PHP Chinese website!