String & Binary Data Types

This topic describes the string/text data types, including binary strings, supported in Snowflake, along with the supported formats for string constants/literals.

In this Topic:

Data Types for Text Strings

Snowflake supports the following data types for text (i.e. character) strings.

VARCHAR

The maximum length is 16MB (uncompressed). The maximum number of Unicode characters that can be stored in a VARCHAR column depends on whether the characters are single-byte or multi-byte:

Single-byte:16,777,216
Multi-byte:Between 8,388,608 (2 bytes per character) and 4,194,304 (4 bytes per character)

If a length is not specified, the default is the maximum length.

A column only consumes storage for the amount of actual data stored. For example, a 1-character string in a VARCHAR(16777216) column only consumes a single character.

There is no performance difference between using the full-length VARCHAR declaration VARCHAR(16777216) or a smaller size. Note that in any relational database, SELECT statements in which a WHERE clause references VARCHAR columns or string columns are not as fast as SELECT statements filtered using a date or numeric column condition.

Some BI/ETL tools define the maximum size of the VARCHAR data in storage or in memory. If you know the maximum size for a column, you could limit the size when you add the column.

CHAR , CHARACTER

Synonymous with VARCHAR, except that if the length is not specified, CHAR(1) is the default.

Note

Snowflake currently deviates from common CHAR semantics in that strings shorter than the maximum length are not space-padded at the end.

STRING , TEXT

Synonymous with VARCHAR.

String Examples in Table Columns

CREATE OR REPLACE TABLE test_text(v VARCHAR,
                                  v50 VARCHAR(50),
                                  c CHAR,
                                  c10 CHAR(10),
                                  s STRING,
                                  s20 STRING(20),
                                  t TEXT,
                                  t30 TEXT(30)
                                  );

DESC TABLE test_text;

+------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+
| name | type              | kind   | null? | default | primary key | unique key | check | expression | comment |
|------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------|
| V    | VARCHAR(16777216) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
| V50  | VARCHAR(50)       | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
| C    | VARCHAR(1)        | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
| C10  | VARCHAR(10)       | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
| S    | VARCHAR(16777216) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
| S20  | VARCHAR(20)       | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
| T    | VARCHAR(16777216) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
| T30  | VARCHAR(30)       | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
+------+-------------------+--------+-------+---------+-------------+------------+-------+------------+---------+

Data Types for Binary Strings

Snowflake supports the following data types for binary strings.

BINARY

The maximum length is 8 MB (8,388,608 bytes). Unlike VARCHAR, the BINARY data type has no notion of Unicode characters, so the length is always measured in terms of bytes.

If a length is not specified, the default is the maximum length.

VARBINARY

VARBINARY is synonymous with BINARY.

Binary Examples in Table Columns

CREATE OR REPLACE TABLE test_binary(b BINARY,
                                    b100 BINARY(100),
                                    vb VARBINARY
                                    );

DESC TABLE test_binary;

+------+-----------------+--------+-------+---------+-------------+------------+-------+------------+---------+
| name | type            | kind   | null? | default | primary key | unique key | check | expression | comment |
|------+-----------------+--------+-------+---------+-------------+------------+-------+------------+---------|
| B    | BINARY(8388608) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
| B100 | BINARY(100)     | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
| VB   | BINARY(8388608) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
+------+-----------------+--------+-------+---------+-------------+------------+-------+------------+---------+

String Constants

Constants (also known as literals) refers to fixed data values. String constants in Snowflake must always be enclosed between delimiter characters. Snowflake supports using either single quotes or dollar signs to delimit string constants.

Single-Quoted String Constants

A string constant can be enclosed between single quote delimiters (e.g. 'This is a string'). To include a single quote character within a string constant, type two adjacent single quotes (e.g. ''.

For example:

SELECT 'Today''s sales projections', '-''''-';

+------------------------------+----------+
| 'TODAY''S SALES PROJECTIONS' | '-''''-' |
|------------------------------+----------|
| Today's sales projections    | -''-     |
+------------------------------+----------+

Note

Two single quotes is not the same as the double quote character ("), which is used (as needed) for delimiting object identifiers. For more information, see Identifier Syntax.

Escape Sequences

Within a single-quoted string constant, a single quote character, along with other special characters, can alternatively be included in escape sequences. A backslash character (\) begins a backslash escape sequence.

There are three kinds of escape sequences: simple, octal, and hexadecimal:

Escape Sequence Character Represented
A single quote (') character
A double quote (") character
\ A backslash (\) character
b A backspace character
f A formfeed character
n A newline (linefeed) character
r A carriage return character
t A tab character
ooo ASCII character in octal notation
xhhh ASCII character in hexadecimal notation

For example:

SELECT $1, $2 FROM
VALUES
('Tab','Hel\tlo'),
('Newline','Hel\nlo'),
('Octal','-\041-'),
('Hexadecimal','-\x21-')
;

+-------------+-----+
| $1          | $2  |
|-------------+-----|
| Tab         | Hel lo     |
| Newline     | Hel |
|             | lo  |
| Octal       | -!- |
| Hexadecimal | -!- |
+-------------+-----+

Note that a non-special character escaped with a backslash is simply interpreted as that character, e.g. '\z' becomes 'z'.

Dollar-Quoted String Constants

When a string contains many quote characters, it may be simpler to enclose string constants in dollar signs rather than single quote characters. A dollar-quoted string constant consists of the characters in the string surrounded by a pair of dollar signs ($$).

For example, the following string constants are equivalent:

'string with a \' character'

$$string with a ' character$$

A dollar-quoted string constant can include quotes, or any other character, without the need to escape them. The string content is always written literally. Note that the string constant cannot contain double-dollar signs.

For example:

SELECT $1, $2 FROM VALUES ('row1', $$a
                                      ' \ \t
                                      \0x123 z $ $$);

+------+-------------------------------------------------------+
| $1   | $2                                                    |
|------+-------------------------------------------------------|
| row1 | a                                                     |
|      |                                           ' \ \t      |
|      |                                           \0x123 z $  |
+------+-------------------------------------------------------+