What is the full syntax of a string literal in T-SQL?
I could not find any explicit definition of string syntax in the official documentation, and any blog post I could find only mentioned certain aspects (like the N prefix). Additionally, any posts on Stack Overflow or other sites that asked how to escape a string had answers that pointed to using parameters instead of answering the question, which doesn’t provide me the information I’m looking for.
From what I can tell, a string literal starts with an optional N followed by a wrapper of single quotes (or double quotes, if a setting is set a certain way), single quotes within the string are escaped by doubling the single quote, and backslashes are escaped by doubling the backslash (possibly only if a setting is set a certain way?).
Is there an authoritative and/or comprehensive source for this information? Even just a list of reserved/special characters would be better than what I was able to find.
Advertisement
Answer
The documentation I could not find is here (thanks @GordonLinoff): https://docs.microsoft.com/en-us/sql/t-sql/data-types/constants-transact-sql?view=sql-server-ver15
Character string constants are enclosed in single quotation marks and include alphanumeric characters (a-z, A-Z, and 0-9) and special characters, such as exclamation point (!), at sign (@), and number sign (#).
If the QUOTED_IDENTIFIER option has been set OFF for a connection, character strings can also be enclosed in double quotation marks, but the Microsoft SQL Server Native Client Provider and ODBC driver automatically use SET QUOTED_IDENTIFIER ON. We recommend using single quotation marks.
If a character string enclosed in single quotation marks contains an embedded quotation mark, represent the embedded single quotation mark with two single quotation marks. This is not required in strings embedded in double quotation marks.
Unicode strings have a format similar to character strings but are preceded by an N identifier (N stands for National Language in the SQL-92 standard). The N prefix must be uppercase.
So, by that definition, a Unicode string in the recommended format has the following syntax (as PCRE):
N'(?:[^']|'')*'