Returns
a token (substring) from a string based on the specified delimiters that
separate tokens in the string and the number of the token that denotes which
token to be returned. The function returns a substring of the provided string.
The substring is defined in the following way: we define the character
expression and the delimiter string that will divide the character expression
into many substrings or tokens. The result of the function will be the
substring whose position is defined by the occurrence number.
Syntax
TOKEN(character_expression,
delimiter_string, occurrence)
|
The arguments
of the TOKEN function are:
Ø Character_expression – represents the string where we will
search the string we are looking for.
Ø Delimiter_string – represents the string that holds one or
more delimiters.
Ø Occurrence – represents the number of the token that will be
returned.
Let’s see an
example
Suppose we have
a excel file and a column (State name) have the value with comma separated and
we need to load state code in one column and state name in another column.
State name
|
Bihar ,BR
|
Utter Pradesh ,UP
|
Maharashtra ,MH
|
Jharkhand ,JH
|
We want to load
this as below
State Name
|
State code
|
Bihar
|
BR
|
Uttar Pradesh
|
UP
|
Maharashtra
|
MH
|
Jharkhand
|
JH
|
There are multiple ways to get this
result.
The easy way using token function.
Taking data flow task and source as excel file.
In drive column
I am using the Token function. We know that that before comma is the state name and
after it states code.
To see
the result I am taking Multicast.
If the
value of <occurrence> parameter is higher than the total number of tokens
in the string, the function returns NULL.
Let’s
see in the above example
Column
has only 2 comma-separated value. Occurrence I am using 5 so it will return a null value.
TOKEN
returns a null result if the character_expression is null.
TOKEN
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.
No comments:
Post a Comment
If you have any doubt, please let me know.