SQL LIKE Operator

The SQL LIKE operator is used to match text values against a specified pattern.

It is typically used in a WHERE clause of a SELECT, UPDATE, or DELETE statement to filter the rows of a table based on a specific pattern.

The LIKE operator is often used in conjunction with wildcard characters, which can be used to match any single character or a series of characters.

Syntax

The basic syntax of the LIKE operator is as follows:

SELECT column1, column2, ... FROM table_name WHERE column_name LIKE pattern;
  • column1, column2, ... are the columns that you want to select from the table.
  • table_name is the name of the table that you want to select data from.
  • column_name is the name of the column that you want to filter based on the pattern.
  • pattern is the text or expression that you want to match the column values against.

Wildcard Characters

The SQL LIKE operator can be used with wildcard characters, which are special characters that can be used to match any single character or a series of characters.

The most commonly used wildcard characters are:

  • % : Matches any number of characters, including zero characters.
  • _ : Matches any single character.

For example, the following query returns all rows where the column 'name' starts with 'a':

SELECT * FROM table_name WHERE name LIKE 'a%';

This query will return all the rows where the name column starts with 'a'.

Another example:

The following query returns all rows where the column 'product_code' has exactly four characters:

SELECT * FROM table_name WHERE product_code LIKE '____';

This query will return all the rows where the product_code column has exactly four characters.

Combining Wildcard Characters

You can also combine wildcard characters to create more complex patterns.

For example, the following query returns all rows where the column 'name' starts with 'a' and ends with 'e':

SELECT * FROM table_name WHERE name LIKE 'a%e';

This query will return all the rows where the name column starts with 'a' and ends with 'e'.

ESCAPE Clause

Sometimes, you may want to use a wildcard character as a literal character in the pattern.

In such cases, you can use the ESCAPE clause to specify that a wildcard character should be treated as a literal character.

The syntax of the ESCAPE clause is as follows:

SELECT column1, column2, ... FROM table_name WHERE column_name LIKE pattern ESCAPE escape_character;
  • escape_character is the character that you want to use to escape the wildcard characters in the pattern.

For example, the following query returns all rows where the column 'name' has the value '%abc':

SELECT * FROM table_name WHERE name LIKE '%abc' ESCAPE '%';

This query will return all the rows where the name column has the value '%abc'.

Conclusion

The SQL LIKE operator is a powerful tool for filtering text data based on a specific pattern. It can be used with wildcard characters to match any single character or a series of characters.

By using the ESCAPE clause, you can also use wildcard characters as literal characters in the pattern.

The Like operator is widely used in the SQL, it's very important to understand the concept and examples provided above, to get a better grip on the operator.

SQL Basics