SQL Server LIKE Operator

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.

Wildcard Characters

These Wildcard characters help us to fetch data from a database or table by matching the pattern. We will understand each Wildcard character with examples.

We have an Article table with some sample data. We use this table to understand each Wildcard Character with the LIKE Operator.

Article Table

1. Using Percent(%) WildCard

It matches one or more occurrences of character.

Query

SELECT *
FROM Article
WHERE category = 'Python' AND name LIKE '%in%'
ORDER BY submit_dt ASC;

The Result Looks Like:

Result Using Percent Wildcard

Explanation: This query returns the rows having category as “Python” and “in” string in either start or middle or end and it was ordered in ascending by submission date .

2. Using Underscore(_) WildCard

It is used to match single occurrences of character.

Query

SELECT *
FROM Article
WHERE name LIKE '___i%';

The Result Looks Like:

Result Using Underscore(_) Wildcard

Explanation This query returns the name whose 4th character is “i” from name column. The 3underscores(_) specifies that, there can be any character in these 3 underscores but at 4th place their should be ‘i’ present it.

3. (^Character) WildCard

Query

SELECT *
FROM Article
WHERE name LIKE '[^A-P]%' ;

The Result Looks Like:

Name should not start within range A to P

Explanation: This query returns the name does not starts with the letters range from A to P.

4. Using [characters] WildCard

Query

SELECT *
FROM Article
WHERE category LIKE 'W[aeiou]%';

The Result Looks Like:

Start with “W” and 2nd character must be a vowel

Explanation: This query returns the category starting with “W” then followed by any vowels specified in the query.

Using NOT LIKE Operator

NOT LIKE Operator is working opposite as LIKE Operator do. The NOT LIKE Operator to find information that does not match any condition. Let’s understand with examples.

Query

SELECT *
FROM Article
WHERE name NOT LIKE '[E-Z]%';

The Result Looks Like:

Not Start from E-Z

Explanation: This query uses the NOT LIKE operator which returns the name that does not starts with letters ranging from E to Z.

Using ESCAPE CLAUSE

To search for a row pattern containing a specific “escape character” we have to go for “Escape Clause“. In the following example, it uses the character (/) as the escape character and treats the % character as a literal string instead of a wildcard. Remind that without the ESCAPE clause, the query would return an empty result set.

Query

SELECT *
FROM Article
WHERE name LIKE '%\%%' ESCAPE '\';

The Result Looks Like:

\ is the escape character

Explanation

• (%) : wildcard character to match any sequence of characters.
• (\%) : the pattern , where \ is the escape character, and % is the literal %

It retrieves articles where the name column contains the percentage sign %. We can adjust the escape character and pattern based on our specific needs.

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.

SQL_Latin1_General_CP1_CI_AS	case-insensitive
SQL_Latin1_General_CP1_CS_AS	case-sensitive

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:

case-sensitive

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

Conclusion

The LIKE Operator in SQL Server is very helpful operator to find pattern when we don’t have exact information regarding particular pattern. With the help of this Operator one can easily find what they want using the Wildcard . It is helpful when we handle large set of data.