Snowflake SQL Tutorial 77 - CAST, TO_NUMBER, TO_VARCHAR, TO_BOOLEAN - Conversion

Snowflake SQL · Episode 77

In this post we are going to cover conversion in Snowflake: CAST, the :: operator, TO_NUMBER, TO_VARCHAR and TO_BOOLEAN.

Conversion is turning one type into another. Text into a number, a number into text, text into a boolean. Snowflake does some of it for you without being asked, and the rest you ask for.

We will start with the conversions you did not ask for, because those are the ones that surprise people, and build it up step by step. On the way we will find out that casting a decimal to an integer rounds rather than truncating, what NUMBER's precision argument actually counts, and a flag column that comes back FALSE on every row with no error anywhere.

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

Episode 77 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 the conversions you did not ask for
3. Now let's watch a comparison change meaning
4. What happens when the text is not a number at all?
5. Now let's ask on purpose, with CAST and ::
6. What does casting a decimal to an integer do?
7. Now let's count that on real money
8. Now let's look at NUMBER and FLOAT
9. Why float equality is a trap
10. And a cast that simply refuses
11. Let's turn text into a number with TO_NUMBER
12. What does precision actually mean?
13. Now let's clean the messy amount column
14. Now the other direction, with TO_VARCHAR
15. What happens to NULL in all of this?
16. Let's try TO_BOOLEAN
17. One last thing: a flag column that is wrong on every row
18. One column, end to end

Let's create some sample data

This post uses two tables. ORDERS is the clean one, with a real NUMBER amount column. SALES_RAW is the dirty one, where the amount is text with currency symbols and thousands separators in it.

Copy the script below into a worksheet and run it once.

Sample data

-- Sample data for this post.
-- ORDERS is the clean table the whole course uses. SALES_RAW is the
-- deliberately dirty one: every column in it is text.
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');

-- ORDERS comes from the course setup script. You already have it if you have
-- been following along.

Here are the orders.

Query

SELECT ORDER_ID, CUSTOMER_ID, ORDER_DATE, AMOUNT, STATUS
FROM ORDERS ORDER BY ORDER_ID LIMIT 10;

Result10 rows · 5 columns

ORDER_ID	CUSTOMER_ID	ORDER_DATE	AMOUNT	STATUS
1001	101	2023-01-12	4200.00	SHIPPED
1002	102	2023-01-28	890.50	SHIPPED
1003	101	2023-02-14	6150.00	SHIPPED
1004	103	2023-02-27	1500.00	CANCELLED
1005	104	2023-03-09	2310.75	SHIPPED
1006	101	2023-03-22	5020.00	SHIPPED
1007	105	2023-04-05	7890.00	SHIPPED
1008	102	2023-04-19	1120.00	SHIPPED
1009	103	2023-05-02	3340.25	SHIPPED
1010	106	2023-05-21	640.00	PENDING

Ten rows of it. AMOUNT is a proper number and STATUS is text. Keep an eye on STATUS, because it is the last thing we look at and it is the most useful thing in this post.

And here is the messy table.

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, every column text. AMOUNT_TXT is the one we will be converting: some values carry a dollar sign, some carry a comma, some carry both, some carry neither, and one of them says N/A.

Let's start with the conversions you did not ask for

Before we ask for a conversion, let's see what happens when we do not. Text and numbers in the same expression, with no cast anywhere.

Query

SELECT '10' + 5              AS STRING_PLUS_NUMBER,
       '10' * '2'            AS STRING_TIMES_STRING,
       '10' > 5              AS STRING_COMPARED_TO_NUMBER,
       '10' = 10             AS STRING_EQUALS_NUMBER,
       TYPEOF(('10' + 5)::VARIANT) AS RESULT_TYPE;

Result1 row · 5 columns

STRING_PLUS_NUMBER	STRING_TIMES_STRING	STRING_COMPARED_TO_NUMBER	STRING_EQUALS_NUMBER	RESULT_TYPE
15	20.0	TRUE	TRUE	INTEGER

The text '10' plus the number 5 gave 15. Snowflake read the text as a number and added. Text times text gave 20.0, so that one came back as a decimal rather than a whole number.

The two comparison columns are both TRUE, and the last column says the type of the addition is INTEGER.

All of that is Snowflake being helpful. It is also where the trouble starts.

Now let's watch a comparison change meaning

Same idea, but this time the conversion decides what the comparison means. Let's compare ten and nine four different ways.

Query

SELECT '10' < '9'    AS TEXT_COMPARISON,
       10 < 9        AS NUMERIC_COMPARISON,
       '10' < 9      AS MIXED_COMPARISON,
       '010' = '10'  AS LEADING_ZERO_AS_TEXT,
       '010'::INT = '10'::INT AS LEADING_ZERO_AS_NUMBER;

Result1 row · 5 columns

TEXT_COMPARISON	NUMERIC_COMPARISON	MIXED_COMPARISON	LEADING_ZERO_AS_TEXT	LEADING_ZERO_AS_NUMBER
TRUE	FALSE	FALSE	FALSE	TRUE

The text '10' is less than the text '9': TRUE. The number 10 is less than the number 9: FALSE. And the text '10' against the number 9 is also FALSE.

As text, 1 comes before 9. That is alphabetical, and it is the right answer for text. The mixed comparison converted the text to a number first, so it agreed with the numeric one.

The last two columns make the same point with a leading zero. As text, '010' and '10' are different strings, so the comparison is FALSE. Cast both to INT and they are the same value, so it is TRUE.

Where this bites in real life

This is why an ID column stored as text sorts in an order nobody expects, and why two codes that look equal can fail to join. The comparison is not wrong. It is doing the right thing for the type it was given.

What happens when the text is not a number at all?

The helpfulness has a limit. Let's find it.

Query

SELECT 'abc' + 5 AS SHOULD_NOT_GET_THIS_FAR;

Query failed — Snowflake returned an error

100038 (22018): Numeric value 'abc' is not recognized

A good error. It names the value it could not read. And notice there was no cast in that query at all — the conversion Snowflake added for us is the thing that failed.

Now let's ask on purpose, with CAST and ::

There are two ways to write a cast in Snowflake. The word CAST, and the :: operator. Let's check that they are the same thing rather than assume it.

Query

SELECT CAST('42' AS INT)   AS CAST_FORM,
       '42'::INT           AS OPERATOR_FORM,
       CAST('42' AS INT) = '42'::INT AS SAME,
       CAST(42 AS VARCHAR) AS NUMBER_TO_TEXT,
       42::VARCHAR         AS OPERATOR_TO_TEXT;

Result1 row · 5 columns

CAST_FORM	OPERATOR_FORM	SAME	NUMBER_TO_TEXT	OPERATOR_TO_TEXT
42	42	TRUE	42	42

Both forms gave 42, and the comparison column says TRUE. They are the same thing, so use whichever you find easier to read.

The last two columns go the other way, number to text, and both forms work there too.

What does casting a decimal to an integer do?

Guess this one before you read the grid. Three decimals, all cast to INT, with TRUNC and ROUND on the last one for comparison.

Query

SELECT 2.4::INT   AS TWO_POINT_FOUR,
       2.5::INT   AS TWO_POINT_FIVE,
       2.7::INT   AS TWO_POINT_SEVEN,
       TRUNC(2.7) AS TRUNC_FOR_COMPARISON,
       ROUND(2.7) AS ROUND_FOR_COMPARISON;

Result1 row · 5 columns

TWO_POINT_FOUR	TWO_POINT_FIVE	TWO_POINT_SEVEN	TRUNC_FOR_COMPARISON	ROUND_FOR_COMPARISON
2	3	3	2	3

2.4 became 2. 2.5 became 3. And 2.7 became 3.

2.7 went up. The cast rounded. TRUNC(2.7) is 2 and ROUND(2.7) is 3, and the cast matched ROUND.

This catches people coming from code

That is the opposite of most programming languages. In C, in Java and in Python, casting to an integer throws the decimals away. In Snowflake it rounds. If you have been writing ::INT expecting a truncation, you have been getting something else.

Now the negatives, so we know which way it rounds.

Query

SELECT (-2.4)::INT   AS MINUS_TWO_POINT_FOUR,
       (-2.5)::INT   AS MINUS_TWO_POINT_FIVE,
       (-2.7)::INT   AS MINUS_TWO_POINT_SEVEN,
       TRUNC(-2.7)   AS TRUNC_FOR_COMPARISON,
       FLOOR(-2.7)   AS FLOOR_FOR_COMPARISON;

Result1 row · 5 columns

MINUS_TWO_POINT_FOUR	MINUS_TWO_POINT_FIVE	MINUS_TWO_POINT_SEVEN	TRUNC_FOR_COMPARISON	FLOOR_FOR_COMPARISON
-2	-3	-3	-2	-3

-2.5 gave -3 and -2.7 gave -3. TRUNC(-2.7) is -2 and FLOOR(-2.7) is -3.

So the cast rounds half away from zero, which is the same rule ROUND follows and which we met in Episode 70.

Now let's count that on real money

That is a rule about two decimals. Let's find out what it does to a column of money, because this is where it costs you something.

One query: how often does the cast agree with TRUNC, how often with ROUND, and what do the two totals come to.

Query

SELECT COUNT(*) AS ROWS_TOTAL,
       COUNT_IF(AMOUNT::INT = TRUNC(AMOUNT)) AS CAST_MATCHES_TRUNC,
       COUNT_IF(AMOUNT::INT = ROUND(AMOUNT)) AS CAST_MATCHES_ROUND,
       SUM(AMOUNT::INT) AS TOTAL_AS_INT,
       ROUND(SUM(AMOUNT), 2) AS TOTAL_EXACT
FROM ORDERS;

Result1 row · 5 columns

ROWS_TOTAL	CAST_MATCHES_TRUNC	CAST_MATCHES_ROUND	TOTAL_AS_INT	TOTAL_EXACT
41	37	40	205428	205427.00

Forty-one orders. The cast agreed with TRUNC on 37 of them and with ROUND on 40.

Forty rather than forty-one, because one order has no amount at all. Both comparisons on that row return NULL, and COUNT_IF cannot count a comparison that never came out true or false.

Now look at the two totals. Summing the integer casts gives 205428. Summing the real amounts gives 205427.00.

Read the gap carefully

The gap is exactly 1. Not a fraction of a unit — a whole one, on a column of money, over a table this small. Cast an amount column to an integer and you will not get a warning. You will get a total that does not tie, and somebody else will find it.

Now let's look at NUMBER and FLOAT

NUMBER takes a precision and a scale. Precision is the total number of digits and scale is how many of them sit after the point. FLOAT takes neither.

Let's put one value through both, and then do a little arithmetic in each.

Query

SELECT 1234.5678::NUMBER(10,2)  AS NUMBER_10_2,
       1234.5678::NUMBER(6,1)   AS NUMBER_6_1,
       1234.5678::FLOAT         AS AS_FLOAT,
       0.1::FLOAT + 0.2::FLOAT  AS FLOAT_ARITHMETIC,
       0.1::NUMBER(10,2) + 0.2::NUMBER(10,2) AS DECIMAL_ARITHMETIC;

Result1 row · 5 columns

NUMBER_10_2	NUMBER_6_1	AS_FLOAT	FLOAT_ARITHMETIC	DECIMAL_ARITHMETIC
1234.57	1234.6	1234.5678	0.30000000000000004	0.30

NUMBER(10,2) gave 1234.57, rounded to fit the scale. NUMBER(6,1) gave 1234.6. As a FLOAT, all four decimals survived.

So the float held more of this value. Now look at the last two columns, which are the same sum done twice.

As floats, 0.1 plus 0.2 came back as 0.30000000000000004. As NUMBER(10,2), the same sum came back as 0.30, exactly.

Why float equality is a trap

That extra digit looks harmless. Let's see what a comparison makes of it.

Query

SELECT 0.1::FLOAT + 0.2::FLOAT = 0.3::FLOAT                     AS FLOAT_EQUALITY,
       0.1::NUMBER(10,2) + 0.2::NUMBER(10,2) = 0.3::NUMBER(10,2) AS DECIMAL_EQUALITY,
       ABS((0.1::FLOAT + 0.2::FLOAT) - 0.3::FLOAT) < 0.000001    AS WITHIN_TOLERANCE,
       (0.1::FLOAT + 0.2::FLOAT) - 0.3::FLOAT                    AS THE_DIFFERENCE;

Result1 row · 4 columns

FLOAT_EQUALITY	DECIMAL_EQUALITY	WITHIN_TOLERANCE	THE_DIFFERENCE
FALSE	TRUE	TRUE	5.551115123125783e-17

As floats, the sum does not equal 0.3: FALSE. As decimals, it does: TRUE.

The last column is the actual difference, 5.551115123125783e-17. Tiny. Not zero. And a WHERE clause does not care how tiny it is.

The third column shows the way out if you are stuck with floats: compare with a tolerance instead of with =, and that column says TRUE.

The simpler answer

Or store money as NUMBER and never think about it again. That is the real advice. Floats are for measurements, not for amounts that have to add up.

And a cast that simply refuses

Before we move on, one more from the messy table. Row 5's amount is the text N/A. Let's cast it.

Query

SELECT 'N/A'::INT AS SHOULD_NOT_GET_THIS_FAR;

Query failed — Snowflake returned an error

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

Same error as the implicit conversion gave us earlier, and it stops the whole query. There is a family of functions that hands you a NULL instead, and that is Episode 78.

Let's turn text into a number with TO_NUMBER

TO_NUMBER is the named function for text into a number. Let's start with the plain form and then add the arguments.

Query

SELECT TO_NUMBER('42')             AS PLAIN,
       TO_NUMBER('42.75')          AS WITH_DECIMALS,
       TO_NUMBER('42.75', 10, 2)   AS PRECISION_AND_SCALE,
       TO_NUMBER('42.75', 10, 0)   AS SCALE_ZERO,
       TO_NUMBER('-42.75', 10, 2)  AS NEGATIVE_;

Result1 row · 5 columns

PLAIN	WITH_DECIMALS	PRECISION_AND_SCALE	SCALE_ZERO	NEGATIVE_
42	43	42.75	43	-42.75

'42' gave 42. Good. Now read the second column: '42.75' gave 43.

Read that again. The text said 42.75 and we got back 43. The default scale is zero, so it rounded, and nothing told us.

Add a precision and a scale and the decimals come back: 42.75. Ask for scale zero explicitly and you get 43 again, which is the same thing the plain form did.

The default is not the one you want

TO_NUMBER with no arguments on a price column has thrown your pennies away. It is not an error and it is not a NULL. It is a whole number where a decimal used to be. Always say the scale.

What does precision actually mean?

This is the part people guess at. Precision is the total number of digits, not the number of decimal places. Let's take a value that needs six digits and try several precisions on it.

Query

SELECT TO_NUMBER('1234.56', 6, 2)  AS FITS_EXACTLY,
       TO_NUMBER('1234.56', 10, 2) AS PLENTY_OF_ROOM,
       TO_NUMBER('1234.56', 6, 0)  AS SCALE_ZERO_FITS,
       TO_NUMBER('1234.5', 6, 2)   AS ONE_FEWER_DIGIT;

Result1 row · 4 columns

FITS_EXACTLY	PLENTY_OF_ROOM	SCALE_ZERO_FITS	ONE_FEWER_DIGIT
1234.56	1234.56	1235	1234.50

1234.56 needs six digits in all, so a precision of 6 with a scale of 2 fits it exactly. A precision of 10 has plenty of room and gives the same answer.

Scale zero at the same precision gives 1235, rounded. And a value with one fewer digit comes back padded out to the scale, as 1234.50.

Now let's ask for a precision that is too small for the value.

Query

SELECT TO_NUMBER('1234.56', 4, 2) AS SHOULD_NOT_GET_THIS_FAR;

Query failed — Snowflake returned an error

100039 (22003): Numeric value '1234.56' is out of range

Out of range. Not truncated, not rounded, not quietly wrapped. Refused. That is the behaviour you want from a number that will not fit.

Now let's clean the messy amount column

Here is the practical problem. Our amounts are text with dollar signs and commas in them. There are two ways to deal with that and they behave very differently.

TO_NUMBER takes a format string, the same way TO_CHAR did in Episode 75. Let's try the format on one value, with the strip-the-punctuation approach from Episode 67 beside it.

Query

SELECT TO_NUMBER('$4,200.00', '$9,999.00')   AS WITH_CURRENCY_FORMAT,
       TO_NUMBER('4,200.00', '9,999.00')     AS WITH_COMMA_FORMAT,
       TRY_TO_NUMBER('$4,200.00')            AS WITHOUT_A_FORMAT,
       TRY_TO_NUMBER(REPLACE(REPLACE('$4,200.00', '$', ''), ',', '')) AS EPISODE_67_WAY;

Result1 row · 4 columns

WITH_CURRENCY_FORMAT	WITH_COMMA_FORMAT	WITHOUT_A_FORMAT	EPISODE_67_WAY
4200	4200	NULL	4200

The format mask read $4,200.00 and gave 4200. A comma-only mask on comma-only text did the same. With no format at all we got NULL, and stripping the punctuation first gave 4200 as well.

So both work on that value. Let's run them both down the whole column and see whether that holds.

Query

SELECT ROW_ID, AMOUNT_TXT,
       TRY_TO_NUMBER(AMOUNT_TXT, '$9,999.00')                               AS VIA_FORMAT,
       TRY_TO_NUMBER(REPLACE(REPLACE(AMOUNT_TXT, '$', ''), ',', ''), 12, 2) AS VIA_REPLACE
FROM SALES_RAW
ORDER BY ROW_ID;

Result8 rows · 4 columns

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

It does not hold. The format column is NULL on most of the rows.

That is because a format string is literal. 890.50 has no dollar sign, and $6,150 has no decimals, and our mask asks for both of those on every row. The strip-and-convert column handled all of them except N/A, which is not a number by any reading.

Let's count it rather than squint at eight rows.

Query

SELECT COUNT(*) AS ROWS_TOTAL,
       COUNT(TRY_TO_NUMBER(AMOUNT_TXT, '$9,999.00')) AS FORMAT_SUCCEEDED,
       COUNT(TRY_TO_NUMBER(REPLACE(REPLACE(AMOUNT_TXT, '$', ''), ',', ''), 12, 2))
         AS REPLACE_SUCCEEDED,
       COUNT_IF(NVL(TRY_TO_NUMBER(AMOUNT_TXT, '$9,999.00'), -1)
                <> NVL(TRY_TO_NUMBER(REPLACE(REPLACE(AMOUNT_TXT, '$', ''), ',', ''), 12, 2), -1))
         AS DISAGREE
FROM SALES_RAW;

Result1 row · 4 columns

ROWS_TOTAL	FORMAT_SUCCEEDED	REPLACE_SUCCEEDED	DISAGREE
8	2	7	5

Eight rows. The format mask succeeded on two of them. Stripping the punctuation first succeeded on seven. And the two approaches disagree on five rows.

Which one to reach for

A format string is precise and brittle. Stripping the punctuation first is forgiving and will accept things you might rather it rejected. On data you control, use the format, because then a surprise is an error. On data somebody else sends you, strip first and count what failed.

Now the other direction, with TO_VARCHAR

TO_VARCHAR turns anything into text. Let's feed it one of each type.

Query

SELECT TO_VARCHAR(42)                                AS FROM_INT,
       TO_VARCHAR(42.75)                             AS FROM_DECIMAL,
       TO_VARCHAR(DATE '2024-06-30')                 AS FROM_DATE,
       TO_VARCHAR(TRUE)                              AS FROM_BOOLEAN,
       TO_VARCHAR(NULL)                              AS FROM_NULL;

Result1 row · 5 columns

FROM_INT	FROM_DECIMAL	FROM_DATE	FROM_BOOLEAN	FROM_NULL
42	42.75	2024-06-30	true	NULL

Numbers and dates come out the way you would expect. Two columns are worth a second look.

The boolean came out as true in lower case. And NULL came out as NULL, not as the word “null” in a string.

TO_VARCHAR takes a format string too, exactly like TO_CHAR. In fact let's check whether they are the same function wearing two names.

Query

SELECT TO_VARCHAR(1234.5678, '9,999.99')            AS NUMBER_FORMATTED,
       TO_VARCHAR(DATE '2024-06-30', 'DD-MON-YYYY') AS DATE_FORMATTED,
       TO_CHAR(1234.5678, '9,999.99')               AS TO_CHAR_FOR_COMPARISON,
       TO_VARCHAR(1234.5678, '9,999.99') = TO_CHAR(1234.5678, '9,999.99') AS SAME;

Result1 row · 4 columns

NUMBER_FORMATTED	DATE_FORMATTED	TO_CHAR_FOR_COMPARISON	SAME
1,234.57	30-Jun-2024	1,234.57	TRUE

Same number mask, same date mask, and the comparison column says TRUE. For formatting purposes they are interchangeable.

What happens to NULL in all of this?

One thing to be careful about before we move on. Let's put a NULL through every conversion we have seen.

Query

SELECT TO_VARCHAR(NULL) IS NULL   AS VARCHAR_IS_NULL,
       TO_NUMBER(NULL) IS NULL    AS NUMBER_IS_NULL,
       NULL::INT IS NULL          AS CAST_IS_NULL,
       LENGTH(TO_VARCHAR(NULL))   AS LENGTH_OF_IT,
       'x' || TO_VARCHAR(NULL)    AS CONCATENATED;

Result1 row · 5 columns

VARCHAR_IS_NULL	NUMBER_IS_NULL	CAST_IS_NULL	LENGTH_OF_IT	CONCATENATED
TRUE	TRUE	TRUE	NULL	NULL

NULL to text is still NULL. NULL to a number is NULL. A cast of NULL is NULL.

And the last two columns are the ones that catch people. LENGTH of it is NULL rather than zero, and concatenating something onto it gives NULL rather than the something.

So a conversion never rescues a NULL. It carries it straight through, and so does everything downstream of it.

Let's try TO_BOOLEAN

TO_BOOLEAN accepts more spellings than you would guess. Let's see the true side first.

Query

SELECT TO_BOOLEAN('true')  AS T_TRUE,
       TO_BOOLEAN('TRUE')  AS T_UPPER,
       TO_BOOLEAN('t')     AS T_LETTER,
       TO_BOOLEAN('yes')   AS T_YES,
       TO_BOOLEAN('y')     AS T_Y,
       TO_BOOLEAN('on')    AS T_ON,
       TO_BOOLEAN('1')     AS T_ONE;

Result1 row · 7 columns · scroll →

T_TRUE	T_UPPER	T_LETTER	T_YES	T_Y	T_ON	T_ONE
TRUE	TRUE	TRUE	TRUE	TRUE	TRUE	TRUE

Seven columns, seven spellings, all TRUE: the word, the word in capitals, the single letter, yes, y, on and the digit 1.

And the false side.

Query

SELECT TO_BOOLEAN('false') AS F_FALSE,
       TO_BOOLEAN('f')     AS F_LETTER,
       TO_BOOLEAN('no')    AS F_NO,
       TO_BOOLEAN('n')     AS F_N,
       TO_BOOLEAN('off')   AS F_OFF,
       TO_BOOLEAN('0')     AS F_ZERO,
       TO_BOOLEAN(NULL)    AS F_NULL;

Result1 row · 7 columns · scroll →

F_FALSE	F_LETTER	F_NO	F_N	F_OFF	F_ZERO	F_NULL
FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	NULL

Six spellings all come back FALSE. The last column is the important one: NULL gives NULL, not FALSE. Which is right, and which people forget.

Now something that is neither.

Query

SELECT TO_BOOLEAN('maybe') AS SHOULD_NOT_GET_THIS_FAR;

Query failed — Snowflake returned an error

100037 (22018): Boolean value 'maybe' is not recognized

An error, and it names the value. So there is a TRY_ version, the same way there is for dates.

Query

SELECT TRY_TO_BOOLEAN('maybe') AS MAYBE_,
       TRY_TO_BOOLEAN('yes')   AS YES_,
       TRY_TO_BOOLEAN('2')     AS TWO_,
       TRY_TO_BOOLEAN('')      AS EMPTY_;

Result1 row · 4 columns

MAYBE_	YES_	TWO_	EMPTY_
NULL	TRUE	NULL	NULL

'maybe' gives NULL. 'yes' gives TRUE. The empty string gives NULL. And the string '2' gives NULL, so among digits as text only '0' and '1' work.

Now watch what happens when the same characters arrive as actual numbers instead of text.

Query

SELECT TO_BOOLEAN(0)    AS ZERO_,
       TO_BOOLEAN(1)    AS ONE_,
       TO_BOOLEAN(2)    AS TWO_,
       TO_BOOLEAN(-1)   AS MINUS_ONE,
       TO_BOOLEAN(0.0)  AS ZERO_DECIMAL;

Result1 row · 5 columns

ZERO_	ONE_	TWO_	MINUS_ONE	ZERO_DECIMAL
FALSE	TRUE	TRUE	TRUE	FALSE

The number 2 is TRUE. So is -1. Only zero is FALSE.

Same character, different answer

The text '2' is NULL and the number 2 is TRUE. Same character on the screen, different type, different answer. If a flag arrives as text from a file, that distinction is yours to get right.

One last thing: a flag column that is wrong on every row

This is the one I want you to take away from the post. I wrote a flag to answer a simple question: is this order shipped? The test is STATUS = 'Shipped'.

Query

SELECT STATUS, COUNT(*) AS N,
       TO_BOOLEAN(IFF(STATUS = 'Shipped', 'true', 'false')) AS IS_SHIPPED
FROM ORDERS
GROUP BY STATUS
ORDER BY N DESC;

Result4 rows · 3 columns

STATUS	N	IS_SHIPPED
SHIPPED	34	FALSE
PENDING	3	FALSE
RETURNED	2	FALSE
CANCELLED	2	FALSE

Thirty-four orders are shipped. And the flag says FALSE on all four rows of that grid, including the shipped one.

The reason is sitting in the column immediately to the left. The data says SHIPPED in capitals. I wrote 'Shipped'. String comparison is case sensitive.

The quietest bug in this post

No error. No NULL. No empty result. The query ran, the grid looks healthy, and the boolean is wrong everywhere. Use UPPER on both sides, or ILIKE, and check a flag column against the column it was derived from before you trust it.

One column, end to end

Let's finish with one grid that does the whole job on the messy amount: raw text in, a number, an integer, back to text, and a flag saying whether it worked.

Query

SELECT ROW_ID,
       AMOUNT_TXT                                           AS RAW_TEXT,
       TRY_TO_NUMBER(AMOUNT_TXT, '$9,999.00')               AS AS_NUMBER,
       TRY_TO_NUMBER(AMOUNT_TXT, '$9,999.00')::INT          AS AS_INT,
       TO_VARCHAR(TRY_TO_NUMBER(AMOUNT_TXT, '$9,999.00'), '9,999.99') AS BACK_TO_TEXT,
       TO_BOOLEAN(IFF(TRY_TO_NUMBER(AMOUNT_TXT, '$9,999.00') IS NULL, 'false', 'true'))
         AS DID_IT_CONVERT
FROM SALES_RAW
ORDER BY ROW_ID;

Result8 rows · 6 columns · scroll →

ROW_ID	RAW_TEXT	AS_NUMBER	AS_INT	BACK_TO_TEXT	DID_IT_CONVERT
1	$4,200.00	4200	4200	4,200.	TRUE
2	890.50	NULL	NULL	NULL	FALSE
3	$6,150	NULL	NULL	NULL	FALSE
4	2,310.75	NULL	NULL	NULL	FALSE
5	N/A	NULL	NULL	NULL	FALSE
6	$640.00	640	640	640.	TRUE
7	9,250.00	NULL	NULL	NULL	FALSE
8	$1,080	NULL	NULL	NULL	FALSE

Look at the BACK_TO_TEXT column on the first row. It reads 4,200. and then nothing.

That is because TO_NUMBER with that format gave us scale zero, so there were no decimals left to print by the time we formatted it. The loss happened in the second column and you only see it in the fourth.

That is conversion in one grid. Nothing errored, nothing warned, and the pennies left the building two steps earlier.

Key points

• Snowflake converts text to numbers for you in arithmetic and comparisons, and the conversion it adds is the thing that fails when the text is not a number.
• Text comparison is alphabetical. '10' < '9' is TRUE and 10 < 9 is FALSE. A leading zero makes two strings different and two numbers equal.
• CAST(x AS t) and x::t are the same thing.
• Casting a decimal to an integer rounds, it does not truncate, and it rounds half away from zero. That is the opposite of C, Java and Python.
• On 41 orders the cast agreed with TRUNC 37 times and with ROUND 40 times, and the integer total came out 1 higher than the real total. A whole unit, on money.
• NUMBER's precision is total digits, not decimal places. A precision that is too small is refused as out of range, not truncated.
• TO_NUMBER with no arguments uses scale zero, so it rounds your decimals away and says nothing. Always pass the precision and scale.
• A format mask is literal: it converted 2 of our 8 messy amounts, while stripping the punctuation first converted 7, and the two disagreed on 5 rows.
• TO_VARCHAR and TO_CHAR gave identical results for formatting, and a boolean comes out as lower-case true.
• A conversion never rescues a NULL. LENGTH of a NULL is NULL, and concatenating onto one gives NULL.
• TO_BOOLEAN accepts 7 true spellings and 6 false ones, and NULL gives NULL rather than FALSE. The text '2' is NULL while the number 2 is TRUE.
• Check a flag column before you trust it. Comparing STATUS against 'Shipped' when the data says SHIPPED gave FALSE on every row with no error at all.

Next in this series

Episode 78 — the TRY_ family. TRY_TO_NUMBER, TRY_CAST and TRY_TO_DATE, what they accept, the two things they refuse to compile at all, and the ones people reach for that do not exist.

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 ORDERS and SALES_RAW data is invented demo data created by the setup script above. 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.