Edit

Share via

REGEXP_LIKE (Transact-SQL)

  • Article

Applies to: Azure SQL Database SQL database in Microsoft Fabric

Note

As a preview feature, the technology presented in this article is subject to Supplemental Terms of Use for Microsoft Azure Previews.

Indicates if the regular expression pattern matches in a string.

REGEXP_LIKE 
     (
      string_expression,
      pattern_expression [, flags ]
     )

Note

REGEXP_LIKE is available only under compatibility level 170 and above. If your database compatibility level is lower than 170, SQL Server can't find and run REGEXP_LIKE. Other regular expression scalar functions are available at all compatibility levels.

You can check compatibility level in the sys.databases view or in database properties. You can change the compatibility level of a database with the following command:

ALTER DATABASE [DatabaseName] SET COMPATIBILITY_LEVEL = 170;

Arguments

string_expression

An expression of a character string.

Can be a constant, variable, or column of character string.

Data types: char, nchar, varchar, or nvarchar.

pattern_expression

Regular expression pattern to match. Usually a text literal

Data types: char, nchar, varchar, or nvarchar. pattern_expression supports a maximum character length of 8,000 bytes. 

flags

One or more characters that specify the modifiers used for searching for matches. Type is varchar or char, with a maximum of 30 characters.

For example, ims. The default is c. If an empty string (' ') is provided, it will be treated as the default value ('c'). Supply c or any other character expressions. If flag contains multiple contradictory characters, then SQL Server uses the last character.

For example, if you specify ic the regex returns case-sensitive matching.

If the value contains a character other than those listed at Supported flag values, the query returns an error like the following example:

Invalid flag provided. '<invalid character>' are not valid flags. Only {c,i,s,m} flags are valid.
Supported flag values
Flag Description
i Case-insensitive (default false)
m Multi-line mode: ^ and $ match begin/end line in addition to begin/end text (default false)
s Let . match \n (default false)
c Case-insensitive (default true)

Return value

Boolean value. true or false.

Examples

Select all records from the EMPLOYEES table where the first name starts with A and ends with Y

SELECT * FROM EMPLOYEES WHERE REGEXP_LIKE (FIRST_NAME, '^A.*Y$');

Select all records from the ORDERS table where the order date is in February 2020.

SELECT * FROM ORDERS WHERE REGEXP_LIKE (ORDER_DATE, '2020-02-\d\d');

Select all records from the PRODUCTS table where the product name contains at least three consecutive vowels

SELECT * FROM PRODUCTS WHERE REGEXP_LIKE (PRODUCT_NAME, '[AEIOU]{3,}');

Create employees table with CHECK constraints for Email and Phone_Number columns.

DROP TABLE IF EXISTS EMPLOYEES;

CREATE TABLE EMPLOYEES (  
    ID INT IDENTITY(101,1),  
    [Name] VARCHAR(150),  
    Email VARCHAR(320)  
    CHECK (REGEXP_LIKE(Email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')),  
    Phone_Number NVARCHAR(20)  
    CHECK (REGEXP_LIKE (Phone_Number, '^(\d{3})-(\d{3})-(\d{4})$'))  
);

Related content