Skip to content

regexp-substr-transact-sql.md

Latest commit

 

History

History
95 lines (64 loc) · 3.53 KB

regexp-substr-transact-sql.md

File metadata and controls

95 lines (64 loc) · 3.53 KB
title REGEXP_SUBSTR (Transact-SQL)
description REGEXP_SUBSTR returns one occurrence of a substring of a string that matches the regular expression pattern.
author MikeRayMSFT
ms.author mikeray
ms.reviewer abhtiwar, wiassaf, randolphwest
ms.date 11/18/2025
ms.service sql
ms.subservice t-sql
ms.topic reference
ms.custom
ignite-2025
dev_langs
TSQL
monikerRange =sql-server-ver17 || =sql-server-linux-ver17 || =azuresqldb-current || =azuresqldb-mi-current || =fabric-sqldb

REGEXP_SUBSTR (Transact-SQL)

[!INCLUDE sqlserver2025-asdb-asmi-fabricsqldb]

Returns one occurrence of a substring of a string that matches the regular expression pattern. If no match is found, it returns NULL.

REGEXP_SUBSTR
(
    string_expression,
    pattern_expression [ , start [ , occurrence [ , flags [ , group ] ] ] ]
)

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

[!INCLUDE regexp-string-expression]

pattern_expression

[!INCLUDE regexp-pattern-expression]

start

Specify the starting position for the search within the search string. Optional. Type is int or bigint.

The numbering is 1-based, meaning the first character in the expression is 1 and the value must be >= 1. If the start expression is less than 1, returns error. If the start expression is greater than the length of string_expression, the function returns NULL. The default is 1.

occurrence

An expression (positive integer) that specifies which occurrence of the pattern expression within the source string to be searched or replaced. Default is 1. Searches at the first character of the string_expression. For a positive integer n, it searches for the nth occurrence beginning with the first character following the first occurrence of the pattern_expression, and so forth.

flags

[!INCLUDE regexp-flags-expression]

group

Specifies which capture group (subexpression) of a pattern_expression determines the position within string_expression to return. The capture group (subexpression) is a fragment of pattern enclosed in parentheses and can be nested.

The capture groups are numbered in the order in which their left parentheses appear. The data type of group is int and the value must be greater than or equal to 0, and must not be greater than the number of capture groups (subexpressions) in pattern_expression. The default group value is 0, which indicates that the position is based on the string that matches the entire pattern.

Return value

String.

Examples

Extract the domain name from an email address.

SELECT REGEXP_SUBSTR(EMAIL, '@(.+)$', 1, 1, 'i', 1) AS DOMAIN
FROM CUSTOMERS;

Find the first word in a sentence that starts with a vowel.

SELECT REGEXP_SUBSTR(COMMENT, '\b[aeiou]\w*', 1, 1, 'i') AS WORD
FROM FEEDBACK;

Get the last four digits of a credit card number.

SELECT REGEXP_SUBSTR(CARD_NUMBER, '\d{4}$') AS LAST_FOUR
FROM PAYMENTS;

Related content