Learn The Snowflake LEN() Function With 20 Examples

In most programming environments including Snowflake, the LEN() function is ubiquitous. It is a popular function commonly used for string manipulation.

If you’re interested in mastering how to write Snowflake queries and also how to use the LEN() function in real-world scenarios, these exercises can help you achieve that.

Table of Contents show

LEN(), LENGTH() Syntax

There are two length functions in Snowflake.

LEN(<expression>) returns int

LENGTH(<expression>) returns int

The expression argument can be a constant, variable, or column of either character or binary data.

According to Snowflake, the LEN() or LENGTH() function

requires at least 1 argument (<expression>)
and returns an integer value, precisely NUMBER(18,0)

For the exercises below, we will be using the LEN() function. Just know you can replace it with the LENGTH() function if you choose to do so.

Dataset

Download the Sample Superstore 2022 dataset to follow along. You can visit the datasets page to see other available download options.

Let’s get started.

Basic SELECT Query

1. Select the Customer Names and the length of each Customer Name. Sort the length of Customer Names in descending order. Your result should not contain duplicates.

SELECT DISTINCT
	CUSTOMER_NAME
	,LEN(CUSTOMER_NAME) AS CUSTOMER_NAME_LENGTH
FROM 
ORDERS
ORDER BY CUSTOMER_NAME_LENGTH DESC

2. Select the length of the following fields and give them appropriate aliases.

Ship Date
Customer Name
Ship Mode
Customer ID
Product Name
Sales
State/Province
Quantity
Discount
Profit

SELECT 
	LEN(Ship_Date) AS Ship_Date_Length
	,LEN(Customer_Name) AS Customer_Name_Length
	,LEN(Ship_Mode) AS Ship_Mode_Length
	,LEN(Customer_ID) AS Customer_ID_Length
	,LEN(Product_Name) AS Product_Name_Length
	,LEN(Sales) AS Sales_Length
        ,LEN(State_Province) AS State_Province_Length
        ,LEN(Quantity) AS Quantity_Length
        ,LEN(Discount) AS Discount_Length
        ,LEN(Profit) AS Profit_Length
FROM ORDERS

Filtering

3. Select the Product Names where the length of the Product Name is greater than 90. Sort the result by Product Name. Your result should not contain duplicates.

SELECT DISTINCT
	Product_Name
	,LEN(Product_Name) AS Product_Name_Length
FROM 
Orders
WHERE 
       LEN(Product_Name) > 90
ORDER BY Product_Name

4. Select Product Names where the length of the Product Name is greater than 75 and the Profit is greater than 100. Your result should not contain duplicates.

SELECT DISTINCT
	Product_Name
	,LEN(Product_Name) AS Product_Name_Length
FROM Orders
WHERE 
       LEN(Product_Name) > 75
       AND Profit > 100
ORDER BY Product_Name

5. Select Product Names where the length of the Product Name is greater than 70 and the Region is in the North or South. Sort the result by Product Name, and then by the Product Name length. The result should not contain duplicates.

SELECT DISTINCT
    Product_Name
    ,Region
    ,LEN(Product_Name) AS Product_Name_Length
FROM Orders
WHERE
    LEN(Product_Name) > 70
    AND Region IN ('North', 'South')
ORDER BY 
    Product_Name, 
    LEN(Product_Name)

6. Select Customer Names and their lengths where the length of the Product Name is greater than 55 and the length of the Customer Name is at least 7. Sort the result by the Customer Name length. The result should not contain duplicates.

SELECT DISTINCT
	Customer_Name
	,LEN(Customer_Name) AS Customer_Name_Length
FROM 
Orders
WHERE 
       LEN(Product_Name) > 55
       AND LEN(Customer_Name) <= 7
ORDER BY Customer_Name_Length

Conditional Statements

7. Select Product Name and Product Length Category for the length of Product Names. The result should not contain duplicates.

Product Length	Product Length Category
0 – 50	Short
51 – 100	Medium
above 100	Long

SELECT DISTINCT
	Product_Name
	,CASE 
	WHEN LEN(Product_Name) <= 50 THEN 'Short'
	WHEN LEN(Product_Name) > 50 AND LEN(Product_Name) <= 100 THEN 'Medium'
	WHEN LEN(Product_Name) > 100 THEN 'Long'
	END AS Product_Length_Category
FROM 
Orders

Aggregation

8. Using the Product Length Category above, select Product Length Category and the total number of Orders. Sort the result by the Product Length Category.

SELECT DISTINCT
	CASE 
	WHEN LEN(Product_Name) <= 50 THEN 'Short'
	WHEN LEN(Product_Name) > 50 AND LEN(Product_Name) <= 100 THEN 'Medium'
	WHEN LEN(Product_Name) > 100 THEN 'Long'
	END AS Product_Length_Category
    ,COUNT(*) AS Product_Length_Category_Count
FROM 
Orders
GROUP BY 
    Product_Length_Category
ORDER BY 
    Product_Length_Category DESC

9. What is the longest Product Name, shortest Product Name length, and average Product Name length? Round the average length to the nearest whole number.

SELECT 
	MAX(LEN(Product_Name)) AS Longest_Length
	,MIN(LEN(Product_Name)) AS Shortest_Length
	,ROUND(AVG(LEN(Product_Name))) AS Average_Length
FROM Orders

10. What is the average length of Product Names between 65 and 100? Round the average length to one decimal place.

SELECT
	ROUND(AVG(LEN(Product_Name)),1)
FROM Orders
WHERE 
    LEN(Product_Name) BETWEEN 65 AND 100

11. Select the length of the Customer Name, the sum of Quantity, and the sum of Profit grouped by the length of the Customer Name where the Quantity is above 500. Sort the result by Profit descending.

SELECT
	LEN(Customer_Name) AS Customer_Name_Length
	,SUM(Quantity) AS Total_Quantity
	,SUM(Profit) AS Total_Profit
FROM 
Orders
GROUP BY
    Customer_Name_Length
HAVING 
    Total_Quantity > 500
ORDER BY
    Total_Profit DESC

Window Functions

12. Select the City and second highest Sale for every City length.

SELECT
	City
	,LEN(City) AS City_Length
	,Sales
	,RANK() OVER (PARTITION BY LEN(City) ORDER BY Sales DESC) AS City_Length_Rank
FROM Orders
)
SELECT 
City
,City_Length
,Sales
FROM CTE
WHERE 
    City_Length_Rank = 2

Arithmetic Statements

13. List all cities where the length of the Customer_Name is an even number. The result should not contain duplicates.

SELECT DISTINCT
	Customer_Name
	,LEN(Customer_Name) AS Customer_Name_Length
FROM Orders
WHERE 
    LEN(Customer_Name) % 2 = 0
ORDER BY 
    Customer_Name

14. List all cities where the length of the Customer_Name is an odd number. The result should not contain duplicates.

SELECT DISTINCT
	Customer_Name
	,LEN(Customer_Name) AS Customer_Name_Length
FROM [Orders]
WHERE 
    LEN(Customer_Name) % 2 = 1
ORDER BY 
    Customer_Name

15. Select all Orders in the South Region where the length of the Product Name is greater than the length of the City, State, and Postal Code.

SELECT 
    *
FROM 
Orders
WHERE 
    LEN([Product Name]) < LEN(CONCAT(City, [State], [Postal Code]))
AND Region = 'South'

Date Statements

16. Select Customer Name, Product Length Name, Order Date, and New Order Date where the Category starts with Office. The New Order Date is the Order Date plus the length of the Product Name. The result should not contain duplicates.

SELECT DISTINCT
	Customer_Name
	,LEN(Product_Name) AS Product_Name_Length
	,Order_Date
	,DATEADD(DAY, LEN(Product_Name), Order_Date) AS  New_Order_Date
FROM Orders
WHERE 
    Category LIKE 'Office%'

17. Select every City where the length of the City is equal to the Ship Date day. Sort the result by the length of the City followed by the Ship Date. The result should not contain duplicates.

SELECT DISTINCT
	City
	,Ship_Date
FROM Orders
WHERE 
    LEN(City) = DAY(Ship_Date)
ORDER BY LEN(City), Ship_Date

18. List all Orders where the difference in days between the Order Date and Ship Date is less than or equal to the length of the City.

SELECT 
	*
FROM Orders
WHERE 
    DATEDIFF(DAY, Order_Date,  Ship_Date) <= LEN(City)

Common Table Expressions & Sub Queries

19. Select the shortest and longest City names. If there is more than one shortest or longest City name, choose the one that comes first when ordered alphabetically. Sort the result by the length of the City name.

WITH 
ShortestName AS
(
    SELECT DISTINCT TOP 1 
        City, 
        LEN(City) City_Length
    FROM Orders
    ORDER BY LEN(City), City
), 
LongestName AS
(
    SELECT DISTINCT TOP 1 
        City, 
        LEN(City) City_Length
    FROM Orders
    ORDER BY LEN(City) DESC, City)

SELECT * 
FROM ShortestName 
UNION 
SELECT * 
FROM LongestName
ORDER BY City_Length

20. For each City length, return the longest City name. If there is more than one shortest or longest City name, choose the one that comes first when ordered alphabetically. Sort the result by the length of the City name.

SELECT
City
,City_Length
,O2.Row_Rank
FROM(
	SELECT
		City, 
		City_Length
		,RANK() OVER (PARTITION BY LEN(City) ORDER BY LEN(City), City) AS Row_Rank
	FROM 
	(
		SELECT DISTINCT
		City
		,LEN(City) AS City_Length
		FROM Orders
	) O1
) O2
where Row_Rank = 1
ORDER BY 
City_Length

I hope you enjoyed these exercises.

Happy Analyzing!