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:
The following example query uses a regular expression to find the name of products, starting with the letter L:
SELECT Name FROM Products WHERE Name LIKE '[L]%';
The previous query should find the matching patterns and return the result as shown:
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:
The query should return the names of the products starting with LO. The resulting set is shown as:
To filter for over two letters, specify each letter in its square brackets as shown:
The resulting set is shown as:
Suppose you want to filter for products matching a specific string range. For example, products starting with characters between L – P:
The resulting set is as shown:
You can also filter for multiple matching conditions in a single query as shown:
An example result set is as:
Suppose you want to get products ending with a specific character? In this case, you can alter the position of the percentage as shown:
Results are as shown:
To filter for resulting starting and ending with the specified characters, you can run the query as:
The query above should return a result set as:
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:
The results should exclude the letter a to m.
Suppose you want to find the products where the name contains a number? We can run a query as shown:
The result should be as shown:
Note: You can use the NOT LIKE operator to negate the result of the regular expression.
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.