# 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.

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

Singlebyte

16,777,216

Multibyte

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.

### 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.

### 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 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

xhhh

For example:

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

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


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