SQL LIKE Operator - How to Use LIKE Operator and Wildcards in SQL?

Recipe Objective - SQL LIKE Operator - How to Use LIKE Operator and Wildcards in SQL? 

The SQL LIKE operator is a powerful tool for conducting flexible and efficient searches in a database. This operator is especially useful when you want to retrieve data based on patterns rather than exact matches. Check out this recipe to explore the SQL LIKE operator, its syntax, and how to use wildcards in SQL to perform intricate searches. 

Understanding the LIKE Operator in SQL Server 

The SQL LIKE operator is employed in a WHERE clause to search for a specified pattern in a column. It allows for partial matching, making it a valuable asset for scenarios where you need to retrieve data that meets certain criteria but doesn't necessarily have to be an exact match.

Syntax of the SQL LIKE Operator

The basic syntax of the SQL LIKE operator is as follows:

SELECT column1, column2, ...

FROM table

WHERE column_name LIKE pattern;

The 'pattern' can include wildcards, which are special characters representing unknown or variable values. Let's delve into the commonly used wildcards in SQL and their applications.

How to Use the LIKE operator in SQL Server?

The LIKE operator provides a measure of pattern matching. It does so by providing wildcards for one or more characters. The following two are the most used wildcard characters alongside the LIKE operator:

SQL Wildcards

• Percent (%) Wildcard: The '%' wildcard represents zero or more characters.

Example: To find all entries starting with "a," you can use WHERE column_name LIKE 'a%'.

• Underscore (_) Wildcard: The '_' wildcard represents a single character.

Example: To find all entries with "a" as the second character, use WHERE column_name LIKE '_a%'

• Character Range Wildcard: Square brackets ([ ]) can be used to specify a range of characters.

Example: To find all entries starting with "a" or "b," use WHERE column_name LIKE '[ab]%'.

• ^ Stands for character/s not mentioned in the brackets

Example: b[^oa]t will find bit, but and not bot or bat

• - Stands for any single character mentioned within the specified range

Example: n[a-h]t finds nat, nbt, nnct, ndt, net, nft, ngt and nht

How to Use a LIKE Operator in SQL for Multiple Values? 

If you need to search for multiple values using the SQL LIKE operator, you can combine multiple conditions using the OR operator.

SELECT column1, column2, ...

FROM table

WHERE column_name LIKE 'value1%' OR column_name LIKE 'value2%';

This allows you to retrieve data that matches any of the specified patterns.

How to Use a LIKE Operator for Numbers in SQL? 

The SQL LIKE operator is not limited to string matching; it can also be used for numeric patterns. When dealing with numbers, it's crucial to ensure that the column type is appropriate for the comparison.

Example: To find all entries starting with "2," use ‘WHERE column_name LIKE '2%'.

Access House Price Prediction Project using Machine Learning with Source Code

 Let us find customers whose name starts with M

Code:

SELECT customer_name FROM customers WHERE customer_name LIKE "M%";

Output:

+---------------+

| customer_name |

+---------------+

| Michael Gray  |

| May Carleton  |

+---------------+

2 rows in set (0.00 sec) 

How to Use Wildcards in SQL? 

Example - Let us find customers that are from cities ending in “on” –

Code:

SELECT customer_name, city FROM customers WHERE city LIKE "%on";

Output:

| customer_name  | city   |

+----------------+--------+

| Alfie Solomons | London |

+----------------+--------+

1 row in set (0.00 sec)

Let us find customers whose name starts with M

Code:

SELECT customer_name FROM customers WHERE customer_name LIKE "M%";

Output:

+---------------+

| customer_name |

+---------------+

| Michael Gray  |

| May Carleton  |

+---------------+

2 rows in set (0.00 sec)

Explore more about SQL Operators with ProjectPro!   

Understanding how to use wildcards opens up a world of possibilities for efficient data retrieval. The flexibility provided by LIKE and its wildcards empowers users to perform intricate searches, significantly enhancing their data manipulation capabilities. To truly solidify your skills, hands-on experience is paramount. Explore more about SQL operators and broaden your practical knowledge through real-world projects with ProjectPro! With an extensive repository of over 270+ projects in data science and big data, ProjectPro is your one-stop platform for honing your SQL skills in a practical and immersive learning environment.