Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
TOKENCOUNT (SSIS Expression)

TOKENCOUNT (SSIS Expression)

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

Returns the number of tokens in a string that contains tokens separated by the specified delimiters.

TOKENCOUNT(character_expression, delimiter_string)

character_expression

A string that contains tokens separated by delimiters.

delimiter_string

A string that contains delimiter characters. For example, "; ,” contains three delimiter characters semi-colon, a blank space, and a comma.

The following remarks apply to the TOKEN function:

  • The delimiter string can contain one or more delimiter characters.

  • Leading delimiters are skipped.

  • TOKENCOUNT works only with the DT_WSTR data type. A character_expression argument that is a string literal or a data column with the DT_STR data type is implicitly cast to the DT_WSTR data type before TOKEN performs its operation. Other data types must be explicitly cast to the DT_WSTR data type.

  • TOKENCOUNT returns 0 (zero) if the character_expression is null.

  • You can use variables and columns as arguments for this expression.

In the following example, the TOKENCOUNT function returns 3 because the string contains three tokens: “01”, “12”, “2011”.

TOKENCOUNT("01/12/2011", "/")

In the following example, the TOKENCOUNT function returns 4 because there are four tokens (“a”, “little”, “white”, “dog”).

TOKENCOUNT("a little white dog"," ")

In the following example, the TOKENCOUNT function returns 1. The function parses the input string for delimiters and since there are none in the string, it just adds the entire string as the first token.

TOKENCOUNT("a little white dog","|")

In the following example, the TOKENCOUNT function returns 4. The delimiter string in this example contains 5 delimiters. The input string contains 4 tokens: “a”, “little”, “white”, “dog”.

TOKENCOUNT("a:little|white dog","| ,.:")

In the following example, the TOKENCOUNT function returns 4. It ignores all the leading space characters.

TOKENCOUNT("        a little white dog", " ")

Community Additions

ADD
Show:
© 2015 Microsoft