SQL Server provides a powerful tool called the LIKE operator, which allows you to search for and manipulate text using regular expressions. Regular expressions are patterns used to match strings in a WHERE clause. In this article, we will explore the symbols and syntax of regular expressions, and learn how to use the LIKE operator with wildcard characters to create flexible matching patterns.

Using the ‘%’ Wildcard Character

The ‘%’ wildcard character represents any string with zero or more characters in the search pattern. For example, if you want to find all rows in a table where the last name starts with the letter ‘A’, you can use the following SQL statement:

SELECT * FROM Person WHERE lastname LIKE 'A%';

This will return all rows where the last name starts with ‘A’ and is followed by any number of characters.

Using the ‘_’ Wildcard Character

The ‘_’ wildcard character represents any single character in the search pattern. For example, if you want to find all telephone numbers that have an area code starting with 7 and ending in 8, you can use the following SQL statement:

SELECT * FROM PersonPhone WHERE PhoneNumber LIKE '7_8%';

This will return all rows where the phone number starts with ‘7’, followed by any single character, and ends with any number of characters.

Using the ‘[]’ Wildcard Characters

The ‘[]’ wildcard characters allow you to specify a range of characters that can be matched in a specific position. For example, if you want to find all rows where the first name starts with either ‘I’ or ‘K’, you can use the following SQL statement:

SELECT * FROM Person WHERE firstname LIKE '[IK]%';

This will return all rows where the first name starts with either ‘I’ or ‘K’.

Using the ‘^’ Wildcard Character

The ‘^’ wildcard character is used to negate a range of characters in a specific position. For example, if you want to find all rows where the last name does not start with the letters ‘A’ to ‘D’, you can use the following SQL statement:

SELECT * FROM Person WHERE lastname LIKE '[^A-D]%';

This will return all rows where the last name does not start with ‘A’, ‘B’, ‘C’, or ‘D’.

Using the ESCAPE Clause

The ESCAPE clause is used to escape a wildcard character and change its meaning. For example, if you want to find the string ‘100% Free’ in a column, you can use the following SQL statement:

SELECT * FROM temp WHERE col1 LIKE '%100!% Free%' ESCAPE '!';

This will return all rows where the column ‘col1’ contains the string ‘100% Free’.

Using the CASE Statement

The CASE statement can be used with the LIKE operator to categorize data based on a specific pattern. For example, if you want to categorize phone numbers as either domestic or international based on their format, you can use the following SQL statement:

SELECT PhoneNumber, 
       CASE 
           WHEN PhoneNumber LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]' THEN 'Domestic Phone Number' 
           ELSE 'International Phone Number' 
       END AS PhoneCategory 
FROM PersonPhone;

This will return all phone numbers in the ‘PersonPhone’ table, along with their corresponding category.

Conclusion

The SQL Server LIKE operator, along with wildcard characters, provides a powerful tool for searching and manipulating text data. By using wildcard characters such as ‘%’, ‘_’, ‘[]’, and ‘^’, you can create flexible matching patterns to find specific data or perform fuzzy searches. Understanding how to use the LIKE operator and wildcard characters will greatly enhance your ability to query and manipulate data in SQL Server.

Thank you for reading this article on the SQL Server LIKE operator and wildcard characters. If you have any questions, feel free to ask in the comments below.