რედაქტირება

REGEXP_LIKE (Transact-SQL)

Applies to: SQL Server 2025 (17.x) Azure SQL Database Azure SQL Managed Instance SQL database in Microsoft Fabric

Indicates if the regular expression pattern matches in a string.

REGEXP_LIKE
(
    string_expression,
    pattern_expression [ , flags ]
)

REGEXP_LIKE requires database compatibility level 170 and above. If the database compatibility level is lower than 170, REGEXP_LIKE isn't available. Other regular expression scalar functions are available at all compatibility levels.

You can check the 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;

Note

Regular expressions are available in Azure SQL Managed Instance with the SQL Server 2025 or Always-up-to-date update policy.

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.

Note

The REGEXP_* functions support LOB types (varchar(max) and nvarchar(max)) up to 2 MB for the string_expression parameter.

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-sensitive (default true)

Return value

Boolean value. true or false.

Remarks

Cardinality estimation

To enhance the accuracy of cardinality estimation for the REGEXP_LIKE function, use the ASSUME_FIXED_MIN_SELECTIVITY_FOR_REGEXP and ASSUME_FIXED_MAX_SELECTIVITY_FOR_REGEXP query hints to adjust the default selectivity values. For more information, see Query hints.

These query hints also integrate with Cardinality estimation (CE) feedback. The CE feedback model automatically identifies queries that use the REGEXP_LIKE function where there's a significant difference between estimated and actual row counts. It then applies the appropriate selectivity hint at the query level to improve plan quality without requiring manual input.

To disable the automatic feedback behavior, enable trace flag 16268.

SARGable pattern support

REGEXP_LIKE is SARGable only when the pattern begins with the anchor ^. In addition, the anchored pattern can include:

  • A quantifier: *, +, ?, {n}, {n,}, or {n,m}. For example, ^ab+ or ^ab*.
  • Range characters, such as [0-9A-Za-z].

To escape a metacharacter, use the backslash (\).

These conditions let the query optimizer use index seek operations to improve query performance.

Regular expressions don't honor collation rules. Their behavior might be different from other string comparison functions, such as LIKE. This difference is most important on indexed columns that have language-specific collations.

For example, in Turkish collation, the characters i and I are treated distinctly even in the case-insensitive collation due to language-specific rules. For more information, see example F. Compare SARGable and non-SARGable pattern matching with Turkish collation.

Note

The term SARGable in relational databases refers to a Search ARGumentable predicate that can use an index to speed up the execution of the query. For more information, see SQL Server and Azure SQL index architecture and design guide.

Examples

A. Match values that start and end with specific characters

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$');

B. Perform a case-insensitive pattern match

Select all records from the Employees table where the first name starts with A and ends with Y, using case-insensitive mode:

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

C. Match dates using a regular expression pattern

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');

D. Match repeated character patterns

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,}');

E. Enforce data validation with CHECK constraints

Create an employees table with CHECK constraints for the 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})$'))
);

F. Compare SARGable and non-SARGable pattern matching with Turkish collation

This example demonstrates SARGable and non-SARGable use of the REGEXP_LIKE function with Turkish collation.

-- Create a temporary table with Turkish collation and and an index
CREATE TABLE #Users
(
    Username NVARCHAR (100) COLLATE Turkish_100_CI_AS_SC_UTF8 NOT NULL,
    INDEX idx_username (Username)
);

-- Insert sample data
INSERT INTO #Users (Username)
VALUES (N'i'), -- lowercase i
       (N'I'), -- uppercase dotless I
       (N'İ'), -- uppercase dotted İ
       (N'abc');

-- SARGable pattern: starts with ^ and uses quantifier
-- This will use index seek if applicable, but REGEXP_LIKE ignores collation
-- So 'i' and 'I' are treated as different characters
SELECT 'SARGable' AS PatternType,
       *
FROM #Users
WHERE REGEXP_LIKE (Username, '^i');

-- Non-SARGable pattern: does not start with ^.
-- REGEXP_LIKE performs full scan, and matches are
-- case-insensitive since 'i' flag is supplied,
-- so both 'i' and 'I' match.
SELECT 'Non-SARGable' AS PatternType,
       *
FROM #Users
WHERE REGEXP_LIKE (Username, 'i', 'i');

-- Cleanup
DROP TABLE #Users;

Related content

  • Last updated on