Basic Regex Emulator for SQL Server

Problem

Regular expressions (REGEX) let you adaptively investigate, employ, and authenticate text data. This makes it easy to search for simple or complex string patters. There is no direct way to do this in SQL Server, but in this article we look at some SQL functions you can create to emulate regex like functionality.

Solution

The easiest way to have all the features of REGEX is to create a Common Language Runtime (CLR) integration with .NET language as a SQL Server DLL. However, this is not an option in some environments due to security issues. So, in this article we are going to look at how you can build functions to handle REGEX within SQL Server.

SQL Server 2025 REGEX Functions

As part of the new SQL Server 2025, Microsoft is debuting a REGEX emulator to bolster T-SQL regex functions:

• REGEXP_LIKE: This function returns TRUE if a string matches a regular expression pattern, or FALSE otherwise.
• REGEXP_COUNT: This function returns the number of times a regular expression pattern matches in a string.
• REGEXP_INSTR: This function returns the starting or ending position, based on the specified option, of the given occurrence of a regular expression pattern in a string.
• REGEXP_REPLACE: This function returns a modified string replaced by a ‘replacement string’, where occurrence of the regular expression pattern found.
• REGEXP_SUBSTR: This function returns a substring that matches a regular expression pattern from a string.

Regex Emulator Using T-SQL

The idea is to use the normal SQL LIKE operator to do the job to convert the Regex expression to something that the LIKE operator understands what is expected to be done. I will start emulating REGEXP_LIKE for only very basic rules. Complex regex expression can be developed using the same approach.

Note: some regex expressions are the same as the ones that the LIKE operators use.

Regex Match User Defined Function

This is the function that transforms a regular expression to a statement that the operator LIKE will recognize:

-- MSSQLTips.com (T-SQL)
CREATE OR ALTER FUNCTION [dbo].[ufnRegexMatch] 
            (@ToChk nvarchar(MAX)
            ,@Regex nvarchar(MAX))
RETURNS bit
AS
BEGIN
   DECLARE @Result bit = 1;
 
   DECLARE @Prefix nvarchar(1) = '%';
   IF SUBSTRING(@Regex,1,1) = '^' BEGIN
      SET @Regex = SUBSTRING(@Regex,2,LEN(@Regex));
      SET @Prefix = '';
   END
 
   DECLARE @Sufix nvarchar(1) = '%';
   IF RIGHT(@Regex,1) = '$' BEGIN
      SET @Regex = LEFT(@Regex,LEN(@Regex) - 1);
      SET @Sufix = '';
   END
 
   SET @Regex = REPLACE(@Regex,'*','%');
 
   DECLARE @MinLenght nvarchar(1) = '';
   IF @Regex LIKE '%,}%' BEGIN
      SET @Regex = REPLACE(@Regex,',}','}');
      SET @MinLenght = '%'; 
   END
 
   SET @Regex = REPLACE(@Regex,'[^','[NOT');
   SET @Regex = REPLACE(@Regex,'\.','DÖT');
   SET @Regex = REPLACE(@Regex COLLATE Latin1_General_BIN,'\d','[0-9]');
   SET @Regex = REPLACE(@Regex COLLATE Latin1_General_BIN,'\D','[NOT0-9]');
 
   SET @Regex = CASE WHEN @Regex COLLATE Latin1_General_BIN LIKE '%[[\w]%' 
                 THEN REPLACE(@Regex COLLATE Latin1_General_BIN,'\w','a-zA-Z0-9_')
                 ELSE REPLACE(@Regex COLLATE Latin1_General_BIN,'\w','[a-zA-Z0-9_]')
             END;
 
   SET @Regex = CASE WHEN @Regex COLLATE Latin1_General_BIN LIKE '%[[\W]%' 
                 THEN REPLACE(@Regex COLLATE Latin1_General_BIN,'\W','NOTa-zA-Z0-9_')
                 ELSE REPLACE(@Regex COLLATE Latin1_General_BIN,'\W','[NOTa-zA-Z0-9_]')
             END;
 
   DECLARE @delimiter char(1) = ' ';
   DECLARE @start int = 1;
   DECLARE @position int;
   DECLARE @length int;
   DECLARE @grp int;
 
   DECLARE @splitParts 
      TABLE (Part nvarchar(MAX)
            ,Grp int IDENTITY
            ,Rep nvarchar(MAX));
 
   WHILE @start <= LEN(@Regex) BEGIN
      SET @position = PATINDEX('%[\()}^$.*+|]%', SUBSTRING(@Regex, @start, LEN(@Regex) - @start + 1));
 
      IF @position > 0
         BEGIN
            SET @length = @position;
 
            IF SUBSTRING(@Regex, @start, 1) <> '\' 
               INSERT INTO  @splitParts 
                        (Part) 
                  VALUES (REPLACE(SUBSTRING(@Regex, @start, @length),')',''));
 
            SET @start = @start + @position;
         END
      ELSE
         BEGIN
            INSERT INTO  @splitParts 
                     (Part)
               VALUES ('%' + SUBSTRING(@Regex, @start, LEN(@Regex) - @start + 1) + '%');
 
            BREAK;
         END
   END
 
   UPDATE @splitParts
      SET [Rep] = [dbo].[ufnRegexExtract] ([Part],'{','}');
 
   UPDATE @splitParts
      SET [Part] = [dbo].[ufnRegexRepeat] ([Part],'{','}')
      WHERE [Rep] IS NOT NULL;
 
   DECLARE @SqlLike nvarchar(MAX) = '';
 
   SELECT @SqlLike += [Part] FROM @splitParts WHERE LEN([Part]) > 0;
 
   SET @SqlLike = CONCAT(@Prefix,REPLACE(@SqlLike,'[NOT','[^'),@Sufix);
   SET @SqlLike = REPLACE(@SqlLike,'\','');
   SET @SqlLike = REPLACE(@SqlLike,'(','');
   SET @SqlLike = REPLACE(@SqlLike,'DÖT','.');
 
   WHILE @SqlLike LIKE '%]+%' BEGIN
   
      SET @position = CHARINDEX( ']+',@SqlLike);
 
      DECLARE @RightPos int = CHARINDEX(SUBSTRING(@SqlLike,@position + 2,1), REVERSE(@ToChk));
      DECLARE @LeftPos int = LEN(@ToChk) - @RightPos + 1;
 
      IF SUBSTRING(@ToChk,1,@LeftPos - 1) LIKE '%' + REPLACE(LEFT(@SqlLike,@position),'[','[^') + '%' BEGIN
         SET @Result = 0;
         BREAK;
      END
   
      SET @SqlLike = REPLACE(@SqlLike,LEFT(@SqlLike,@position + 2),'');
      SET @ToChk = REPLACE(@ToChk,SUBSTRING(@ToChk,1,@LeftPos),'');
   END
 
   SET @SqlLike += @MinLenght;
 
   IF PATINDEX(@SqlLike, @ToChk COLLATE Latin1_General_BIN) > 0 AND @Result = 1
      SET @Result = 1;
   ELSE
      SET @Result = 0;
 
   RETURN @Result;
END
GO

Regex Extract User Defined Function

This function extracts the number of repeats inside parenthesis or any other character chosen.

-- MSSQLTips.com (T-SQL)
CREATE OR ALTER FUNCTION [dbo].[ufnRegexExtract] 
            (@Source nvarchar(MAX)
            ,@Starts nvarchar(1)
            ,@Ends nvarchar(1))
RETURNS nvarchar(MAX)
AS
BEGIN
   DECLARE  @Result nvarchar(MAX)
         ,@PosIni int
         ,@PosFin int;
 
   SET @PosIni = CHARINDEX(@Starts,@Source) + 1; 
   SET @PosFin = CHARINDEX(@Ends,@Source); 
 
   IF @PosIni < @PosFin
      SET @Result = SUBSTRING(@Source,@PosIni,@PosFin - @PosIni);
 
   RETURN @Result;
END
GO

Regex Repeat User Defined Function

This function will repeat the expression as many times as specified.

-- MSSQLTips.com (T-SQL)
CREATE OR ALTER FUNCTION [dbo].[ufnRegexRepeat] 
            (@Source nvarchar(MAX)
            ,@Starts nvarchar(1)
            ,@Ends nvarchar(1))
RETURNS nvarchar(MAX)
AS
BEGIN
   DECLARE  @Result nvarchar(MAX)
         ,@PosIni int
         ,@PosFin int;
 
   SET @PosIni = CHARINDEX(@Starts,@Source) + 1; 
   SET @PosFin = CHARINDEX(@Ends,@Source); 
 
   IF @PosIni < @PosFin BEGIN
      SET @Result = SUBSTRING(@Source,@PosIni,@PosFin - @PosIni);
 
      DECLARE @Pos int =  @PosIni - 2;
 
      DECLARE @Rep nvarchar(100);
 
      IF SUBSTRING(@Source,@Pos,1) = ']' 
         BEGIN
            WHILE SUBSTRING(@Source,@Pos,1) <> '[' BEGIN
               SET @Pos -= 1;
            END
 
            SET @Rep = SUBSTRING(@Source,@Pos,LEN(@Source));
            SET @Source = REPLACE(@Source,@Rep,'') + REPLICATE(REPLACE(@Rep,CONCAT('{',@Result,'}'),''),@Result);
         END
      ELSE IF SUBSTRING(@Source,@Pos-1,1) = '\' 
         BEGIN
            SET @Rep = SUBSTRING(@Source,PATINDEX('%[\]%',@Source),@Pos)
            SET @Source = REPLACE(@Source,@Rep,'') + REPLICATE(REPLACE(@Rep,CONCAT('{',@Result,'}'),''),@Result);
         END
      ELSE
         BEGIN
            SET @Rep = SUBSTRING(@Source,@Pos,1)
            SET @Source = REPLACE(@Source,CONCAT('{',@Result,'}'),'') + REPLICATE(@Rep,@Result - 1);
         END
   END
 
   RETURN @Source;
END
GO

Regex Like Social Security Number (SSN) Validation

The Regex expression that I chose for SSN is ‘^[0-8]\d{2}-\d{2}-\d{4}$’, and there are more sophisticated rules that also check additional features like the constraints of SSN that do not start with three consecutive zeroes or sixes. I chose the simplest one.

Regex to Check SSN

^[0-8]\d{2}-\d{2}-\d{4}$

Let’s test this out with some examples and I will indicate where it is valid or not to see if the regex matches.

-- MSSQLTips.com (T-SQL)
DECLARE @Table 
   TABLE (valid bit
         ,SSN nvarchar(150)
         ,chk bit);
 
INSERT INTO @Table
         (valid,SSN)
  VALUES (0,'123-45-6789'),
         (0,'123 45 6789'),
         (0,'333-22-4444'),
         (0,'aaa-bbb-cccc'),
         (0,'900-58-4564'),
         (0,'999-58-4564'),
         (0,'000-45-5454');
 
UPDATE @Table
   SET chk = dbo.ufnRegexMatch(SSN,'^[0-8]\d{2}-\d{2}-\d{4}$');
 
SELECT * FROM @Table WHERE valid <> chk;
GO

Running the code we can see the valid SSNs below based on the regex expression. SSNs starting with 000 are not valid, so additional tweaking should be done. Using the NOT LIKE operator could help solve this issue.

Regex Like Email Validation

Let’s try to validate email address with regex.

Regex to Check Email

^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$

Let’s do a test.

-- MSSQLTips.com (T-SQL)
DECLARE @Table 
   TABLE (valid bit
         ,email nvarchar(150)
         ,chk bit);
 
INSERT INTO @Table
         (valid,email)
  VALUES (0,'abc.def@mssqltips.c'),
         (0,'abc.def@mssqltips.cc'),
         (0,'abc.def@mssqltips#archive.com'),
         (0,'abc.def@mssqltips-archive.com'),
         (0,'abc.def@mssqltips'),
         (0,'abc.def@mssqltips.org'),
         (0,'abc.def@mssqltips..com'),
         (0,'abc.def@mssqltips.com'),
         (0,''),
         (0,'abc@mssqltips.com'),
         (0,'abc.support@mssqltips.com'),
         (0,'.abc@mssqltips.com'),
         (0,'abc..@mssqltips.com'),
         (0,'abc@mssqltips.c'),
         (0,'abc@support@mssqltips.com'),
         (0,'abc.support@mssql_tips.com'),
         (0,'+abc@mssqltips.com'),
         (0,'abc Support@mssqltips.com'),
         (0,'abc@mssql tips.com'),
         (0,NULL),
         (0,'456@mssqltips.com'),
         (0,'@mssqltips.com');
 
UPDATE @Table
   SET chk = dbo.ufnRegexMatch(email,'^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');
 
SELECT * FROM @Table WHERE valid <> chk;
GO

Running the code, we can see this shows the following are valid.

On closer look, we can see the following should not be considered valid.

The function did not work for those, as expected—two consecutive dots, emails starting with a dot, a plus sign, and @ are not specified in the Regular expression to be detected. This should be covered by the NOT LIKE operator.

Regex Like Phone Validation

The Regex expression that I chose to check if a phone number is well-formed is as follows where I am accepting the numbers separated by a dot, hyphen, or space.

Regex to Check Phone Number

^\d{3}[-. ]\d{3}[-. ]\d{4}$

Test regex code with examples.

-- MSSQLTips.com (T-SQL)
DECLARE @Table 
   TABLE (valid bit
         ,phone nvarchar(150)
         ,chk bit);
 
INSERT INTO @Table
         (valid,phone)
  VALUES (0,'123-456-7890'),
         (0,'123 456-7890'),
         (0,'123.456.7890'),
         (0,'123 456 7890'),
         (0,'123-456-78a0'),
         (0,'tst 123-4567'),
         (0,'1234-456-7890');
 
UPDATE @Table
   SET chk = dbo.ufnRegexMatch(phone,'^\d{3}[-. ]\d{3}[-. ]\d{4}$');
 
SELECT * FROM @Table WHERE valid <> chk;
GO

Here are the results that are valid based on our regex expression.

Regex Like Other Examples

-- MSSQLTips.com (T-SQL)
-- check date format YYYY-MM-DD
SELECT [dbo].[ufnRegexMatch] ('2025-01-16','^(\d{4})-(\d{2})-(\d{2})$') AS VerifyDate;
 
-- check Brazil SSN Like Number
SELECT [dbo].[ufnRegexMatch] ('123.456.789-01','^\d{3}.\d{3}.\d{3}-\d{2}$') AS VerifyBrazilianSSNLike;
 
-- check that string contains letters
SELECT [dbo].[ufnRegexMatch] ('Lorem ipsum','^[e]$') AS VerifyContainsLetterE;
 
-- check for a valid zip +4 code
SELECT [dbo].[ufnRegexMatch] ('90210-1234','^\d{5}(-\d{4})$') AS VerifyZipCode;
GO

Running the above code, all strings match the regex expression.

Next Steps

• This is only a starting point to emulate a Regular expression statement and certainly does not cover all possibilities.
• The next step is to construct, using the same approach, the statements that will be covered by the NOT LIKE operator, and also the Look ahead and Look behind Regex functions.
• Review this Regex for SQL Server tutorial  
• More resources
  • WIKIPEDIA – Regular Expression
  • MICROSOFT – Introducing Regular Expression (Regex) Support in Azure SQL DB
  • MICROSOFT – Regular Expression Language, Quick Reference
  • MICROSOFT – LIKE (Transact-SQL)
  • regex101 – Regex expression online tester
  • ihateregex.io – Regex expression online tester

Sebastiao Pereira

Sebastião Pereira has over 40 years of experience in database development including T-SQL, algorithm design, machine learning and bringing innovative mathematical formulas to SQL Server. He started his career at a transnational fast-moving consumer goods (FMCG) company as an employee then later transitioning into a consultant role. He eventually founded his own company to develop software solutions for the healthcare industry.  Sebastião is a respected award-winning author on MSSQLTips.com extending SQL Server capabilities beyond traditional workloads.

• MSSQLTips Awards
  • Author of the Year – 2025
  • Trendsetter (25+ tips) – 2025
  • Rookie of the Year – 2024