Categories:

String & Binary Functions (General)

STRTOK

Tokenizes a given string and returns the requested part.

If the requested part does not exist, then NULL is returned. If any parameter is NULL, then NULL is returned.

See also:

SPLIT_PART

Syntax

STRTOK(<string> [,<delimiter>] [,<partNr>])

Arguments

Required:

string

Text to be tokenized.

Optional:

delimiter

Text representing the set of delimiters to tokenize on. Each character in the delimiter string is a delimiter. If the delimiter is empty, and the string is empty, then the function returns NULL. If the delimiter is empty, and the string is non empty, then the whole string will be treated as one token. The default value of the delimiter is a single space character.

partNr

Requested token (1-based, i.e. the first token is token number 1, not token number 0). If the token number is out of range, then NULL is returned. The default value is 1.

Returns

The data type of the returned value is VARCHAR.

Usage Notes

If the string starts or is terminated with the delimiter, the system considers empty space before or after the delimiter, respectively, as a valid token.

Similar to Linux strtok(), STRTOK never returns an empty string as a token.

Examples

Here is a simple example of using STRTOK:

SELECT STRTOK('a.b.c', '.', 1);
+-------------------------+
| STRTOK('A.B.C', '.', 1) |
|-------------------------|
| a                       |
+-------------------------+

This example shows how to use multiple delimiters to return the first, second, and third tokens when the delimiters are ‘@’ and ‘.’.

SELECT STRTOK('user@snowflake.com', '@.', 1);
+---------------------------------------+
| STRTOK('USER@SNOWFLAKE.COM', '@.', 1) |
|---------------------------------------|
| user                                  |
+---------------------------------------+
SELECT STRTOK('user@snowflake.com', '@.', 2);
+---------------------------------------+
| STRTOK('USER@SNOWFLAKE.COM', '@.', 2) |
|---------------------------------------|
| snowflake                             |
+---------------------------------------+
SELECT STRTOK('user@snowflake.com', '@.', 3);
+---------------------------------------+
| STRTOK('USER@SNOWFLAKE.COM', '@.', 3) |
|---------------------------------------|
| com                                   |
+---------------------------------------+

This demonstrates what happens if you try to index past the last possible token in the string:

select strtok('user@snowflake.com.', '@.', 4);
+----------------------------------------+
| STRTOK('USER@SNOWFLAKE.COM.', '@.', 4) |
|----------------------------------------|
| NULL                                   |
+----------------------------------------+

In this example, since the input string is empty, there are 0 elements, and therefore element #1 is past the end of the string, so the function returns NULL rather than an empty string:

select strtok('', '', 1);
+-------------------+
| STRTOK('', '', 1) |
|-------------------|
| NULL              |
+-------------------+

Here is an example with an empty delimiter string:

select strtok('a.b', '', 1);
+----------------------+
| STRTOK('A.B', '', 1) |
|----------------------|
| a.b                  |
+----------------------+

Here are examples with NULL values for each of the parameters:

select strtok(NULL, '.', 1);
+----------------------+
| STRTOK(NULL, '.', 1) |
|----------------------|
| NULL                 |
+----------------------+
select strtok('a.b', NULL, 1);
+------------------------+
| STRTOK('A.B', NULL, 1) |
|------------------------|
| NULL                   |
+------------------------+
select strtok('a.b', '.', NULL);
+--------------------------+
| STRTOK('A.B', '.', NULL) |
|--------------------------|
| NULL                     |
+--------------------------+