mdq.RegexSplit (Transact-SQL)
Splits an input string into an array of substrings at the positions defined by a regular expression match. Optionally, this function determines whether each substring matches a separate regular expression pattern. This function uses the regular expression functionality of the Regex.Split and RegexIsMatch methods in the Microsoft .NET Framework. mdq.RegexSplit is in the mdq schema and is available only in the Master Data Services database.
.gif "Topic link icon")
Transact-SQL Syntax Conventions
Syntax
mdq.RegexSplit (input,splitPattern, [ tokenPattern, ] mask)
Arguments
input
Is the input string to split. input is nvarchar(4000) with no default.splitPattern
Is the regular expression pattern to use to split the input string. splitPattern is nvarchar(4000) with no default.tokenPattern
Is the regular expression pattern to use for matching each substring token. tokenPattern is nvarchar(4000) with no default. This parameter is optional.mask
Is the RegexOptions mask that specifies the behavior of the regular expression. For more information, see mdq.RegexMask (Transact-SQL).
Table Returned
Column name |
Column type |
Description |
|---|---|---|
Sequence |
int |
Is the sequence of the tokens in the result stream. |
Value |
nvarchar(4000) |
Is the token that matched the specified split pattern. |
IsValid |
bit |
If the token pattern is not NULL, this column shows whether the token matches the specified token pattern. |
Permissions
Requires membership in the public role.
Examples
The following example splits the input string on a comma (,), and then checks whether each resulting token has a numeric pattern.
USE MDM_Sample;
GO
SELECT * FROM mdq.RegexSplit(N'10,20,30A,5,JKL', N',', N'^\d+$', 0);
See Also
Reference
Master Data Services Functions (Transact-SQL)