COLLATE CLAUSE

LIKE Operator is case-insensitive by default. To change it a case-sensitive we have to use COLLATE. To change a row as case-sensitive LIKE operator was also used.

To Create:

INSERT INTO Article (article_id, name, category, length, submit_dt)
VALUES (10, 'Secure Authentication in Python', 'Python', 950, '2023-06-15') 
COLLATE SQL_Latin1_General_CP1_CS_AS; 

Explanation: In the above Query, we have inserted some data and also convert the LIKE Operator from case-insensitive to case-sensitive using the COLLATE Clause.

To Change Specific Row:

SELECT *
FROM Article
WHERE name LIKE 'Value%' COLLATE SQL_Latin1_General_CP1_CS_AS;

The Result Looks Like:

Explanation: This query changes the row that starts with “Value” to case-sensitive from case-insensitive using the COLLATE clause.

The SQL Server LIKE operator is similar to the SQL LIKE Operator. It retrieves the rows by matching a string or character-specified pattern. A pattern can include regular characters wildcard characters or both. It is often used in the WHERE clause of the SELECT, UPDATE, and DELETE statements to filter rows based on pattern matching. It is a flexible operator that helps in finding pattern matching when you don’t know the exact pattern.

Syntax:

SELECT column1, column2
FROM table_name
WHERE col_name LIKE pattern ;

Explanation: In the above query, we have used the LIKE Operator to find the pattern in the particular column called col_name in table table_name. We will understand everything in detail in the below examples.