Conversion Functions- Part1 - Nimbus Intelligence

Conversion functions allow a data item of one datatype supported by Snowflake to be transformed into another datatype.
The functions are numerous and are divided by family. Let’s take a look at these categories and some of the functions in them.

Text/Character/Binary

TO_CHAR/TO_VARCHAR: these two functions are synonymous and convert the input to a variable of type varchar
They can have expressions, dates, numeric expressions, or binary expressions as input.
In addition to the input data, the format can be, where allowed, specified.

I find the following example particularly fitting showing how the same column can be translated into a string by specifying different formats!

select column1 as orig_value,
   to_char(column1, '">"$99.0"<"') as D2_1,
   to_char(column1, '">"B9,999.0"<"') as D4_1,
   to_char(column1, '">"TME"<"') as TME,
   to_char(column1, '">"TM9"<"') as TM9,
   to_char(column1, '">"0XXX"<"') as X4,
   to_char(column1, '">"S0XXX"<"') as SX4
from values (-12.391), (0), (-1), (0.10), (0.01), (3987), (1.111);

Regarding conversion to binary, Snowflake provides the TO_BINARY function. This function requires as input a string and as optional parameter a format, if not specified the default is HEX. Note is that the input string must be in a format supported by the function so a possible example is as follows:

SELECT TO_BINARY(HEX_ENCODE('ciao'));

the result of the query will be: 6369616f (which is nothing but the value in hexadecimal of what ‘ciao’ is in ASCII).
The conversion to binary can also be performed with the TRY_TO_BINARY function which is the same as the previous one but like any TRY_TO_ function in case it fails to complete the conversion process it does not return an error but returns a value of type NULL.

Numeric

There are 4 numerical conversions . 3 of these can be treated in the same way. The function arguments are the same as we can see from the syntax:

TO_DECIMAL( <expr> [, '<format>' ] [, <precision> [, <scale> ] ] )

TO_NUMBER( <expr> [, '<format>' ] [, <precision> [, <scale> ] ] )

TO_NUMERIC( <expr> [, '<format>' ] [, <precision> [, <scale> ] ] )

where can be of numeric, literal, or variant type , the format expresses precisely the format in which the number will be expressed , the precion indicates how many decimal digits will be present in the output, and finally the scale indicates the number of fractional decimal digits.

When the precision is not specified is by default 38 digits,If it is the scale that is not specified, it is equal to 0.
Let us see an example :

select column1,
       to_decimal(column1, '99.9') as D0,
       to_decimal(column1, '99.9', 9, 5) as D5,
       to_decimal(column1, 'TM9', 9, 5) as TD5
from values ('1.0'), ('-12.3'), ('0.0'), ('  - 0.1   ');

For all these functions there is the «safe» option of TRY_TO_, which also exists for the next numeric conversion function, which is

TO_DOUBLE( <expr>[, '<format>' ] )

As we observe, precision and scale cannot be specified but only format. With this function Fixed-point numbers are converted to floating point, there may be a loss of precision.

For numeric values, values such as nan, inf and infinity are also covered.

Boolean

This function allows us to convert input values in a data of type Boolean.

As for the numeric type arguments all values other than zero result in True.

The string type arguments on the other hand that are accepted by the function are only a few. We see them below :

‘true’, ‘t’, ‘yes’, ‘y’, ‘on’, ‘1’ return TRUE.

‘false’, ‘f’, ‘no’, ‘n’, ‘off’, ‘0’ return FALSE.

An example query to show some equivalences:

SELECT 
TRY_TO_BOOLEAN('yes') =1
AND 
TRY_TO_BOOLEAN('yes') =true;


The result of this query is true because 1, true and yes in Boolean optics are equivalent.

Cast, ::

This operation among those seen is my favorite since it is the most generic : it is valid FOR EVERY DATA TYPE!!! For example if we are working on semi-structured data we see how after an ingestion of a fie of Json type inside a single column of variant type we can create a view or a table with the cast operation using the «::» operator.

SELECT '1.2345'::DECIMAL(15,5);
+-------------------------+
| '1.2345'::DECIMAL(15,5) |
|-------------------------|
|                 1.23450 |
+-------------------------+

This function also has an analogue that attempts the conversion but if something goes wrong it does not generate an error but a null value.

Conclusion

As we have seen numerous conversions can be made, these can prove useful on numerous occasions, the first that comes to mind and that I have interfaced with in my most recent projects is the conversion of stored semi-structured data fields into a single variable of variant type.
In addition to the usage it is certainly important to note that Snowflake offers safety mechanisms in the conversion with the TRY_TO_ function : if the type of a column cannot be converted the operation is not performed.

There are other conversion functions that operate on other datatype types (geographic, semi-structured, variant, date, and timestamp), we will look at them in a future blog article.