Text data types

Data Types and Functions in Snowflake

Jake Roach

Field Data Engineer

Data types and functions in Snowflake

Snowflake Logo Icon

Snowflake is used by more than 10,000 organizations globally

Data Types

Manipulating Data

Advanced Techniques

¹ https://www.snowflake.com/en/company/overview/about-snowflake/

Snowflake text data

Text data is one of the most popular data types in Snowflake

Categorical values
Messages
Phone numbers
Unstructured text

-- Text data might be categorical
'Software Engineer'


-- Messages are also text data
'Hello, World!'


-- As well as phone numbers
'931-663-0164'


-- And even things like this!
'Taylormade,Titleist,Ping'

Text data types

CREATE TABLE TABLE_NAME (

    message VARCHAR(100),

    phone_number TEXT,

    brands STRING

);

Define column with VARCHAR(...)

TEXT
STRING

              message     |  phone_number  |             brands                    
          --------------- | -------------- | ----------------------------          
           Hello, World!  |  931-663-0164  |  Taylormade, Titleist, Ping           
           Goodnight!     |  617-256-3086  |  Apple, Windows, Linux

Columns that store text data can look quite different!

Describing a Snowflake table

DESCRIBE TABLE <SCHEMA>.<table_name>;

Outputs information about each column in the table
name, type, default, ...

The result of running a DESCRIBE TABLE on a Snowflake table.

Text data in queries

SELECT
    <field>,
    <another-field>,
    -- Can be used in a SELECT
    '<value>'
FROM SCHEMA.TABLE_NAME;

SELECT
    *
FROM SCHEMA.TABLE_NAME
-- Can be used in a WHERE clause
WHERE FIELD = '<value>';

Text values can be used in different parts of a SQL query
SELECT
WHERE
CASE

'...' Single Quotes

Case-sensitive

Manipulating text data

Function	Description
`LENGTH`	Find the number of characters in a text value.
`TRIM`	Remove a sequence of characters at the beginning or end of a string.
`RTRIM`	Same functionality as `TRIM`, only applied to right-side of the string.
`LTRIM`	Same functionality as `TRIM`, only applied to left-side of the string.
`SPLIT`	Split a string using some delimiter, like a comma.
`SEARCH`	Search a string for a certain value.
`CAST`, `::`	Convert other data types into a `VARCHAR` data type.

Let's practice!

Data Types and Functions in Snowflake