Explained: CAST() function in Snowflake?

Are you looking to find how to use the CAST() function within the Snowflake cloud data warehouse or maybe you are looking for a solution to use the String function in the Snowflake? If you are looking for any of these problem solutions then you have landed on the correct page. I will also show you what and how to use the CAST() function. I will explain the CAST() function by taking a practical example. So don’t waste time let’s start step by step guide to understanding what is the CAST() expression.

What is CAST() Function?

CAST() function helps to convert the value of one data type to another. For example, if we want to cast the decimal to integer as shown below examples, then we can go with the cast() function.

How can we use CAST() Function ?

CAST() function is used to converts a value of one data type into another data type. The semantics of CAST is the same as the semantics of corresponding TO_DATATYPE conversion.

  • If the scale is not sufficient to hold the value, then the function rounds the value.
  • If the precision is not sufficient to hold the value, then the function will raise the error.  

What is the syntax of the CAST() function in Snowflake?

-- Syntax : 

CAST( Source_expression AS Target_datatype )

Source_expression :: Target_datatype

CAST() Argument Details :

Source_expressionAny supported expression
Target_datatypeData Type

Source_expression:

The expression of any supported data type will be converted into a different data type.

Target_datatype:

The data type to which to convert the source expression. If the datatype supports additional properties ( Scale and precision ) then the properties can be included.

Examples of CAST() function in Snowflake:

How to CAST() the data to decimal with example?

select cast('1.4225' as decimal(15,2));

+---------------------------------+
| CAST('1.4225' AS DECIMAL(15,2)) |
|---------------------------------|
|                            1.42 |
+---------------------------------+

What is the other method to CAST() the data to decimal with example?

select '1.4225'::decimal(15,5);
+-------------------------+
| '1.4225'::DECIMAL(15,5) |
|-------------------------|
|                 1.42250 |
+-------------------------+

How to CAST() the data to an integer with example?

select cast(1.68 as integer);

+-----------------------+
| CAST(1.68 AS INTEGER) |
|-----------------------|
|                     2 |
+-----------------------+

How to CAST() the data to timestamp with example?

select cast('14-Mar-2017' as timestamp);

+----------------------------------+
| CAST('14-MAR-2017' AS TIMESTAMP) |
|----------------------------------|
| 2017-03-14 00:00:00.000          |
+----------------------------------+

When you should use CAST() Function in Snowflake?

There are certain use case scenarios when it is recommended to use the CAST function within the Snowflake cloud data warehouse which are as follows:

  • You want to convert the data type from one format to other.
  • To change the format in table data.

Real World Use Case Scenarios for CAST Function in Snowflake

  • Modifying base model which looks similarly like top model.
  • Converting jeans pants to demin shorts.

CAST() Snowflake Official Documentation Link

Final Thoughts

In this article, we have learned about the CAST() function and its uses with the examples explained clearly. I have also covered different scenarios with a practical example that could be possible. I hope the information that was provided is helped in gaining the knowledge.

Please share your comments and suggestions in the comment section below and I will try to answer all your queries as time permits.

Leave a Comment