How to Use Regex in the SQL Server With a LIKE Operator

Regular expressions are a fundamental skill for anyone who wants to search and manipulate strings. You can specify precise and complex expressions using regex to find and replace various strings and patterns. In tools, such as SSMS, you can specify the regex patterns in the Find What and Find and Replace options.

In this guide, however, we will look at how to execute regular expressions queries in T-SQL using the LIKE and NOT LIKE operators.

NOTE: Regular expressions are a comprehensive topic and cannot be exhausted in a single tutorial. Instead, we will focus on the most command and useful regex you can use in your daily database operations.

In T-SQL, we can define regular expressions using the LIKE operator. The operator will take the matching expression and find any matching patterns.

There are various types of regular expressions in SQL Server:

Alphabetical RegEx
Numerical RegEx
Special Character RegEx
Case Sensitive RegEx
Exclusion RegEx

Let us understand how we can define regular expression in SQL Server.

SQL Server Regular Expressions Examples

Let us understand how to use Regular Expressions in SQL Server using practical examples. In this article, we will use the salesdb sample database.

You can download the sample database from the following resource:

Example 1

The following example query uses a regular expression to find the name of products, starting with the letter L:

USE salesdb;
SELECT Name FROM Products WHERE Name LIKE '[L]%';

The previous query should find the matching patterns and return the result as shown:

Example 2

We filter for matching products in the example above, starting with the letter L. To filter for the first and second letters, we can do the following:

SELECT Name FROM Products WHERE Name LIKE '[L][O]%';

The query should return the names of the products starting with LO. The resulting set is shown as:

Example 3

To filter for over two letters, specify each letter in its square brackets as shown:

SELECT Name FROM Products WHERE Name LIKE '[L][O][C][K]%';

The resulting set is shown as:

Example 4

Suppose you want to filter for products matching a specific string range. For example, products starting with characters between L – P:

SELECT Name FROM Products WHERE Name LIKE '[L-P]%';

The resulting set is as shown:

Example 5

You can also filter for multiple matching conditions in a single query as shown:

SELECT Name FROM Products WHERE Name LIKE '[L-P][a-o]%';

An example result set is as:

Example 6

Suppose you want to get products ending with a specific character? In this case, you can alter the position of the percentage as shown:

SELECT Name FROM Products WHERE Name LIKE '%[pe]';

Results are as shown:

Example 7

To filter for resulting starting and ending with the specified characters, you can run the query as:

SELECT * FROM Products WHERE Name LIKE '[To]%[re]';

The query above should return a result set as:

Example 8

What if you want to exclude specific characters from the filter query? You can use the ^ to exclude the characters.

For example, to get all the products starting with all other characters apart from a to m, we can do the following:

SELECT * FROM Products WHERE Name LIKE '[^a-m]%';

The results should exclude the letter a to m.

Example 9

Suppose you want to find the products where the name contains a number? We can run a query as shown:

SELECT * FROM Products WHERE Name LIKE '%[0-9]';

The result should be as shown:

Note: You can use the NOT LIKE operator to negate the result of the regular expression.

Conclusion

In this article, you learned how to use regular expressions in SQL Server using the LIKE operator. We hope you found this article helpful. Check out more Linux Hint articles for tips and information, and you can learn more about RegEx in SQL Server Docs.