Snowflake SQL Tutorial 67 - REPLACE, TRANSLATE, SPLIT, SPLIT_PART, STRTOK - Strings 4

Snowflake SQL · Episode 67

In this post we are going to cover REPLACE, TRANSLATE, SPLIT, SPLIT_PART and STRTOK in Snowflake.

These five functions change a string or break it into pieces. REPLACE swaps one piece of text for another. TRANSLATE swaps single characters, and deletes the ones it has no replacement for. SPLIT, SPLIT_PART and STRTOK cut a string into fields at a separator.

We will start with a table where every column is text and every column is wrong in a different way, and clean it one function at a time. By the end a column of $4,200.00 will be a real number.

Every query below was run on a real Snowflake account, and every result you see is the output it gave back.

Episode 67 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

On this page

1. Let's create some sample data
2. Let's start with REPLACE
3. Now let's nest two REPLACE calls
4. What happens if we leave the third argument off?
5. Now let's turn that text into a number
6. Now let's try TRANSLATE
7. Let's use that deletion to strip the amount in one call
8. So where do REPLACE and TRANSLATE really differ?
9. Now let's point TRANSLATE at the phone numbers
10. Let's split the region column with SPLIT
11. Now let's try SPLIT_PART instead
12. Let's take the email apart with SPLIT_PART
13. What happens when a field is empty?
14. Can STRTOK take a whole set of delimiters?
15. One last thing: let's clean the whole table in one statement

Let's create some sample data

Everything on this page works on one table, SALES_RAW. It is the sort of thing that arrives from a spreadsheet. Every column is text, and every column is wrong in its own way.

Copy this into a Snowflake worksheet and run it once.

Sample data

-- Sample data for this post.
-- Run this once. SALES_RAW is deliberately dirty: every column is text, the
-- amounts carry currency symbols and thousands separators, the names carry
-- stray spaces and inconsistent case, and the phone numbers are punctuated
-- several different ways. Nothing on this page modifies it.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

CREATE OR REPLACE TABLE SALES_RAW (
    ROW_ID        INT,
    CUSTOMER_TXT  VARCHAR,
    AMOUNT_TXT    VARCHAR,
    DATE_TXT      VARCHAR,
    REGION_TXT    VARCHAR,
    PHONE_TXT     VARCHAR
);

INSERT INTO SALES_RAW VALUES
    (1, '  Northwind Traders ', '$4,200.00', '2023-01-12',  'us-east',    '(704) 555-0142'),
    (2, 'blue ridge supply',    '890.50',    '01/28/2023',  'US-EAST',    '704-555-0198'),
    (3, 'KESTREL ANALYTICS',    '$6,150',    '14-Feb-2023', 'ca-west',    '+1 416 555 0117'),
    (4, 'Harbour Foods  ',      '2,310.75',  '2023-03-09',  'CA-West',    '604.555.0163'),
    (5, 'Alpine Logistics',     'N/A',       '2023-04-05',  'eu-central', '+41 44 555 0188'),
    (6, ' Delta Print Co',      '$640.00',   '05/21/2023',  'us-east',    '919-555-0124'),
    (7, 'Meridian Health',      '9,250.00',  '14-Aug-2023', 'US-East',    '(617) 555-0179'),
    (8, 'copper kettle ltd',    '$1,080',    '2024-09-29',  'uk-north',   '+44 161 555 0133');

-- The EMPLOYEES table later on this page is the one from Episode 1.
-- You already have it if you have been following along.

Now let's look at what we just created.

Query

SELECT ROW_ID, CUSTOMER_TXT, AMOUNT_TXT, REGION_TXT, PHONE_TXT
FROM SALES_RAW
ORDER BY ROW_ID;

Result8 rows · 5 columns

ROW_ID	CUSTOMER_TXT	AMOUNT_TXT	REGION_TXT	PHONE_TXT
1	Northwind Traders	$4,200.00	us-east	(704) 555-0142
2	blue ridge supply	890.50	US-EAST	704-555-0198
3	KESTREL ANALYTICS	$6,150	ca-west	+1 416 555 0117
4	Harbour Foods	2,310.75	CA-West	604.555.0163
5	Alpine Logistics	N/A	eu-central	+41 44 555 0188
6	Delta Print Co	$640.00	us-east	919-555-0124
7	Meridian Health	9,250.00	US-East	(617) 555-0179
8	copper kettle ltd	$1,080	uk-north	+44 161 555 0133

Eight rows. Have a look at what is wrong with each column.

• CUSTOMER_TXT has stray spaces at the ends, and the names are typed in three different cases. Some are in title case, some are all lower case, and some are in capitals.
• AMOUNT_TXT has currency symbols, thousands separators, and one value that is not a number at all.
• REGION_TXT is really two fields joined by a hyphen, and the same code is written in whatever case somebody typed.
• PHONE_TXT is punctuated four different ways.

By the end of the page every one of those is a clean column, using nothing but the five functions in the title.

Let's start with REPLACE

REPLACE takes three things: the value, the substring to look for, and what to put in its place.

Let's strip the commas out of the amount, and then the dollar signs, in two separate columns.

Query

SELECT ROW_ID, AMOUNT_TXT,
       REPLACE(AMOUNT_TXT, ',', '') AS COMMAS_GONE,
       REPLACE(AMOUNT_TXT, '$', '') AS DOLLARS_GONE
FROM SALES_RAW
ORDER BY ROW_ID;

Result8 rows · 4 columns

ROW_ID	AMOUNT_TXT	COMMAS_GONE	DOLLARS_GONE
1	$4,200.00	$4200.00	4,200.00
2	890.50	890.50	890.50
3	$6,150	$6150	6,150
4	2,310.75	2310.75	2,310.75
5	N/A	N/A	N/A
6	$640.00	$640.00	640.00
7	9,250.00	9250.00	9,250.00
8	$1,080	$1080	1,080

Neither column is finished. COMMAS_GONE still has a $ on the rows that had one, and DOLLARS_GONE still has a comma on the rows that had one. The rows that had neither are untouched in both.

One thing that is easy to miss here. REPLACE replaces every occurrence, not just the first one. There is no occurrence argument to pass.

Now let's nest two REPLACE calls

We want both characters gone in one column, and REPLACE returns a string. So the next REPLACE can take that string as its input.

Let's wrap one call inside the other. One call per character we want gone.

Query

SELECT ROW_ID, AMOUNT_TXT,
       REPLACE(REPLACE(AMOUNT_TXT, '$', ''), ',', '') AS BOTH_GONE
FROM SALES_RAW
ORDER BY ROW_ID;

Result8 rows · 3 columns

ROW_ID	AMOUNT_TXT	BOTH_GONE
1	$4,200.00	4200.00
2	890.50	890.50
3	$6,150	6150
4	2,310.75	2310.75
5	N/A	N/A
6	$640.00	640.00
7	9,250.00	9250.00
8	$1,080	1080

Both characters gone, in a single column. The inner call strips the $, and the outer call strips the comma out of what the inner call produced.

ROW_ID 5 passes straight through, because N/A contains neither character. Keep an eye on that row. It comes back twice more before the end of the page.

What happens if we leave the third argument off?

The third argument is optional, and leaving it off does something useful. Let's try that, and two edge cases with it.

Query

SELECT REPLACE('a-b-c', '-')      AS TWO_ARGS,
       REPLACE('a-b-c', '-', '+') AS THREE_ARGS,
       REPLACE('a-b-c', 'x', '+') AS NOT_PRESENT,
       REPLACE(NULL, '-', '+')    AS REPLACE_NULL;

Result1 row · 4 columns

TWO_ARGS	THREE_ARGS	NOT_PRESENT	REPLACE_NULL
abc	a+b+c	a-b-c	NULL

TWO_ARGS has the dashes gone, while THREE_ARGS has them swapped for a +. With two arguments REPLACE deletes instead of substituting, which is the same thing as passing an empty string.

NOT_PRESENT and REPLACE_NULL are the two edges worth knowing. Looking for something that is not there changes nothing and does not complain, and REPLACE of NULL is NULL.

Now let's turn that text into a number

Stripping the punctuation was never the point. The point is that the result can be converted, and a string with a comma in it cannot be.

So let's put the conversion next to the cleaned text.

Query

SELECT ROW_ID, AMOUNT_TXT,
       REPLACE(REPLACE(AMOUNT_TXT, '$', ''), ',', '') AS CLEANED,
       TRY_TO_NUMBER(REPLACE(REPLACE(AMOUNT_TXT, '$', ''), ',', ''), 12, 2) AS AS_NUMBER
FROM SALES_RAW
ORDER BY ROW_ID;

Result8 rows · 4 columns

ROW_ID	AMOUNT_TXT	CLEANED	AS_NUMBER
1	$4,200.00	4200.00	4200.00
2	890.50	890.50	890.50
3	$6,150	6150	6150.00
4	2,310.75	2310.75	2310.75
5	N/A	N/A	NULL
6	$640.00	640.00	640.00
7	9,250.00	9250.00	9250.00
8	$1,080	1080	1080.00

Keeping CLEANED beside AS_NUMBER is deliberate, because it explains every value in it.

ROW_ID 5 is the interesting one again. Its cleaned value is still N/A, because stripping dollar signs and commas from N/A leaves N/A. So the conversion returned NULL rather than failing.

On eight rows one NULL is easy to spot. On eight hundred thousand it is not, so let's count instead.

Query

SELECT COUNT(*) AS ROWS_TOTAL,
       COUNT(TRY_TO_NUMBER(REPLACE(REPLACE(AMOUNT_TXT, '$', ''), ',', ''), 12, 2))
         AS CONVERTED,
       SUM(TRY_TO_NUMBER(REPLACE(REPLACE(AMOUNT_TXT, '$', ''), ',', ''), 12, 2))
         AS TOTAL
FROM SALES_RAW;

Result1 row · 3 columns

ROWS_TOTAL	CONVERTED	TOTAL
8	7	24521.25

Eight rows in, seven numbers out, and a total of the seven that did convert. Count the conversions rather than scrolling the grid. That is the habit that scales.

Why TRY_TO_NUMBER and not TO_NUMBER

TRY_TO_NUMBER is the forgiving form. It returns NULL for anything it cannot convert. Plain TO_NUMBER would have failed the whole statement on the N/A row and returned nothing at all, which is sometimes exactly what you want. The TRY_ family gets its own post, Episode 78.

Now let's try TRANSLATE

TRANSLATE looks like a sibling of REPLACE, and it is a different kind of function. REPLACE works on a substring. TRANSLATE works on characters, pairing its second argument with its third one position at a time.

Let's run it three ways, with a REPLACE beside it for comparison.

Query

SELECT TRANSLATE('a-b-c', '-', '+')   AS ONE_FOR_ONE,
       TRANSLATE('abc', 'abc', 'xyz') AS THREE_FOR_THREE,
       TRANSLATE('abc', 'abc', 'xy')  AS SHORTER_TO_LIST,
       REPLACE('abc', 'abc', 'xyz')   AS REPLACE_FOR_COMPARISON;

Result1 row · 4 columns

ONE_FOR_ONE	THREE_FOR_THREE	SHORTER_TO_LIST	REPLACE_FOR_COMPARISON
a+b+c	xyz	xy	xyz

ONE_FOR_ONE and THREE_FOR_THREE are the easy cases. One character in, one character out. a becomes x, b becomes y, and c becomes z.

SHORTER_TO_LIST is the one that matters.

The behaviour people are caught by

SHORTER_TO_LIST is the whole function in one column. It was given three characters to find and only two to use, so a and b were mapped and c, the one with no partner, was deleted. Compare it with REPLACE_FOR_COMPARISON in the same row, which kept all three characters. TRANSLATE does not pad, warn or error on a short list. It drops what it cannot map.

Let's use that deletion to strip the amount in one call

Once we know the overflow is deleted, an empty third argument becomes a way of stripping a whole set of characters at once. Every character in the second argument has no partner, so every one of them goes.

Let's do the amount again that way, with the nested REPLACE version beside it.

Query

SELECT ROW_ID, AMOUNT_TXT,
       TRANSLATE(AMOUNT_TXT, '$,', '')                AS TRANSLATE_ONE_CALL,
       REPLACE(REPLACE(AMOUNT_TXT, '$', ''), ',', '') AS REPLACE_NESTED_TWICE
FROM SALES_RAW
ORDER BY ROW_ID;

Result8 rows · 4 columns

ROW_ID	AMOUNT_TXT	TRANSLATE_ONE_CALL	REPLACE_NESTED_TWICE
1	$4,200.00	4200.00	4200.00
2	890.50	890.50	890.50
3	$6,150	6150	6150
4	2,310.75	2310.75	2310.75
5	N/A	N/A	N/A
6	$640.00	640.00	640.00
7	9,250.00	9250.00	9250.00
8	$1,080	1080	1080

TRANSLATE_ONE_CALL does in one call what REPLACE_NESTED_TWICE needs two for. The two columns are side by side so we can read them against each other rather than take it on trust.

And we can do better than reading them. Let's count the rows where they agree.

Query

SELECT COUNT(*) AS ROWS_TOTAL,
       COUNT_IF(TRANSLATE(AMOUNT_TXT, '$,', '')
                = REPLACE(REPLACE(AMOUNT_TXT, '$', ''), ',', '')) AS AGREE
FROM SALES_RAW;

Result1 row · 2 columns

ROWS_TOTAL	AGREE
8	8

Eight rows, eight agreements. On this data the two approaches are interchangeable, and the TRANSLATE form scales better. Adding another character to strip means adding one character to the pattern, instead of wrapping the whole expression in another call.

So where do REPLACE and TRANSLATE really differ?

They agreed on the whole amount column, which is a reason to be careful rather than relaxed. The two functions give the same answer on a lot of inputs and then diverge on one you did not test.

Let's try to make them disagree.

Query

SELECT REPLACE('cab', 'ab', 'X')   AS REPLACE_SUBSTRING,
       TRANSLATE('cab', 'ab', 'X') AS TRANSLATE_CHARACTERS,
       REPLACE('ab ba', 'ab', '..')   AS REPLACE_AGAIN,
       TRANSLATE('ab ba', 'ab', '..') AS TRANSLATE_AGAIN;

Result1 row · 4 columns

REPLACE_SUBSTRING	TRANSLATE_CHARACTERS	REPLACE_AGAIN	TRANSLATE_AGAIN
cX	cX	.. ba	.. ..

REPLACE_SUBSTRING and TRANSLATE_CHARACTERS were written to show the difference and failed to. REPLACE('cab', 'ab', 'X') and TRANSLATE('cab', 'ab', 'X') returned the same thing.

The reason is the deletion rule we just saw. TRANSLATE mapped a to X, found no partner for b, and deleted it. Two different mechanisms, one coincidence. The failed attempt stays on the page, because this is exactly what a one-character test at home will do.

REPLACE_AGAIN and TRANSLATE_AGAIN are the pair that does separate them. The input is chosen so it cannot coincide, because the characters appear in both orders.

• REPLACE('ab ba', 'ab', '..') looks for the substring ab. It occurs once, at the front, so one replacement happens and the ba at the back is left alone.
• TRANSLATE('ab ba', 'ab', '..') maps every a and every b wherever they appear, so both halves change.

	REPLACE	TRANSLATE
Works on	A substring, in order	Single characters, in any position
Second and third arguments	One thing to find, one to put in its place	Two lists, paired position by position
A short third argument	Deletes the substring entirely	Deletes the unpaired characters
Good for	Swapping a known word or sequence	Stripping or mapping a set of characters at once

Now let's point TRANSLATE at the phone numbers

This is the job TRANSLATE is genuinely best at. The punctuation in PHONE_TXT is inconsistent and all of it has to go. Four different formats, one call, no nesting.

The second argument is just a list of every character we want gone: the two brackets, a hyphen, a dot, a space and a plus sign. We also ask for the length of the result, so we can check it.

Query

SELECT ROW_ID, PHONE_TXT,
       TRANSLATE(PHONE_TXT, '()-. +', '') AS DIGITS_ONLY,
       LENGTH(TRANSLATE(PHONE_TXT, '()-. +', '')) AS N_DIGITS
FROM SALES_RAW
ORDER BY ROW_ID;

Result8 rows · 4 columns

ROW_ID	PHONE_TXT	DIGITS_ONLY	N_DIGITS
1	(704) 555-0142	7045550142	10
2	704-555-0198	7045550198	10
3	+1 416 555 0117	14165550117	11
4	604.555.0163	6045550163	10
5	+41 44 555 0188	41445550188	11
6	919-555-0124	9195550124	10
7	(617) 555-0179	6175550179	10
8	+44 161 555 0133	441615550133	12

N_DIGITS is there so the result can be checked rather than admired. Read it down the grid. Five of the numbers came back ten digits long, two came back eleven and one came back twelve.

And the three long ones are exactly the three rows whose PHONE_TXT starts with a +. That is a country code, not a fault, and it is the kind of thing a length column tells you immediately.

Let's split the region column with SPLIT

REGION_TXT holds two fields joined by a hyphen, which is a job for splitting rather than cutting. Snowflake has two functions for it, and the confusion between them is a difference of type.

Let's start with SPLIT and ask Snowflake what it gave us back.

Query

SELECT ROW_ID, REGION_TXT,
       SPLIT(REGION_TXT, '-')                AS AS_ARRAY,
       TYPEOF(SPLIT(REGION_TXT, '-'))        AS ITS_TYPE,
       ARRAY_SIZE(SPLIT(REGION_TXT, '-'))    AS N_PARTS
FROM SALES_RAW
ORDER BY ROW_ID;

Result8 rows · 5 columns

ROW_ID	REGION_TXT	AS_ARRAY	ITS_TYPE	N_PARTS
1	us-east	[ "us", "east" ]	ARRAY	2
2	US-EAST	[ "US", "EAST" ]	ARRAY	2
3	ca-west	[ "ca", "west" ]	ARRAY	2
4	CA-West	[ "CA", "West" ]	ARRAY	2
5	eu-central	[ "eu", "central" ]	ARRAY	2
6	us-east	[ "us", "east" ]	ARRAY	2
7	US-East	[ "US", "East" ]	ARRAY	2
8	uk-north	[ "uk", "north" ]	ARRAY	2

Look at AS_ARRAY: square brackets, quoted elements, one per line. That is not a string that happens to contain brackets. ITS_TYPE says ARRAY, and N_PARTS says two parts on every row.

Which means we index into it. Let's do that next.

Query

SELECT ROW_ID, REGION_TXT,
       SPLIT(REGION_TXT, '-')[0] AS INDEX_0,
       SPLIT(REGION_TXT, '-')[1] AS INDEX_1,
       SPLIT(REGION_TXT, '-')[2] AS INDEX_2_PAST_THE_END
FROM SALES_RAW
ORDER BY ROW_ID;

Result8 rows · 5 columns

ROW_ID	REGION_TXT	INDEX_0	INDEX_1	INDEX_2_PAST_THE_END
1	us-east	"us"	"east"	NULL
2	US-EAST	"US"	"EAST"	NULL
3	ca-west	"ca"	"west"	NULL
4	CA-West	"CA"	"West"	NULL
5	eu-central	"eu"	"central"	NULL
6	us-east	"us"	"east"	NULL
7	US-East	"US"	"East"	NULL
8	uk-north	"uk"	"north"	NULL

The off-by-one that is actually in the language

SPLIT's first element is at index 0. SUBSTR, one post ago, counted its first character as 1. Both are correct, both are in the same database, and the difference is that one is a string function and the other returns an array. So array rules apply.

INDEX_2_PAST_THE_END is the edge case. Index 2 is past the end of a two-element array, and the answer is NULL rather than an error.

Notice also that the values came back with their quotes around them. They are VARIANT elements, not plain strings, and comparing one to 'us' without casting it is a trap for another day.

Now let's try SPLIT_PART instead

SPLIT_PART asks a narrower question. Give me field number n. And it answers with a string, so there are no brackets, no quotes and nothing to unwrap.

Query

SELECT ROW_ID, REGION_TXT,
       SPLIT_PART(REGION_TXT, '-', 1)         AS PART_1,
       SPLIT_PART(REGION_TXT, '-', 2)         AS PART_2,
       TYPEOF(SPLIT_PART(REGION_TXT, '-', 1)::VARIANT) AS ITS_TYPE
FROM SALES_RAW
ORDER BY ROW_ID;

Result8 rows · 5 columns

ROW_ID	REGION_TXT	PART_1	PART_2	ITS_TYPE
1	us-east	us	east	VARCHAR
2	US-EAST	US	EAST	VARCHAR
3	ca-west	ca	west	VARCHAR
4	CA-West	CA	West	VARCHAR
5	eu-central	eu	central	VARCHAR
6	us-east	us	east	VARCHAR
7	US-East	US	East	VARCHAR
8	uk-north	uk	north	VARCHAR

ITS_TYPE says VARCHAR, and the values print bare. And the index is 1-based: PART_1 is the first field, which is the opposite of what SPLIT just did.

	SPLIT	SPLIT_PART
Returns	An ARRAY	A VARCHAR
First element is at	0	1
Past the end gives	NULL	An empty string
Use it when	You want all the fields, or the count of them	You want one named field

Now let's push it off both ends and see what it does.

Query

SELECT SPLIT_PART('a-b-c', '-', 0)  AS INDEX_0,
       SPLIT_PART('a-b-c', '-', 1)  AS INDEX_1,
       SPLIT_PART('a-b-c', '-', 3)  AS INDEX_3,
       SPLIT_PART('a-b-c', '-', 9)  AS PAST_THE_END,
       SPLIT_PART('a-b-c', '-', -1) AS NEGATIVE;

Result1 row · 5 columns

INDEX_0	INDEX_1	INDEX_3	PAST_THE_END	NEGATIVE
a	a	c		c

INDEX_0 returns the first field, exactly as SUBSTR treated a start of 0 as 1 last post. The same forgiving rule, in a different function.

PAST_THE_END gives an empty string rather than NULL, which is worth knowing because an empty string survives an IS NOT NULL check.

And NEGATIVE shows that a negative index counts from the right. -1 is the last field, whatever the number of fields turns out to be. That is genuinely useful: the file extension, the last part of a path, the domain of an address, none of them requiring us to count first.

Let's take the email apart with SPLIT_PART

Episode 66 took an email address apart with POSITION and SUBSTR, which needed nested calls and a length worked out by subtraction. Let's do the same job with no arithmetic at all.

Query

SELECT EMAIL,
       SPLIT_PART(EMAIL, '@', 1) AS LOCAL_PART,
       SPLIT_PART(EMAIL, '@', 2) AS DOMAIN_PART,
       SPLIT_PART(EMAIL, '.', 1) AS FIRST_NAME,
       SPLIT_PART(SPLIT_PART(EMAIL, '@', 1), '.', 2) AS LAST_NAME
FROM EMPLOYEES
ORDER BY EMP_ID;

Result8 rows · 5 columns

EMAIL	LOCAL_PART	DOMAIN_PART	FIRST_NAME	LAST_NAME
aisha.khan@example.com	aisha.khan	example.com	aisha	khan
marcus.reid@example.com	marcus.reid	example.com	marcus	reid
lena.fischer@example.com	lena.fischer	example.com	lena	fischer
tomas.novak@example.com	tomas.novak	example.com	tomas	novak
priya.raman@example.com	priya.raman	example.com	priya	raman
daniel.oconnor@example.com	daniel.oconnor	example.com	daniel	oconnor
sofia.marino@example.com	sofia.marino	example.com	sofia	marino
grace.lin@example.com	grace.lin	example.com	grace	lin

Four fields, four calls, each one naming the piece it wants. LAST_NAME is the only nested call, and only because it splits on two different separators in turn. Take everything before the @, then take the second field of that.

That it looks right is not the same as being right. So let's compare one of these columns directly against last post's version with MINUS. The only thing that query can return is a row where the two disagree.

Query

SELECT EMAIL, SPLIT_PART(EMAIL, '@', 1) AS P FROM EMPLOYEES
MINUS
SELECT EMAIL, LEFT(EMAIL, POSITION('@', EMAIL) - 1) FROM EMPLOYEES;

Result0 rows · 2 columns

EMAIL	P

Nothing came back. On every row, the local part from SPLIT_PART is the same value that Episode 66 built with LEFT and POSITION. That is the only column the query compared, so it is the only column we can claim. An empty grid from a query built to find differences is worth more than two grids that look alike.

What happens when a field is empty?

This is the part of the post most likely to be the cause of a bug you already have. STRTOK is a third way of splitting, and on tidy data it is indistinguishable from SPLIT_PART. On data with an empty field it is not.

Our test string is 'a,,c'. Two separators in a row, so the middle field is empty. Let's ask both functions for fields 1, 2 and 3 of it.

Query

SELECT SPLIT_PART('a,,c', ',', 1)  AS SP_1,
       SPLIT_PART('a,,c', ',', 2)  AS SP_2,
       SPLIT_PART('a,,c', ',', 3)  AS SP_3,
       STRTOK('a,,c', ',', 1)      AS ST_1,
       STRTOK('a,,c', ',', 2)      AS ST_2,
       STRTOK('a,,c', ',', 3)      AS ST_3;

Result1 row · 6 columns · scroll →

SP_1	SP_2	SP_3	ST_1	ST_2	ST_3
a		c	a	c	NULL

SPLIT_PART reports the empty field as an empty value in SP_2, and c in SP_3.

STRTOK skips the empty field entirely. Its second token, ST_2, is c, and ST_3 is NULL, because there is no third non-empty piece.

Neither is wrong. They are answering different questions. And the difference becomes a reporting error as soon as anything counts the fields, so let's count them.

Query

SELECT ARRAY_SIZE(SPLIT('a,,c', ','))         AS SPLIT_SAYS,
       ARRAY_SIZE(STRTOK_TO_ARRAY('a,,c', ',')) AS STRTOK_SAYS,
       ARRAY_SIZE(SPLIT('a,,c,', ','))          AS SPLIT_TRAILING_SEPARATOR,
       ARRAY_SIZE(STRTOK_TO_ARRAY('a,,c,', ',')) AS STRTOK_TRAILING_SEPARATOR;

Result1 row · 4 columns

SPLIT_SAYS	STRTOK_SAYS	SPLIT_TRAILING_SEPARATOR	STRTOK_TRAILING_SEPARATOR
3	2	4	2

Pick the one you meant

Same string, two answers: three fields or two. Add a trailing separator and they diverge further. One of them now says four while the other still says two. If the thing you are splitting can contain a double separator, which function you picked decides whether your field numbering is right, and nothing will raise an error either way.

Question you are asking	Function that answers it
What is in position n, counting empties	SPLIT_PART / SPLIT
What are the non-empty pieces	STRTOK / STRTOK_TO_ARRAY

Can STRTOK take a whole set of delimiters?

Yes, and that is the other difference between the two. SPLIT_PART takes one delimiter. STRTOK takes a set of characters, any one of which separates a token. That is the same idea TRIM used two posts ago, and the same idea TRANSLATE used on the phone numbers earlier on this page.

Let's point it at the phone numbers and count the tokens.

Query

SELECT ROW_ID, PHONE_TXT,
       STRTOK(PHONE_TXT, '()-. +', 1) AS TOKEN_1,
       STRTOK(PHONE_TXT, '()-. +', 2) AS TOKEN_2,
       STRTOK(PHONE_TXT, '()-. +', 3) AS TOKEN_3,
       ARRAY_SIZE(STRTOK_TO_ARRAY(PHONE_TXT, '()-. +')) AS N_TOKENS
FROM SALES_RAW
ORDER BY ROW_ID;

Result8 rows · 6 columns · scroll →

ROW_ID	PHONE_TXT	TOKEN_1	TOKEN_2	TOKEN_3	N_TOKENS
1	(704) 555-0142	704	555	0142	3
2	704-555-0198	704	555	0198	3
3	+1 416 555 0117	1	416	555	4
4	604.555.0163	604	555	0163	3
5	+41 44 555 0188	41	44	555	4
6	919-555-0124	919	555	0124	3
7	(617) 555-0179	617	555	0179	3
8	+44 161 555 0133	44	161	555	4

One call handles brackets, hyphens, dots, spaces and plus signs at once. And here the empty-field behaviour is an advantage, because (704) 555-0142 has a bracket followed by a space and we do not want an empty token between them.

N_TOKENS counts them. Five of the numbers split into three tokens and three split into four, and those three are the rows with a leading + and a country code of their own.

Reading a token count down the grid like that is how you find out your data has two shapes in it, before you write code that assumes one.

One last thing: let's clean the whole table in one statement

Everything on this page, plus the trimming and casing from Episode 64 and Episode 65, applied to the whole of SALES_RAW at once. Each raw column is kept next to the column derived from it, which is the habit worth copying while you are still developing the expressions.

Query

SELECT ROW_ID,
       CUSTOMER_TXT                                   AS RAW_NAME,
       INITCAP(TRIM(CUSTOMER_TXT))                    AS TIDY_NAME,
       AMOUNT_TXT                                     AS RAW_AMOUNT,
       TRY_TO_NUMBER(TRANSLATE(AMOUNT_TXT, '$,', ''), 12, 2) AS AMOUNT,
       REGION_TXT                                     AS RAW_REGION,
       UPPER(SPLIT_PART(REGION_TXT, '-', 1))          AS COUNTRY_CODE,
       UPPER(SPLIT_PART(REGION_TXT, '-', 2))          AS AREA,
       TRANSLATE(PHONE_TXT, '()-. +', '')             AS PHONE_DIGITS
FROM SALES_RAW
ORDER BY ROW_ID;

Result8 rows · 9 columns · scroll →

ROW_ID	RAW_NAME	TIDY_NAME	RAW_AMOUNT	AMOUNT	RAW_REGION	COUNTRY_CODE	AREA	PHONE_DIGITS
1	Northwind Traders	Northwind Traders	$4,200.00	4200.00	us-east	US	EAST	7045550142
2	blue ridge supply	Blue Ridge Supply	890.50	890.50	US-EAST	US	EAST	7045550198
3	KESTREL ANALYTICS	Kestrel Analytics	$6,150	6150.00	ca-west	CA	WEST	14165550117
4	Harbour Foods	Harbour Foods	2,310.75	2310.75	CA-West	CA	WEST	6045550163
5	Alpine Logistics	Alpine Logistics	N/A	NULL	eu-central	EU	CENTRAL	41445550188
6	Delta Print Co	Delta Print Co	$640.00	640.00	us-east	US	EAST	9195550124
7	Meridian Health	Meridian Health	9,250.00	9250.00	US-East	US	EAST	6175550179
8	copper kettle ltd	Copper Kettle Ltd	$1,080	1080.00	uk-north	UK	NORTH	441615550133

All eight rows, with the name trimmed and recapitalised, the amount a real number, the region split into two columns, and the phone number reduced to digits.

The amount on ROW_ID 5 is still NULL. The cleaning cannot invent a value that N/A never held, and it should not pretend to.

Nine columns, five functions, and not one of them doing anything this page has not shown on its own first.

Key points

• REPLACE swaps a substring and replaces every occurrence. With only two arguments it deletes instead of substituting.
• TRANSLATE maps characters, pairing its second argument with its third position by position. A different mechanism that often produces the same answer, which is why the two get confused.
• If TRANSLATE's third argument is shorter than its second, the unpaired characters are deleted. That is not an error case, it is the mechanism. An empty third argument strips a whole set of characters in one call.
• SPLIT returns an ARRAY and is 0-based. SPLIT_PART returns a VARCHAR and is 1-based. TYPEOF settles which one you are holding.
• Past the end, SPLIT's index gives NULL and SPLIT_PART gives an empty string, which survives an IS NOT NULL test.
• SPLIT_PART with -1 is the last field, however many fields there are.
• SPLIT_PART keeps an empty field and STRTOK throws it away, so on 'a,,c' one says three fields and the other says two. Neither is a bug; picking the wrong one is.
• STRTOK takes a set of delimiter characters, which is what makes it the right tool for inconsistently punctuated input.
• TRY_TO_NUMBER returns NULL for what it cannot convert instead of failing the statement, so count the conversions. Nothing else will tell you one row did not make it.

Next in this series

In the next post we will start on regular expressions with REGEXP_LIKE and REGEXP_COUNT, and with the rule that surprises everyone who has written regex anywhere else. In Snowflake, REGEXP_LIKE is anchored at both ends.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.34.101) running the demo script for this episode, including the empty grid and the comparison that failed to show a difference. The SALES_RAW and EMPLOYEES data is invented demo data created by that script. No performance or benchmark claims are made anywhere in this series. Function behaviour follows the Snowflake string-function documentation.