{"id":107222,"date":"2025-07-14T16:00:00","date_gmt":"2025-07-14T16:00:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=107222"},"modified":"2026-04-17T15:23:57","modified_gmt":"2026-04-17T15:23:57","slug":"using-regex-in-sql-server-2025-complete-guide","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/featured\/using-regex-in-sql-server-2025-complete-guide\/","title":{"rendered":"Regex in SQL Server 2025: All Seven REGEXP Functions Explained"},"content":{"rendered":"\n

SQL Server 2025 introduces native regular expression support for the first time, answering years of developer requests. Seven new REGEXP functions – REGEXP_LIKE<\/code>, REGEXP_COUNT<\/code>, REGEXP_INSTR<\/code>, REGEXP_REPLACE<\/code>, REGEXP_SUBSTR<\/code>, REGEXP_MATCHES<\/code>, and REGEXP_SPLIT_TO_TABLE<\/code> – are available at database compatibility level 170. <\/strong><\/p>\n\n\n\n

These functions replace complex PATINDEX workarounds and custom CLR assemblies for pattern matching, counting, position-finding, replacement, and splitting. This article documents all seven with working T-SQL examples, plus the four flag options (i, c, m, s) that control case sensitivity and multi-line behavior.<\/strong><\/p>\n\n\n\n

Newly added in SQL Server 2025<\/a> is the answer to years of feature requests: Native Regex<\/a> support. New functions have been added that provide ways to address common string-searching requests that are faster, easier, and more performant than previously-used methods. <\/p>\n\n\n\n

This article dives into all of the new functionality with examples, details, and everything needed to make use of this new feature. <\/p>\n\n\n\n

All demos and details have been constructed using SQL Server 2025 Community Technology Preview 1.4 and may change slightly between now and when its first RTM release occurs. If any of this content changes, it will be updated to be as accurate as possible after SQL Server 2025 becomes widely available for general usage. <\/p>\n\n\n\n

Note that this functionality was also added to Azure SQL Database<\/a> as a private preview in May, 2024, so its addition to SQL Server proper does not come as a huge surprise. <\/p>\n\n\n\n

Background <\/h2>\n\n\n\n

String-searching in SQL Server<\/a> has always been a mighty hassle. Balancing performance and horribly-complex queries is a compromise that no one enjoys. <\/p>\n\n\n\n

\n
\n\n

You may also be interested in:<\/strong>
Learn the CLR assembly approach for adding regex to SQL Server 2022 (and earlier)<\/a><\/p>\n\n<\/div>\n<\/div> \n\n\n

Generally speaking, a relational database is not an ideal place to search large amounts of text. Even when leveraging features such as Full-Text Indexing<\/a>, the ability for an application to leverage speedy text-searching decreases as data becomes larger. If a service optimized for text-search can be used, such as Elasticsearch<\/a> or Azure AI Search<\/a>, then it will be far easier to deliver accurate results quickly. <\/p>\n\n\n\n

Despite that guidance, there are many reasons to search within text in SQL Server. Sometimes data is small and crunching a block of text can be done very quickly and at-scale without any latency. Other times, there is an immediate need to reduce the amount of data being returned to an application, and string-searching in the database will ultimately save time and resources. Alternatively, some analytic data systems can pre-crunch text data during data load processes, allowing the expensive text-search processes to have their results available to users when needed, without any waiting. <\/p>\n\n\n\n

What Are Regular Expressions in SQL Server?<\/h2>\n\n\n\n

Regex (aka: regular expressions)<\/a> provides a common syntax for search expressions that can be used when searching text. This may include words, letter sequences, patterns, or other combinations of text occurrences. For example, if I wanted to search this article for all places where the word \u201cnative\u201d occurs as part of another word, then a Regex pattern similar to this would be used: <\/p>\n\n\n\n

\u201cnative[a-zA-Z]\u201d or \u201c[a-zA-Z]native\u201d<\/strong> <\/p>\n\n\n\n

The first expression looks for the word \u201cnative\u201d where it is immediately followed by another letter. The second expression checks for any letter first, followed by the word \u201cnative\u201d. Regex has an extensive selection of metacharacters and patterns that can be used to describe text patterns, from simple and straightforward to those that get quite complex. <\/p>\n\n\n\n

SQL Server has had limited support for Regex within LIKE operators<\/a> for Perl Compatible Regular Expressions (PCRE)<\/a> and .NET<\/a> variants for quite a while, but this did not provide full support for the IEEE POSIX standard and functions that can make more efficient use of it. <\/p>\n\n\n\n

In April, 2024, Microsoft added full Regex support to Azure SQL Database, which provided a sneak-peek at future functionality that would be available in a future full SQL Server version. SQL Server 2025 is the recipient of these features, which will now be described in detail. <\/p>\n\n\n\n

Regex in SQL Server 2025 <\/h2>\n\n\n\n

Without any further adieu, here is a comprehensive list of new Regex functions in SQL Server, along with their most common use-cases and examples of their usage. Note that some of these functions require compatibility level 170 (SQL Server 2025) to be enabled to use them. Therefore, that change will be made here immediately, before proceeding: <\/p>\n\n\n\n

ALTER DATABASE WideWorldImportersDW SET COMPATIBILITY_LEVEL = 170; <\/pre><\/div>\n\n\n\n
REGEXP_LIKE() <\/h3>\n\n\n\n
This function returns a 1 or 0 boolean value if a regex pattern is found within a string. A simple example would be one that checks if a string contains a specific word: <\/p>\n\n\n\n
USE WideWorldImportersDW; \nSELECT  \n\n\nFROM Dimension.Customer \n\nWHERE REGEXP_LIKE(Customer.Customer, ' NY[)]'); <\/pre><\/div>\n\n\n\n
This example returns all rows from the Customer table where the Customer name includes the text \u201cNY)\u201d. The square brackets ensure that the parenthesis is read as a character literal and not as a regex modifier. <\/p>\n\n\n\n
This next example is a bit more outlandish, but returns all rows where the Primary Contact starts with a capital A and ends in a lowercase a: <\/p>\n\n\n\n
SELECT \n\n\nFROM Dimension.Customer \n\nWHERE REGEXP_LIKE(Customer.[Primary Contact], '^A.*a$'); <\/pre><\/div>\n\n\n\n
The results include 17 customers with a primary contact starting with A and ending with a: <\/p>\n\n\n\n
\"Picture<\/p>\n\n\n\n
Note that Regex is case-sensitive, regardless of the database collation used. If a database has a collation that is case-insensitive, that will not affect the contents of regex expressions when they are used. Consider this slight adjustment to the above example:   <\/p>\n\n\n\n
SELECT \n\n\nFROM Dimension.Customer \n\nWHERE REGEXP_LIKE(Customer.[Primary Contact], '^A.*A$'); <\/pre><\/div>\n\n\n\n
After that change is made to the query, the result set is now empty: <\/p>\n\n\n\n
\"Picture<\/p>\n\n\n\n
Because of this detail, it is important to ensure that any regex expressions have the correct case for any letters used. <\/p>\n\n\n\n
One final example of this function returns any rows that have at least two consecutive letter \u201cs\u201d in a row: <\/p>\n\n\n\n
SELECT \n\n\nFROM Dimension.Customer \n\nWHERE REGEXP_LIKE(Customer.Customer, '[sS]{2,}'); <\/pre><\/div>\n\n\n\n
The results include nine rows where the customer name contains at least one sequence of multiple letter \u201cs\u201d in a row: <\/p>\n\n\n\n
\"Picture<\/p>\n\n\n\n
REGEXP_COUNT() <\/h3>\n\n\n\n
This function goes a step further and returns the number of times a regex expression appears in a string. For longer strings (or interesting questions), this allows a count to be calculated without the need for iteration, complex string manipulation, recursion, or some other messy method. <\/p>\n\n\n\n
Consider the following query: <\/p>\n\n\n\n
SELECT \n                          [City Key], \n                          City, \n                          [State Province], \n                          REGEXP_COUNT(City.City, '[sS]{2,}') AS \nSequenceCount \nFROM Dimension.City \nWHERE REGEXP_COUNT(City.City, '[sS]{2,}') > 0 \n\nORDER BY REGEXP_COUNT(City.City, '[sS]{2,}') DESC; <\/pre><\/div>\n\n\n\n
This returns the number of times in a string that a sequence of at least two letter \u201cs\u201d appears in the city name. The query also filters out any rows where the count is zero and then orders by that count. The results are as follows: <\/p>\n\n\n\n
\"Picture<\/p>\n\n\n\n
Mississippi is high on the list of interesting city\/state names (no surprise there!) <\/p>\n\n\n\n
An additional parameter may be provided that gives the search a starting character, if there is a need to skip some of the string before beginning to count. For example, if there was a need to skip the first five characters of the city name, then the previous query could be modified like this: <\/p>\n\n\n\n
SELECT \n                          [City Key], \n                          City, \n                          [State Province], \n                          REGEXP_COUNT(City.City, '[sS]{2,}', 6) AS \nSequenceCount \nFROM Dimension.City \nWHERE REGEXP_COUNT(City.City, '[sS]{2,}', 6) > 0 \n\nORDER BY REGEXP_COUNT(City.City, '[sS]{2,}', 6) DESC; <\/pre><\/div>\n\n\n\n
The 3rd<\/sup> parameter is optional and tells the function to begin searching the string at the character position provided. In this example, the regex expression is searched for starting at character 6, which reduces the matches in the overall result set: <\/p>\n\n\n\n
\"Picture<\/p>\n\n\n\n
The rows returned are reduced from 1,752 to 590. <\/p>\n\n\n\n
Another example shows how a list of cities can be retrieved that begin with the word \u201cNew\u201d and include a space after that word: <\/p>\n\n\n\n
SELECT \n\nFROM Dimension.City \n\nWHERE REGEXP_COUNT(City.City, '^New\\s') > 0; <\/pre><\/div>\n\n\n\n
The results show 1,002 cities that begin with the word \u201cNew\u201d: <\/p>\n\n\n\n
\"Picture<\/p>\n\n\n\n
REGEXP_INSTR() <\/h3>\n\n\n\n
This function can return either the start or end position of a matched regular expression. This provides superior functionality to commonly used string-searching functions, such as PATINDEX or CHARINDEX, which only return the starting position for a given string pattern. <\/p>\n\n\n\n
A simple example shows how the position of the word \u201cNew\u201d can be found within a string: <\/p>\n\n\n\n
SELECT \n                          City.City, \n                                City.[State Province], \n                                REGEXP_INSTR(City.City, 'New') AS \nStringLocation \nFROM Dimension.City \n\nWHERE REGEXP_INSTR(City.City, 'New') > 0 <\/pre><\/div>\n\n\n\n
The results provide the starting location in the string, but only in strings where it is found: <\/p>\n\n\n\n
\"Picture<\/p>\n\n\n\n
The first optional parameter allows the starting position to search to be provided. This adjusted query also searches for the string \u201cNew\u201d, but begins at character 2: <\/p>\n\n\n\n
SELECT \n                          City.City, \n                          City.[State Province], \n                          REGEXP_INSTR(City.City, 'New', 2) AS \nStringLocation \nFROM Dimension.City \n\nWHERE REGEXP_INSTR(City.City, 'New', 2) > 0 <\/pre><\/div>\n\n\n\n
The results show all cases where the word \u201cNew\u201d is in the string, but not at the start: <\/p>\n\n\n\n
\"Picture<\/p>\n\n\n\n
The second optional parameter specifies which occurrence of the string pattern should be located. This updated example will return the position of the second occurrence of the word \u201cNew\u201d within a city name: <\/p>\n\n\n\n
SELECT \n                          City.City, \n                          City.[State Province], \n                          REGEXP_INSTR(City.City, 'New', 1, 2) AS \nStringLocation \nFROM Dimension.City \n\nWHERE REGEXP_INSTR(City.City, 'New', 1, 2) > 0 <\/pre><\/div>\n\n\n\n
The results are a very short list of cities that happen to have the word \u201cNew\u201d appear in them multiple times: <\/p>\n\n\n\n
\"Picture<\/p>\n\n\n\n
The third parameter specifies whether the function should return the starting position for the regex pattern (0) or the ending position (1). The previous query can be adjusted to return the end position of the second occurrence of the word \u201cNew\u201d like this: <\/p>\n\n\n\n
SELECT \n                          City.City, \n                          City.[State Province], \n                          REGEXP_INSTR(City.City, 'New', 1, 2, 1) AS\nStringLocation \nFROM Dimension.City \n\nWHERE REGEXP_INSTR(City.City, 'New', 1, 2, 1) > 0 <\/pre><\/div>\n\n\n\n
The rows returned are the same, but the string location identified has shifted to the end: <\/p>\n\n\n\n
\"Picture<\/p>\n\n\n\n
More outlandish regular expressions can be applied, showing the flexibility available in these new functions: <\/p>\n\n\n\n
SELECT \n                          City.City, \n                          City.[State Province], \n                          REGEXP_INSTR(City.City, ' N.*?w', 1, 1, 0,\n'i') AS StringLocation \nFROM Dimension.City \n\nWHERE REGEXP_INSTR(City.City, ' N.*?w', 1, 1, 0, 'i') > 0 <\/pre><\/div>\n\n\n\n
That work of art will return the start position in any city where the second (or later) word starts with \u201cN\u201d and is followed by a \u201cW\u201d later in the string. The \u201ci” flag at the end makes the regular expression case-insensitive, which can be a handy flag for databases that use case-insensitive collations. <\/p>\n\n\n\n
REGEXP_REPLACE() <\/h3>\n\n\n\n
This function takes the search capabilities introduced thus far and extends them to allow a matched string segment to be replaced by a new string. This mirrors how the classic REPLACE() function works, but using regex instead. <\/p>\n\n\n\n
The simplest possible example will replace the string \u201cNew\u201d with \u201cOld\u201d, whenever the search string is found: <\/p>\n\n\n\n
SELECT \n                          City.City, \n                          City.[State Province], \n                          REGEXP_REPLACE(City.City, 'New', 'Old') AS\nOldCityName \nFROM Dimension.City \n\nWHERE REGEXP_COUNT(City.City, 'New') > 0; <\/pre><\/div>\n\n\n\n
The slightly comical results are as follows: <\/p>\n\n\n\n
\"Picture<\/p>\n\n\n\n
The remaining parameters for this function are similar to REGEXP_INSTR(). The first provides a starting position from where to search. Consider the following example: <\/p>\n\n\n\n
SELECT \n                          City.City, \n                          City.[State Province], \n                          REGEXP_REPLACE(City.City, 'New|Old', 'Mid',\n 5) AS MidCityName \nFROM Dimension.City \n\nWHERE REGEXP_COUNT(City.City, 'New|Old', 5) > 0; <\/pre><\/div>\n\n\n\n
Starting at character 5, any occurrences of \u201cNew\u201d or \u201cOld are replaced with \u201cMid\u201d. Note that this is case sensitive and lowercase versions of these words will not be replaced. Here is a sample of the results: <\/p>\n\n\n\n
\"Picture<\/p>\n\n\n\n
Similar to REGEXP_INSTR, the occurrence can be specified so that text is only replaced for the Nth occurrence of the regular expression. The following T-SQL<\/a> replaces \u201cNew\u201d or \u201cOld\u201d with \u201cMid\u201d, but only for the second occurrence: <\/p>\n\n\n\n
SELECT \n                          City.City, \n                          City.[State Province], \n                          REGEXP_REPLACE(City.City, 'New|Old', 'Mid', \n1, 2, 'i') AS MidCityName \nFROM Dimension.City \n\nWHERE REGEXP_REPLACE(City.City, 'New|Old', 'Mid', 1, 2, 'i') <> City.City; <\/pre><\/div>\n\n\n\n
The result set is small, but illustrates the effect of the script effectively: <\/p>\n\n\n\n
\"Picture<\/p>\n\n\n\n
In each case, the first occurrence of New\/Old was left alone, and the second occurrence was modified. <\/p>\n\n\n\n
REGEXP_SUBSTR() <\/h3>\n\n\n\n
The next regex function added to SQL Server, REGEXP_SUBSTR, finds a string pattern and returns the substring that matches it. This can be particularly useful for extracting portions of money, phone numbers, email addresses, websites, and other text that has consistent or important delimiters. <\/p>\n\n\n\n
This example takes a customer name and extracts the contents of the parenthesis within that name: <\/p>\n\n\n\n
SELECT \n                          Customer.[Customer Key], \n                          Customer.Customer, \n                          REGEXP_SUBSTR(Customer.Customer, '\\\n(([^)]+)\\)') AS OfficeLocation \nFROM Dimension.Customer; <\/pre><\/div>\n\n\n\n
The results include only the text between the parenthesis, excluding extra parentheses (if any were to exist): <\/p>\n\n\n\n
\"Picture<\/p>\n\n\n\n
If the substring is not found, then NULL is returned, as can be observed in the first row of the result set. In this next example, the letter code that prefixes some supplier codes is returned: <\/p>\n\n\n\n
SELECT \n                          Supplier.[Supplier Key], \n                          Supplier.[Supplier Reference], \n                          REGEXP_SUBSTR(Supplier.[Supplier Reference],\n'[a-zA-Z]+') AS LetterCode \nFROM Dimension.Supplier; <\/pre><\/div>\n\n\n\n
The results are either the letter code or an empty string, depending on whether a letter prefix exists: <\/p>\n\n\n\n
\"Picture<\/p>\n\n\n\n
The results show that any value with any number of letters as a prefix will return just that prefix. The plus sign instructs the regex to return one or more matching characters. If the string is not found, then NULL is returned. Note the slight difference in this query variant: <\/p>\n\n\n\n
SELECT \n                          Supplier.[Supplier Key], \n                          Supplier.[Supplier Reference], \n                          REGEXP_SUBSTR(Supplier.[Supplier Reference],\n'[a-zA-Z]*') AS LetterCode \nFROM Dimension.Supplier; <\/pre><\/div>\n\n\n\n
In this version, the plus is replaced with an asterisk, which indicates that the regex return zero or more matching characters. This results in an empty string being returned instead of NULL, which may be desirable for some applications. <\/p>\n\n\n\n
REGEXP_MATCHES() <\/h3>\n\n\n\n
This function is ideal when detailed information is needing about regex string patterns and matching. The required parameters are a string expression and a regex pattern to match. The result set includes details about each match, its location, and which substrings were matched to the pattern. <\/p>\n\n\n\n
This amount of information will sometimes be overkill, but will be especially helpful in scenarios such as these: <\/p>\n\n\n
\n