Adding a string that contains a single quote in Snowflake can be done using one of three primary methods: doubling the single quote, using a backslash escape sequence, or enclosing the entire string in dollar signs.
1. Doubling the single quote
This is the standard SQL method and is the most common way to handle single quotes inside a string literal. To include a single quote character, simply type two adjacent single quotes. Snowflake interprets the pair '' as a single literal quote within the string.
Example
SELECT 'This string contains a single quote: children''s playground.';
Use code with caution.
Output:This string contains a single quote: children's playground.
This method is reliable, simple, and works across most SQL dialects, making your code more portable. It is also the behavior you'll see in the output of functions like GET_DDL().
2. Using a backslash escape sequence
The backslash (\) is Snowflake's escape character, and it can be used to precede and escape a single quote. This is an alternative to doubling the quote, and some developers may find it more explicit.
Example
SELECT 'This string contains a single quote: children\'s playground.';
Use code with caution.
Output:This string contains a single quote: children's playground.
Important note: If your string must also include a literal backslash, you must escape the backslash itself by using two backslashes (\\).
Example with backslash
SELECT 'The file path is C:\\user\\name';
Use code with caution.
Output:The file path is C:\user\name
3. Using a dollar-quoted string constant
If your string contains many single quotes or other special characters, managing the escapes can become cumbersome. In this case, a dollar-quoted string constant is an elegant solution. This syntax uses a pair of dollar signs ($$) to enclose the string. Everything inside the dollar signs is treated as a literal string, with no escaping required for single quotes, backslashes, or other special characters.
Example
SELECT $$This string contains a single quote: children's playground.$$ as my_string;
Use code with caution.
Output:This string contains a single quote: children's playground.
Example with complex characters
This approach is particularly useful for complex strings, such as defining JavaScript stored procedures, where multiple types of quotes and special characters may be used.
SELECT $$It's a beautiful day, and the user said, "I'll be there." The file path is C:\Windows.$$ as my_string;
Use code with caution.
Output:
It's a beautiful day, and the user said, "I'll be there." The file path is C:\Windows.
Which method should you use?
| Method | Best for | Pros | Cons |
|---|---|---|---|
Doubling the quote ('') |
Simple, standard SQL strings. | - Standard, portable SQL. - Easy to read for a few quotes. | Can become verbose and less readable with many quotes. |
Backslash escape (\') |
Programmatic string generation or when adhering to specific coding standards. | - Explicitly signals the escape. - A common approach in many languages. | Requires escaping backslashes themselves, which can add complexity. |
Dollar-quoted ($$...$$) |
Complex strings with many special characters, such as SQL within a JavaScript stored procedure. | - No escaping needed inside the delimiters. - Excellent readability for complex strings. | Less common for simple, single-quote cases. |
Practical application in DML statements
All three methods are valid for DML statements like INSERT, UPDATE, and MERGE.
Example INSERT statement
The examples below show how to use each method in an INSERT statement, assuming a table my_table with a varchar column named string_col:
Using doubled quotes ('')
INSERT INTO my_table (string_col)
VALUES ('The artist''s workshop');
Use code with caution.
Using backslash escape (\')
INSERT INTO my_table (string_col)
VALUES ('The artist\'s workshop');
Use code with caution.
Using dollar-quoted ($$...$$)
INSERT INTO my_table (string_col)
VALUES ($$The artist's workshop$$);
Use code with caution.