mdq.Split (Transact-SQL)

Splits an input string into an array of substrings by using the specified characters as delimiters. Optionally, this function checks whether each substring matches a SQL Server data type. mdq.Split is in the mdq schema and is available only in the Master Data Services database.

Transact-SQL Syntax Conventions

Syntax

mdq.Split (input,separators,removeEmpty,tokenPattern,[mask])

Arguments

• input
  Is the input string to split. input is nvarchar(4000) with no default.

• separators
  Is the set of characters to use to split the string. separators is nvarchar(10) with default of the pipe character (|).

• removeEmpty
  Specifies to remove a substring token from the output if the token is empty or NULL. removeEmpty is bit with no default.

• tokenPattern
  Specifies the regular expression pattern to validate each token that remains after the input is split. tokenPattern is nvarchar(4000) with no default.

• mask
  Is the RegexOptions mask that specifies the behavior of the regular expression. This parameter is optional. 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.
Token	nvarchar(4000)	Is each token that remains after the input is split.
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 forward slash (/) and period (.), and then checks whether each resulting token is a valid integer.

USE MDM_Sample;
GO


SELECT * FROM mdq.Split( N'https://www.microsoft.com/office/2007', N'./', 1, N'^\d+$', 0 );

See Also

Reference

Master Data Services Functions (Transact-SQL)

Concepts

Master Data Services