The SQL Like Command

Introduction

The SQL LIKE command is used in a WHERE clause to search for a specified pattern in a column. It’s commonly used in string comparison to find matches in text data within the database.

There are two wildcards used with LIKE:

• %: Represents zero, one, or multiple characters.
• _: Represents a single character.

Example of an SQL Query Using LIKE

Let’s consider a complex SQL example using LIKE with comments explaining each part:

-- Query to find employees with specific name patterns and ordering results
SELECT 
    EmployeeID,                  -- Selects the 'EmployeeID' column
    Name,                        -- Selects the 'Name' column
    Department,                  -- Selects the 'Department' column
    Email                        -- Selects the 'Email' column
FROM 
    Employees                    -- Indicates the source table for the query
WHERE 
    Name LIKE 'J%'               -- Looks for employees whose names start with 'J'
    AND Email LIKE '%@company.com'  -- Filters for emails ending with '@company.com'
ORDER BY 
    Name;                        -- Orders the results by the 'Name' column

In this example:

• SELECT specifies the columns to be displayed in the result set.
  
• FROM Employees indicates that the data is being retrieved from the Employees table.
  
• WHERE Name LIKE 'J%' is used to find employees whose names start with ‘J’. The % symbol is used as a wildcard to represent any sequence of characters following ‘J’.
  
• AND Email LIKE '%@company.com' further filters the results to include only those employees whose email addresses end with ‘@company.com’. Again, % is used as a wildcard to represent any sequence of characters preceding ‘@company.com’.
  
• ORDER BY Name sorts the results alphabetically by the name of the employees.

This query is useful when you need to search for data with specific patterns in a database, such as finding all employees with names starting with a particular letter or with email addresses from a specific domain.

The - character can also be used to match a single character. For example, the following query retrieves all records from the employees table where the last_name column contains the substring “mith” or “ones”:

SELECT * FROM employees
WHERE last_name LIKE '%mith-' OR last_name LIKE '%ones-';

Conclusion

The SQL LIKE command is a versatile tool for searching for patterns in data and retrieving specific records from tables. It allows you to match data based on specific criteria, including partial matches, exact matches, and fuzzy matches. Additionally, the LIKE command can be used to search across multiple fields in a single query, providing a powerful mechanism for extracting relevant information from your data.

Related posts:

Demystifying the SqlPackage.exe Command Git Command List Tailoring Your Command-Line with Oh My Posh: Exploring Module Configurations Keeping Your Git Repository Tidy: The git clean Command