SELECT with Snowflake functions - Commands & Configuration

Introduction

When you want to get specific data from your Snowflake database, you use the SELECT statement. Snowflake functions help you change or calculate data as you get it, like changing text to uppercase or adding numbers.

When you want to see all customers whose names start with 'A'.

When you need to calculate the total price of items in an order.

When you want to change a date format to something easier to read.

When you want to count how many orders were placed last month.

When you want to combine first and last names into one full name.

Commands

This command selects the first names from the customers table and changes them to uppercase. It also calculates the length of the last names and only shows customers whose last names are longer than 5 characters.

Terminal

SELECT UPPER(first_name) AS upper_name, LENGTH(last_name) AS last_name_length FROM customers WHERE LENGTH(last_name) > 5;

Expected Output

UPPER_NAME   | LAST_NAME_LENGTH

JOHN         | 7

ALICE        | 6

This command combines first and last names into a full name for customers who signed up after January 1, 2023.

Terminal

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM customers WHERE signup_date > '2023-01-01';

Expected Output

FULL_NAME

John Smith

Alice Johnson

This command selects order IDs and changes the order date into a simple year-month-day format for orders placed in 2024 or later.

Terminal

SELECT order_id, TO_VARCHAR(order_date, 'YYYY-MM-DD') AS formatted_date FROM orders WHERE order_date >= '2024-01-01';

Expected Output

ORDER_ID | FORMATTED_DATE

101      | 2024-02-15

102      | 2024-03-10

Key Concept

If you remember nothing else from this pattern, remember: Snowflake functions let you change or calculate data right when you select it, making your results more useful.

Common Mistakes

Using single quotes around column names instead of double quotes or no quotes.

Single quotes are for string values, not column names, so the query will fail.

Use double quotes for column names if needed or no quotes if the name is simple.

Forgetting to alias calculated columns with AS.

Without an alias, the output column name can be unclear or default to the function expression.

Always use AS to give a clear name to calculated columns.

Using functions on columns without considering NULL values.

Functions may return NULL or cause errors if the column has NULLs.

Use COALESCE or check for NULLs before applying functions.

Summary

Use SELECT with Snowflake functions to transform or calculate data as you retrieve it.

Alias your calculated columns with AS for clear output names.

Filter your data with WHERE to get only the rows you need.