length in snowflake

What is LENGTH() function in Snowflake?

by

Are you looking to find what is the LENGTH() function within the Snowflake cloud data warehouse or maybe you are looking for a solution about how to use the length functions in the Snowflake? In this blog post I will show you what and how to use LENGTH function. I will also explain the LENGTH function by taking a practical example. So don’t waste time let’s start step by step guide to understand the LENGTH expression in the Snowflake.

LENGTH() Function will be used to get the LENGTH of the expression that might be string or a binary value as per the user requirement. If the argument value is empty then the result value will be zero. Examples are also provided which will help you to understand in better way. The resultant data type that returns INTEGER type

LENGTH() function consists of one argument which need to be filled mandatorily as shown below.

Syntax of Length Function in Snowflake

Syntax : LENGTH( Expression )

LENGTH argument details

First paramter (Expression)String or Binary value (Mandatory)

LENGTH function example in Snowflake:

--Example 1 : 

SELECT LENGTH('abcxyz123'); 

--Query Result :

+-------------------------+
| LENGTH('ABCXYZ123')     |
|-------------------------|
| 9                       |
+-------------------------+ 


--Example 2 : 

select NAME,LENGTH(NAME) from DEMO.PUBLIC.EMP
 
--Query Result :

+--------+--------------+
| NAME   | LENGTH(NAME) |
|--------+--------------|
| Ram    |            3 |
| RAJU   |            4 |
| Sameer |            6 |
+--------+--------------+

Here is the complete example provided below which will help us to check the lengths according to the datatypes.

--Example : 

create table DEMO.PUBLIC.binary_table (v varchar,
                                                    b_hex binary, b_base64 binary, b_utf8 binary);
                                                  insert into binary_table (v) values ('hello');
                                                  update binary_table set
                                                    b_hex    = to_binary(hex_encode(v), 'HEX'),
                                                    b_base64 = to_binary(base64_encode(v), 'BASE64'),
                                                    b_utf8   = to_binary(v, 'UTF-8');

+------------------------------------------+
| status                                   |
|------------------------------------------|
| Table BINARY_TABLE successfully created. |
+------------------------------------------+
+-------------------------+
| number of rows inserted |
|-------------------------|
|                       1 |
+-------------------------+
1 Row(s) produced. Time Elapsed: 0.865s
+------------------------+-------------------------------------+
| number of rows updated | number of multi-joined rows updated |
|------------------------+-------------------------------------|
|                      1 |                                   0 |
+------------------------+-------------------------------------+


--Query Result :

select v, length(v), to_varchar(b_hex, 'HEX') as b_hex, length(b_hex), to_varchar(b_base64, 'BASE64') as b_base64, length(b_base64), to_varchar(b_utf8, 'UTF-8') as b_utf8, length(b_utf8) 
 from demo.public.binary_table;

+-------+-----------+------------+---------------+----------+------------------+--------+----------------+
| V     | LENGTH(V) | B_HEX      | LENGTH(B_HEX) | B_BASE64 | LENGTH(B_BASE64) | B_UTF8 | LENGTH(B_UTF8) |
|-------+-----------+------------+---------------+----------+------------------+--------+----------------|
| hello |         5 | 68656C6C6F |             5 | aGVsbG8= |                5 | hello  |              5 |
+-------+-----------+------------+---------------+----------+------------------+--------+----------------+

How to check the length of column in Snowflake?

To check the length of the column or to find the length of the column we can use the length function of the string. Here you will pass the column name and you will get the length of the column.

select length(address) from CUSTOMER;

-- Output
+-----------------+
| LENGTH(ADDRESS) |
|-----------------|
|               8 |
|              13 |
|               2 |
|              10 |
+-----------------+
4 Row(s) produced. Time Elapsed: 0.347s

Length Snowflake official Documentation Link

Final Thoughts

In this article we have learned about LENGTH function and uses with the examples explained clearly. I have also covered different scenario with practical example that could be possible. I hope the information that was provided is helped in gaining the knowledge.

Join Telegram Group
Download Free Azure Videos
Connect on LinkedIn

Unicode in Snowflake

Repeat function in Snowflake

Revanth Kumar