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

VARCHAR holds Unicode characters.

When you declare a column of type VARCHAR, you can specify an optional parameter (N), which is the maximum number of characters to store. For example:

create table t1 (v varchar(16777216));

If no length is specified, the default is the maximum allowed length (16,777,216).

Although a VARCHAR’s maximum length is specified in characters, a VARCHAR is also limited to a maximum number of bytes (16,777,216 (16MB)). The maximum number of Unicode characters that can be stored in a VARCHAR column is shown below:

Single-byte

16,777,216.

Multi-byte

Between 8,388,608 (2 bytes per character) and 4,194,304 (4 bytes per character).

For example, if you declare a column as VARCHAR(16777216), the column can hold a maximum of 8,388,608 2-byte Unicode characters, even though you specified a maximum length of 16777216.

A column consumes storage for only 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 length. 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.

Internal Representation

The BINARY data type holds a sequence of 8-bit bytes.

When Snowflake displays BINARY data values, Snowflake often represents each byte as 2 hexadecimal characters. For example, the word “HELP” might be displayed as 48454C50, where “48” is the hexadecimal equivalent of the ASCII (Unicode) letter “H”, “45” is the hexadecimal representation of the letter “E”, etc.

For more information about entering and displaying BINARY data, see: Binary Input and Output.

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) refer 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 Requirements.

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

xhh

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 might 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 $  |
+------+-------------------------------------------------------+