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.

Topic link iconTransact-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'http://www.microsoft.com/office/2007', N'./', 1, N'^\d+$', 0 );

See Also

Reference

Master Data Services Functions (Transact-SQL)

Concepts

Master Data Services