Snowflake SQL Tutorial 78 - TRY_TO_NUMBER, TRY_CAST, TRY_TO_DATE - The TRY_ Family

Snowflake SQL · Episode 78

In this post we are going to cover the TRY_ family in Snowflake: TRY_TO_NUMBER, TRY_TO_DATE, TRY_CAST, and the ones people reach for that do not exist.

A TRY_ function does the same conversion as the plain one. The difference is what happens when the value will not convert. The plain one raises an error and your query stops. The TRY_ one hands you a NULL for that value and carries on.

We will start with the single bad row that makes the case for all of it, and build it up step by step. On the way we will find two things this family refuses to do at all, which are compile errors rather than bad data, and write the query I would actually put on a real load.

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

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

On this page

1. Let's create some sample data
2. Let's convert the column the normal way first
3. Now let's do it with TRY_TO_NUMBER instead
4. Let's put a number on what that error cost
5. Can we just filter the bad value out first?
6. So what does TRY_TO_NUMBER actually accept?
7. Let's make the scale thing explicit
8. Now some spellings you did not ask for
9. Now let's look at TRY_TO_DATE's edges
10. Now TRY_CAST, the general one
11. What happens when the input is not a string?
12. And an untyped NULL will not compile either
13. So which TRY_ functions exist?
14. Now let's use it to learn the shape of a column
15. Now the query I would actually write
16. Be careful which average you mean
17. One last thing: the whole job in one query

Let's create some sample data

This post works on SALES_RAW, the dirty table where every column is text. Copy the script below into a worksheet and run it once.

Sample data

-- Sample data for this post.
-- Run this once. SALES_RAW is deliberately dirty, and one row in it is the
-- reason this whole episode exists.
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');

Query

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

Result8 rows · 5 columns

ROW_ID	CUSTOMER_TXT	AMOUNT_TXT	DATE_TXT	REGION_TXT
1	Northwind Traders	$4,200.00	2023-01-12	us-east
2	blue ridge supply	890.50	01/28/2023	US-EAST
3	KESTREL ANALYTICS	$6,150	14-Feb-2023	ca-west
4	Harbour Foods	2,310.75	2023-03-09	CA-West
5	Alpine Logistics	N/A	2023-04-05	eu-central
6	Delta Print Co	$640.00	05/21/2023	us-east
7	Meridian Health	9,250.00	14-Aug-2023	US-East
8	copper kettle ltd	$1,080	2024-09-29	uk-north

Eight rows. The column we are converting is AMOUNT_TXT, and seven of those values are numbers wearing punctuation. One of them is not.

Let's pull out the row that causes all the trouble, because the rest of this post is about it.

Query

SELECT ROW_ID, CUSTOMER_TXT, AMOUNT_TXT, DATE_TXT
FROM SALES_RAW
WHERE AMOUNT_TXT = 'N/A';

Result1 row · 4 columns

ROW_ID	CUSTOMER_TXT	AMOUNT_TXT	DATE_TXT
5	Alpine Logistics	N/A	2023-04-05

Row 5. Alpine Logistics, and the amount is the text N/A. One row out of eight.

Let's convert the column the normal way first

Here is the query anybody would write. Strip the dollar signs and the commas, then TO_NUMBER what is left. It works on seven of these eight values.

Query

SELECT ROW_ID, AMOUNT_TXT,
       TO_NUMBER(REPLACE(REPLACE(AMOUNT_TXT, '$', ''), ',', ''), 12, 2) AS AMOUNT
FROM SALES_RAW
ORDER BY ROW_ID;

Query failed — Snowflake returned an error

100038 (22018): Numeric value 'N/A' is not recognized

And look at what came back. Nothing. No rows at all.

Seven values converted perfectly well and we got none of them, because the eighth one stopped the statement. The error is accurate and useful and it is still a result set of nothing.

Now let's do it with TRY_TO_NUMBER instead

Same query, one word longer. TRY_TO_NUMBER in place of TO_NUMBER and nothing else changed.

Query

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

Result8 rows · 3 columns

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

All eight rows came back. Seven of them carry a number and row 5 carries NULL.

No error, the query finished, and we can see exactly which row is the problem instead of being told there is one somewhere.

Let's put a number on what that error cost

I do not want to leave that as a feeling. Let's count it.

Query

SELECT COUNT(*) AS ROWS_TOTAL,
       COUNT(TRY_TO_NUMBER(REPLACE(REPLACE(AMOUNT_TXT, '$', ''), ',', ''), 12, 2))
         AS ROWS_YOU_COULD_HAVE_HAD,
       COUNT(*) - COUNT(TRY_TO_NUMBER(REPLACE(REPLACE(AMOUNT_TXT, '$', ''), ',', ''), 12, 2))
         AS ROWS_THAT_ACTUALLY_FAILED
FROM SALES_RAW;

Result1 row · 3 columns

ROWS_TOTAL	ROWS_YOU_COULD_HAVE_HAD	ROWS_THAT_ACTUALLY_FAILED
8	7	1

Eight rows in the table. Seven we could have had. One that actually failed.

Seven good rows thrown away for one bad one. That is the whole argument for this family of functions, and it gets worse rather than better as the table gets bigger.

Can we just filter the bad value out first?

Somebody is going to say this, so let's try it. Exclude the known bad value in the WHERE clause and then use the plain function.

Query

SELECT COUNT(*) AS ROWS_KEPT,
       SUM(TO_NUMBER(REPLACE(REPLACE(AMOUNT_TXT, '$', ''), ',', ''), 12, 2)) AS TOTAL
FROM SALES_RAW
WHERE AMOUNT_TXT <> 'N/A';

Result1 row · 2 columns

ROWS_KEPT	TOTAL
7	24521.25

Seven rows kept, and a total of 24521.25. That is the right answer, and it is the right answer today.

It breaks the first time somebody types NA instead of N/A. Or lower case. Or a dash. Or leaves the cell empty.

Why the filter is the weaker answer

Filtering by value means listing the ways data can be wrong, and that list is never finished. TRY_TO_NUMBER asks a different question — did this value convert? — and that question is complete.

So what does TRY_TO_NUMBER actually accept?

Worth knowing before you rely on it. Let's push six values through it, from the obvious to the awkward.

Query

SELECT TRY_TO_NUMBER('42')           AS PLAIN,
       TRY_TO_NUMBER('42.7')         AS WITH_DECIMALS,
       TRY_TO_NUMBER('  42  ')       AS WITH_SPACES,
       TRY_TO_NUMBER('4 2')          AS SPACE_INSIDE,
       TRY_TO_NUMBER('')             AS EMPTY_STRING,
       TRY_TO_NUMBER(NULL::VARCHAR)  AS NULL_INPUT;

Result1 row · 6 columns · scroll →

PLAIN	WITH_DECIMALS	WITH_SPACES	SPACE_INSIDE	EMPTY_STRING	NULL_INPUT
42	43	42	NULL	NULL	NULL

'42' gives 42. Good. '42.7' gives 43, and that is Episode 77's trap still sitting here: no scale means scale zero, so it rounded.

TRY_ does not protect you from that, because nothing failed. The value converted. It just converted to something other than what the text said.

Now the next two columns together, because they are the pair worth remembering. Spaces around the outside were trimmed and the value converted. A space in the middle gave NULL, because '4 2' is not a number.

The empty string gives NULL, and a typed NULL going in gives NULL coming out.

Let's make the scale thing explicit

That rounding is worth ten seconds on its own, because it is the one thing in this post that goes wrong silently. Let's put the same value through several scales.

Query

SELECT TRY_TO_NUMBER('42.7', 10, 0)  AS SCALE_0,
       TRY_TO_NUMBER('42.7', 10, 1)  AS SCALE_1,
       TRY_TO_NUMBER('42.5', 10, 0)  AS HALF_AT_SCALE_0,
       TRY_TO_NUMBER('42.7')         AS NO_SCALE_GIVEN;

Result1 row · 4 columns

SCALE_0	SCALE_1	HALF_AT_SCALE_0	NO_SCALE_GIVEN
43	42.7	43	43

Scale zero gives 43. Scale one gives 42.7. '42.5' at scale zero gives 43, which is half away from zero as always.

And the last column passes no scale at all, which gives 43 — the same as asking for scale zero. So say the scale.

Now some spellings you did not ask for

Real data arrives from systems you do not control. Let's see what TRY_TO_NUMBER makes of five notations you might be handed.

Query

SELECT TRY_TO_NUMBER('1e3')     AS SCIENTIFIC,
       TRY_TO_NUMBER('+42')     AS LEADING_PLUS,
       TRY_TO_NUMBER('0x1F')    AS HEX_,
       TRY_TO_NUMBER('42%')     AS PERCENT_,
       TRY_TO_NUMBER('(42)')    AS ACCOUNTING_NEGATIVE;

Result1 row · 5 columns

SCIENTIFIC	LEADING_PLUS	HEX_	PERCENT_	ACCOUNTING_NEGATIVE
1000	42	NULL	NULL	NULL

'1e3' is scientific notation and it gives 1000. It works. '+42' works too, so a leading plus is fine.

Hex gives NULL. A percent sign gives NULL. And '(42)', which accountants use to mean a negative number, gives NULL as well.

So it is more generous than you expect about notation and stricter than you expect about punctuation. Neither of those is guessable, which is why it is worth running the grid.

Now let's look at TRY_TO_DATE's edges

TRY_TO_DATE behaves the same way, and it checks the calendar as well as the shape. Let's hand it five things that all match YYYY-MM-DD perfectly.

Query

SELECT TRY_TO_DATE('2024-02-30', 'YYYY-MM-DD')  AS IMPOSSIBLE_DAY,
       TRY_TO_DATE('2024-06-31', 'YYYY-MM-DD')  AS JUNE_31,
       TRY_TO_DATE('', 'YYYY-MM-DD')            AS EMPTY_,
       TRY_TO_DATE('0000-00-00', 'YYYY-MM-DD')  AS ALL_ZEROS,
       TRY_TO_DATE(NULL::VARCHAR, 'YYYY-MM-DD') AS NULL_INPUT;

Result1 row · 5 columns

IMPOSSIBLE_DAY	JUNE_31	EMPTY_	ALL_ZEROS	NULL_INPUT
NULL	NULL	NULL	NULL	NULL

Every one of them is NULL. The 30th of February does not exist in any year. June has no 31st. The empty string is not a date. All zeros is not a date. And a typed NULL gives NULL.

Not one of those was rejected on its shape. They were rejected because they are not days.

Now TRY_CAST, the general one

TRY_CAST is the one that is not tied to a single target type. Give it a string and any type you like.

Query

SELECT TRY_CAST('42' AS INT)        AS TEXT_TO_INT,
       TRY_CAST('42.7' AS FLOAT)    AS TEXT_TO_FLOAT,
       TRY_CAST('abc' AS INT)       AS TEXT_THAT_FAILS,
       TRY_CAST('2024-06-30' AS DATE) AS TEXT_TO_DATE,
       TRY_CAST('true' AS BOOLEAN)  AS TEXT_TO_BOOLEAN;

Result1 row · 5 columns

TEXT_TO_INT	TEXT_TO_FLOAT	TEXT_THAT_FAILS	TEXT_TO_DATE	TEXT_TO_BOOLEAN
42	42.7	NULL	2024-06-30	TRUE

Text to INT, text to FLOAT, text to DATE, text to BOOLEAN. And the one that cannot convert gives NULL instead of an error.

So that looks like the answer to everything. It is not.

What happens when the input is not a string?

Let's hand TRY_CAST a number. Not a bad value — a perfectly good number — and ask for an integer.

Query

SELECT TRY_CAST(123.45 AS INT) AS SHOULD_NOT_GET_THIS_FAR;

Query failed — Snowflake returned an error

001065 (22023): SQL compilation error:
Function TRY_CAST cannot be used with arguments of types NUMBER(5,2) and NUMBER(38,0)

TRY_CAST only converts from a string, and nothing in the name tells you that. Notice also that this is a compile error. The query never ran at all.

Which is fair enough, once you see why. What would it be trying? A number cannot be malformed. There is nothing to try, so a plain cast is the correct tool.

Let's show that, two ways, with a string literal beside them for comparison.

Query

SELECT TRY_CAST(123.45::VARCHAR AS INT) AS VIA_VARCHAR,
       123.45::INT                      AS PLAIN_CAST,
       TRY_CAST('123.45' AS INT)        AS FROM_A_LITERAL_STRING;

Result1 row · 3 columns

VIA_VARCHAR	PLAIN_CAST	FROM_A_LITERAL_STRING
123	123	123

Through a VARCHAR it works. The plain cast gives the same answer. And from a string literal, which is what TRY_CAST wanted in the first place, it also works. All three gave 123.

And an untyped NULL will not compile either

This is the other refusal, and it caught me while I was writing this course. Let's pass a bare NULL, with no type on it, to TRY_TO_NUMBER.

Query

SELECT TRY_TO_NUMBER(NULL) AS SHOULD_NOT_GET_THIS_FAR;

Query failed — Snowflake returned an error

001065 (22023): SQL compilation error:
Function TRY_CAST cannot be used with arguments of types NULL and NUMBER(38,0)

Two things in that message are worth reading. The first is that it is a compile error again, so nothing ran.

The second is the function name. We called TRY_TO_NUMBER and the message says TRY_CAST. So TRY_TO_NUMBER is TRY_CAST underneath, which is a free piece of knowledge we did not have to go looking for.

The same thing happens on the date side.

Query

SELECT TRY_TO_DATE(NULL, 'YYYY-MM-DD') AS SHOULD_NOT_GET_THIS_FAR;

Query failed — Snowflake returned an error

001065 (22023): SQL compilation error:
Function TRY_CAST cannot be used with arguments of types NULL and DATE

Same error, with DATE in place of the number type. And the fix is to give the NULL a type.

Query

SELECT TRY_TO_NUMBER('not a number')  AS BAD_DATA_IS_HANDLED,
       TRY_TO_NUMBER('not a number') IS NULL AS AND_IT_IS_NULL,
       TRY_TO_NUMBER(NULL::VARCHAR) IS NULL  AS A_TYPED_NULL_IS_FINE,
       TRY_TO_NUMBER(NULL::NUMBER) IS NULL   AS SO_IS_THIS_ONE;

Result1 row · 4 columns

BAD_DATA_IS_HANDLED	AND_IT_IS_NULL	A_TYPED_NULL_IS_FINE	SO_IS_THIS_ONE
NULL	TRUE	TRUE	TRUE

Bad data is handled and comes back NULL. A NULL cast to VARCHAR is fine. A NULL cast to NUMBER is fine too.

So the rule is this. TRY_ protects you from the data. It does not protect you from the SQL.

One more grid on this, and it is worth being careful about what it shows.

Query

SELECT NULL::VARCHAR                 AS TYPED_NULL,
       TYPEOF(NULL::VARCHAR::VARIANT) AS ITS_TYPE,
       TYPEOF(NULL::VARIANT)          AS AN_UNTYPED_NULLS_TYPE,
       TRY_CAST(NULL::VARCHAR AS INT) AS TRY_CAST_WITH_A_TYPED_NULL;

Result1 row · 4 columns

TYPED_NULL	ITS_TYPE	AN_UNTYPED_NULLS_TYPE	TRY_CAST_WITH_A_TYPED_NULL
NULL	NULL	NULL	NULL

The typed NULL works and TRY_CAST accepts it. But look at the two TYPEOF columns: both of them came back NULL as well.

What this grid cannot show you

TYPEOF cannot tell you the declared type of a NULL, so this grid does not show the difference between a typed and an untyped one. That difference lives in the compiler, which is exactly why the error two grids up was a compile error and not a row of output.

So which TRY_ functions exist?

Let's run the ones that do, all in one row, so you have the list.

Query

SELECT TRY_TO_NUMBER('1')                              AS TRY_TO_NUMBER_,
       TRY_TO_DECIMAL('1.5', 10, 2)                    AS TRY_TO_DECIMAL_,
       TRY_TO_DOUBLE('1.5')                            AS TRY_TO_DOUBLE_,
       TRY_TO_BOOLEAN('yes')                           AS TRY_TO_BOOLEAN_,
       TRY_TO_DATE('2024-06-30')                       AS TRY_TO_DATE_,
       TRY_TO_TIMESTAMP('2024-06-30 00:00:00')         AS TRY_TO_TIMESTAMP_;

Result1 row · 6 columns · scroll →

TRY_TO_NUMBER_	TRY_TO_DECIMAL_	TRY_TO_DOUBLE_	TRY_TO_BOOLEAN_	TRY_TO_DATE_	TRY_TO_TIMESTAMP_
1	1.50	1.5	TRUE	2024-06-30	2024-06-30 00:00:00

Six of them in that grid, plus TRY_CAST, which we have already used.

Now two that people reach for and that are not there.

Query

SELECT TRY_TO_CHAR(42) AS SHOULD_NOT_GET_THIS_FAR;

Query failed — Snowflake returned an error

002139 (02000): SQL compilation error:
Unknown function TRY_TO_CHAR.

And the other one.

Query

SELECT TRY_SQRT(-1) AS SHOULD_NOT_GET_THIS_FAR;

Query failed — Snowflake returned an error

002139 (02000): SQL compilation error:
Unknown function TRY_SQRT.

Neither exists, and the reason is worth understanding because it tells you where the boundary of this family is.

TO_CHAR takes a value you already have and formats it. It cannot meet bad data, so there is nothing for a TRY_ version to catch.

And the square root of a negative number is not bad data. It is a legitimate value with no real answer, which is a different problem and not a conversion one.

So the family is conversions only. Text coming in from somewhere you do not control.

Now let's use it to learn the shape of a column

Here is a use for TRY_ that is not about avoiding errors at all. The date column from Episode 75 has three formats in it. Let's try each format against every row and see which rows answer.

Query

SELECT ROW_ID, DATE_TXT,
       TRY_TO_DATE(DATE_TXT, 'YYYY-MM-DD')  AS AS_ISO,
       TRY_TO_DATE(DATE_TXT, 'MM/DD/YYYY')  AS AS_US,
       TRY_TO_DATE(DATE_TXT, 'DD-MON-YYYY') AS AS_MON
FROM SALES_RAW
ORDER BY ROW_ID;

Result8 rows · 5 columns

ROW_ID	DATE_TXT	AS_ISO	AS_US	AS_MON
1	2023-01-12	2023-01-12	NULL	NULL
2	01/28/2023	NULL	2023-01-28	NULL
3	14-Feb-2023	NULL	NULL	2023-02-14
4	2023-03-09	2023-03-09	NULL	NULL
5	2023-04-05	2023-04-05	NULL	NULL
6	05/21/2023	NULL	2023-05-21	NULL
7	14-Aug-2023	NULL	NULL	2023-08-14
8	2024-09-29	2024-09-29	NULL	NULL

Each row lights up in exactly one column and comes back NULL in the other two. The pattern of NULLs is the information.

Let's count it.

Query

SELECT COUNT(*) AS ROWS_TOTAL,
       COUNT(TRY_TO_DATE(DATE_TXT, 'YYYY-MM-DD'))  AS PARSED_AS_ISO,
       COUNT(TRY_TO_DATE(DATE_TXT, 'MM/DD/YYYY'))  AS PARSED_AS_US,
       COUNT(TRY_TO_DATE(DATE_TXT, 'DD-MON-YYYY')) AS PARSED_AS_MON,
       COUNT(COALESCE(TRY_TO_DATE(DATE_TXT, 'YYYY-MM-DD'),
                      TRY_TO_DATE(DATE_TXT, 'MM/DD/YYYY'),
                      TRY_TO_DATE(DATE_TXT, 'DD-MON-YYYY'))) AS PARSED_BY_ANY
FROM SALES_RAW;

Result1 row · 5 columns

ROWS_TOTAL	PARSED_AS_ISO	PARSED_AS_US	PARSED_AS_MON	PARSED_BY_ANY
8	4	2	2	8

Eight rows. Four parse as ISO, two as the slash format and two as the month-name format. And a COALESCE over all three gets all eight.

Counting with TRY_ is how you learn the shape of a column you were handed, rather than guessing at it from the first few rows.

Now the query I would actually write

Here is what goes on a real load. First question: which rows failed? Not how many. Which ones.

Query

SELECT ROW_ID, CUSTOMER_TXT, AMOUNT_TXT
FROM SALES_RAW
WHERE TRY_TO_NUMBER(REPLACE(REPLACE(AMOUNT_TXT, '$', ''), ',', ''), 12, 2) IS NULL
ORDER BY ROW_ID;

Result1 row · 3 columns

ROW_ID	CUSTOMER_TXT	AMOUNT_TXT
5	Alpine Logistics	N/A

One row, named. Row 5, Alpine Logistics, N/A. A human can go and fix that.

That is the query you put in an alert. A count tells somebody there is a problem. This tells them where it is.

Second, the total — and never the total on its own.

Query

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

Result1 row · 4 columns

ROWS_TOTAL	CONVERTED	FAILED	TOTAL_OF_WHAT_CONVERTED
8	7	1	24521.25

Eight rows, seven converted, one failed, and a total of 24521.25 for what converted.

The failed column is what makes the total trustworthy. Without it, that number is just a number, and nobody reading it downstream knows whether it covers everything.

Be careful which average you mean

There are two averages here and they are both correct. Let's put them side by side.

Query

SELECT ROUND(AVG(TRY_TO_NUMBER(REPLACE(REPLACE(AMOUNT_TXT, '$', ''), ',', ''), 12, 2)), 2)
         AS AVG_OF_CONVERTED,
       ROUND(SUM(TRY_TO_NUMBER(REPLACE(REPLACE(AMOUNT_TXT, '$', ''), ',', ''), 12, 2))
             / COUNT(*), 2) AS TOTAL_DIVIDED_BY_ALL_ROWS,
       COUNT(*) AS ALL_ROWS,
       COUNT(TRY_TO_NUMBER(REPLACE(REPLACE(AMOUNT_TXT, '$', ''), ',', ''), 12, 2))
         AS NON_NULL_ROWS
FROM SALES_RAW;

Result1 row · 4 columns

AVG_OF_CONVERTED	TOTAL_DIVIDED_BY_ALL_ROWS	ALL_ROWS	NON_NULL_ROWS
3503.04	3065.16	8	7

The average of the converted rows is 3503.04. The total divided by all the rows is 3065.16.

The gap between them is the whole of the difference between dividing by seven and dividing by eight. AVG ignores the NULL and divides by the rows that had a value. The second expression divides by every row in the table.

Episode 71 found the same thing on the ORDERS table, and it is the same mechanism here. Neither answer is wrong. One of them is the one you meant, and that is a decision rather than a default.

One last thing: the whole job in one query

Let's put it all together. The amount, the date, and a flag saying whether this row needs a human.

Query

SELECT ROW_ID, AMOUNT_TXT, DATE_TXT,
       TRY_TO_NUMBER(REPLACE(REPLACE(AMOUNT_TXT, '$', ''), ',', ''), 12, 2) AS AMOUNT,
       COALESCE(TRY_TO_DATE(DATE_TXT, 'YYYY-MM-DD'),
                TRY_TO_DATE(DATE_TXT, 'MM/DD/YYYY'),
                TRY_TO_DATE(DATE_TXT, 'DD-MON-YYYY'))                       AS THE_DATE,
       IFF(TRY_TO_NUMBER(REPLACE(REPLACE(AMOUNT_TXT, '$', ''), ',', ''), 12, 2) IS NULL,
           'REVIEW', 'OK')                                                  AS FLAG
FROM SALES_RAW
ORDER BY ROW_ID;

Result8 rows · 6 columns · scroll →

ROW_ID	AMOUNT_TXT	DATE_TXT	AMOUNT	THE_DATE	FLAG
1	$4,200.00	2023-01-12	4200.00	2023-01-12	OK
2	890.50	01/28/2023	890.50	2023-01-28	OK
3	$6,150	14-Feb-2023	6150.00	2023-02-14	OK
4	2,310.75	2023-03-09	2310.75	2023-03-09	OK
5	N/A	2023-04-05	NULL	2023-04-05	REVIEW
6	$640.00	05/21/2023	640.00	2023-05-21	OK
7	9,250.00	14-Aug-2023	9250.00	2023-08-14	OK
8	$1,080	2024-09-29	1080.00	2024-09-29	OK

Row 5 gets a REVIEW flag and still keeps its date, because the date was never the problem. Every other row comes through as OK.

That is the shape of the work. Convert what you can, flag what you cannot, and never lose a row.

Key points

• A TRY_ function returns NULL instead of stopping the query, so one bad value costs you one value rather than the whole result.
• One bad row cost us seven good ones. 8 rows in the table, 7 that would have converted, 1 that actually failed.
• Filtering the bad value out in the WHERE clause gives the right answer today and means listing the ways data can be wrong. TRY_ asks whether the value converted, which is a complete question.
• TRY_TO_NUMBER with no scale uses scale zero and rounds. TRY_ does not protect you from that, because nothing failed.
• Outer spaces are trimmed; a space in the middle is NULL. Scientific notation and a leading plus work. Hex, a percent sign and accountants' brackets do not.
• TRY_TO_DATE checks the calendar, so the 30th of February and the 31st of June are both NULL.
• TRY_CAST only converts from a string. Give it a number and you get a compile error, because a number cannot be malformed and there is nothing to try.
• An untyped NULL is a compile error too, and the message names TRY_CAST even when you called TRY_TO_NUMBER. Cast the NULL to a type and it is fine.
• TRY_ protects you from the data. It does not protect you from the SQL.
• There is no TRY_TO_CHAR and no TRY_SQRT. The family is conversions only.
• Counting with TRY_ is how you learn the shape of a column you were handed: 4 ISO rows, 2 slash rows, 2 month-name rows, and all 8 through a COALESCE.
• Report the rows that failed, not the count, and never send a total without its failure count beside it.
• AVG divides by the non-null rows. Dividing the total by every row gives a different answer, and both are correct for different questions.

Next in this series

Episode 79 — CASE. Conditional logic inside a query: the searched form, the simple form, what happens when nothing matches, and where CASE is allowed to appear.

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. The SALES_RAW data is invented demo data created by the setup script above. Every count and every value quoted in the text is read off a captured column rather than worked out here. Nothing on this page is timed and no performance claims are made anywhere in this series. Function behaviour follows the Snowflake conversion functions documentation.