Skip to content

regexp-like-transact-sql.md

Latest commit

 

History

History
97 lines (70 loc) · 2.78 KB

regexp-like-transact-sql.md

File metadata and controls

97 lines (70 loc) · 2.78 KB
title REGEXP_LIKE (Transact-SQL)
description REGEXP_LIKE Returns a Boolean value that indicates whether the text input matches the regex pattern.
author MikeRayMSFT
ms.author mikeray
ms.reviewer abhtiwar, wiassaf, randolphwest
ms.date 12/31/2024
ms.service sql
ms.subservice t-sql
ms.topic reference
dev_langs
TSQL
monikerRange =azuresqldb-current || =fabric

REGEXP_LIKE (Transact-SQL)

[!INCLUDE asdb-fabricsqldb]

[!INCLUDE preview]

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

[!INCLUDE regexp-string-expression]

pattern_expression

[!INCLUDE regexp-pattern-expression]

flags

[!INCLUDE regexp-flags-expression]

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