STRTOK¶

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, which is 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                       |
+-------------------------+

Copy

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                                  |
+---------------------------------------+

Copy

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

Copy

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

Copy

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                                   |
+----------------------------------------+

Copy

In this example, because 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              |
+-------------------+

Copy

Here is an example with an empty delimiter string:

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

Copy

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

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

Copy

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

Copy

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

Copy