How to Use Regex in SQL With Practical Examples

The issue of regular expressions in SQL Server is a tricky one. On the one hand, they can provide you with more complex and precise ways of getting and managing your data. On the other hand, SQL Server does not support regular expressions natively, yet provides a few alternatives and workarounds. In this guide, we'll cover them and show you all the benefits, practical examples, and actual tools that will make your data querying in SQL Server easier than ever.

Introduction to regular expressions in SQL Server

In SQL, a regular expression (a.k.a. regex) is a sequence of characters that defines a search pattern for text. Regular expressions are commonly used for a variety of tasks such as pattern matching, data validation, data transformation, and querying. It's a highly flexible technique for searching and manipulating data based on specified patterns or criteria.

But if we narrow things down to SQL Server, the first thing we'll see is that it has no native support for regular expressions. Instead, it provides limited regex-like functionality using certain operators, with LIKE being the most prominent one. Although it is more limited than full regular expressions, it still comes in handy, and we'll review a couple of use cases here.

We'll also mention a workaround that might help you out. SQL Server 2016 and later versions support regular expressions via the CLR (Common Language Runtime) integration, which allows writing .NET code that can use regular expressions and their capabilities to the full.

Why use regular expressions?

Regular expressions are very helpful when it comes to complex search-and-replace, validation, or extraction operations. Here are some of the most important benefits of regex.

1. First and foremost, regular expressions allow you to search for patterns rather than specific strings, which makes them highly useful when you are dealing with large datasets. In other words, they offer more precise and context-based search operations.

2. Regular expressions help match complex patterns in a more sophisticated way than basic LIKE or PATINDEX expressions. With regular expressions, you can define patterns with greater flexibility, e.g., match multiple digits, email addresses, phone numbers, URLs, and more complex string structures.

3. Regular expressions are ideal for validating input data. For instance, you can use them to make sure that your values conform to certain patterns such as a valid email address, a phone number, a data format, or a postal code format.

4. The next advantage of regex is its ability to perform advanced string replacements and transformations. You can extract substrings that match a pattern, replace text with another pattern, or format text based on your specific rules.

5. Regular expressions are often used to extract data from structured text, such as logs, URLs, or CSV data. For example, you can use regex to extract certain fields from a text string that follows a specific pattern.

6. Finally, using regular expressions, you can reduce the complexity of the logic needed to search, validate, or manipulate your data, which means you can simplify your SQL code and make it more maintainable. Without regular expressions, you might need to write lengthy CASE or IF statements to replicate the same behavior that regex can do in a much more concise way.

Although we can't wield the full power of regex in SQL Server yet, we have a few tools to help us out.

How to implement regular expressions in SQL Server

Let's move on and see how to use regex in SQL Server; or, we'd rather say, how to overcome the lack of support for those.

Using the LIKE operator as a basic alternative

The key replacement for regex is the SQL LIKE operator, which acts as a basic regex in SQL Server, helpful in pattern matching. A basic query may look as follows:

SELECT column_name 
FROM table_name 
WHERE column_name LIKE pattern;

Your pattern can use one of the two wildcard characters to search for matches:

• _ represents a single character.
• % represents any sequence of zero or more characters, with case sensitivity determined by the database collation.

Here's a simple example. Let's run a query that retrieves all products whose names start with 'Com'.

SELECT Name 
FROM Production.Product 
WHERE Name LIKE 'Com%';

Now, let's run it against our sample database using dbForge Studio for SQL Server, your perfect companion for interactions with SQL Server databases.

And so we get the result.

Matching characters and ranges

You can find matches with specific characters or character ranges using the [] characters. This is the syntax of a basic query.

SELECT column_name 
FROM table_name 
WHERE column_name LIKE '[character_range]%';

Here's how you can find employees whose last names begin with A, B, C, or D.

SELECT LastName 
FROM Person.Person 
WHERE LastName LIKE '[A-D]%';

If we run this query, we get the following output.

Quantifiers for pattern matching

SQL Server does not support the use of regex quantifiers (such as *, +, ?, and {n,m}) with the LIKE operator. However, you can use _ to find matches with a specific number of characters.

For instance, if you are searching for phone numbers that start with 9 characters and a hyphen, this is how you can get all the values that include characters as well as whitespaces.

SELECT PhoneNumber 
FROM Person.PersonPhone 
WHERE PhoneNumber LIKE '_________%';

Let's run it in the Studio as well.

Using grouping and alternation

Another not-so-good news is that SQL Server does not support the alternation operator (|) as well. However, you can use the OR operator to achieve the same result. This is what the syntax looks like.

SELECT column_name 
FROM table_name 
WHERE column_name LIKE pattern1 
   OR column_name LIKE pattern2;

Here's one more quick example. Let's find all products whose names contain 'woo' or 'cor'.

SELECT Name 
FROM Production.Product 
WHERE Name LIKE '%woo%' 
   OR Name LIKE '%cor%';

Let's run it in dbForge Studio to get the output.

That's it. LIKE may not be as powerful as full-fledged regular expressions, yet it can help you out in many cases.

Using SQL regex in dbForge Studio for SQL Server

In the examples above, we've shown you how to run SQL queries against your databases using a multifeatured SQL Server GUI called dbForge Studio for SQL Server. With its help, you can write and execute queries of any complexity against your databases. Besides that, the Studio delivers quite a few tools that cover multiple aspects of SQL Server development, management, and administration. The key features of the Studio include the following:

• Context-aware SQL code completion, formatting, refactoring, and debugging
• Query optimization via profiling
• Coding-free building of queries on diagrams
• Visualization of database structures on ER diagrams
• Visual table design
• Flexible data management in a visual editor
• Identification of differences in database schemas and table data
• Quick synchronization of source and target databases
• Generation of realistic test data
• Generation of full database documentation
• Data aggregation and analysis in visual pivot tables
• Generation of detailed data reports
• Source control integration (covering all major version control systems)
• Database and server monitoring, backup/recovery, user and session management, and more

You can explore all of these features yourself. Simply download dbForge Studio for a free 30-day trial and give it a go. You will definitely be interested in some of its features that we'll cover in the following sections.

Writing and testing regex queries in dbForge Studio for SQL

First of all, you can write regex-like queries (or any queries, for that matter) directly in the SQL Editor of dbForge Studio. You can access it by clicking New SQL on the toolbar. The Editor will speed up your coding with statement completion and smart object suggestions.

Once you are ready with your query, simply hit Execute and get the results, just like we did in our examples. If the Studio encounters an error, you will be notified about it.

Debugging and optimizing regex queries in dbForge Studio for SQL Server

You can as well debug regex-like queries in the Studio as well as optimize their performance. For that purpose, the Studio will provide you with tools like T-SQL Debugger and Query Profiler.

T-SQL Debugger is an advanced tool that's absent from the latest versions of stock SQL Server tools like SSMS. It delivers the easiest way of debugging SQL queries, stored procedures, triggers, functions, and T-SQL scripts.

Query Profiler is yet another integrated tool that helps you analyze and optimize the performance of your SQL queries. With its help, you can compare query profiling results, inspect EXPLAIN plans, detect potential bottlenecks, and diagnose performance issues with slow queries.

Automating and reusing regex queries in dbForge Studio for SQL Server

Now, what about making your work with queries even easier? The Studio has you covered with a set of SQL coding assistance features, designed to make your coding at least twice as fast. Besides the above-mentioned context-aware code completion, it includes a library of predefined SQL code snippets, which can be inserted via shortcuts in a split second. You can create templates for custom snippets just as well.

Moreover, all the queries you have executed are saved and stored in Query History, which can be accessed via the View menu > Other Windows > Query History. The History gives complete information about your queries, including the following:

• Query text
• Query size in bytes
• Date and time of execution
• Duration of execution
• Query file
• Related server
• Related database
• User who initiated the execution

From here, you can access and reuse queries at any given moment.

Conclusion

Now that our SQL Server regex guide is coming to a close, it's time for a brief summary. Although you can't enjoy all the benefits of regex here natively, you still have workarounds up your sleeve to assist you in pattern matching. What's even more important, you have tools that can make your SQL coding faster and way easier.

FAQ

REGEXP_LIKE (column_name, pattern [, match_condition ])