Strings

SQL standard supports single-quoted strings '..' with repeated quote '' used for escaping. The real world implementations have lots of variation:

• BigQuery:
  • "..", '..', """...""", '''..''' (backslash \ used for escaping)
  • R"..", r'''..''' denotes raw strings (backslashes not used for escaping)
  • B"..", b'''..''' denotes binary strings (backslashes not used for escaping)
  • RB"..", br'''..''' the same as above, raw and binary (backslashes not used for escaping)
• DB2:
  • '..' (two single quotes '' are used for escaping)
  • X'..' a hex string (no escaping)
  • G'..', N'..' a graphic string
  • BX'..' a binary hex string (no escaping)
  • GX'..' a graphic hex string (no escaping)
  • UX'..' a Unicode hex string / UCS-2 graphic string (no escaping)
  • U&'..' a Unicode string (two single quotes '' are used for escaping)
• DB2i: The same as DB2, but U&'..' seems to be not supported.
• DuckDB:
  • '..' (two single quotes '' are used for escaping)
  • E'..', e'..' string with C-style escapes (backslash \ or repeated single-quote '' used for escaping)
  • $$..$$, $delim$..$delim$ dollar-quoted string with optional custom delimiters
  • B'..', b'..' bit string
  • X'..', x'..' hex string
  • Also two strings literals separated by newline are treated as implicitly concatenated.
• Hive: '..', ".." (backslash \ used for escaping)
• MariaDB:
  • '..', ".."² (backslash \¹ or repeated single-quote '' used for escaping)
  • x'..', X'..' hex string⁶
  • b'..', B'..' a binary string⁶
• MySQL:
  • '..', ".."² (backslash \¹ or repeated quote ('' or "") used for escaping)
  • x'..', X'..' hex string⁶
  • N'..', n'..' a string using a natural character set⁶
• N1QL: ".." (backslash \ used for escaping)
• PL/SQL:
  • '..' (two single quotes '' are used for escaping)
  • N'..', n'..' a string using a natural character set
  • Q'x..x', q'x..x' where x is a custom delimiter character
  • q'{..}', q'[..]', q'<..>', q'(..)' special handling for certain delimiters in above syntax
• PostgreSQL:
  • '..' (two single quotes '' are used for escaping)
  • E'..', e'..' string with C-style escapes (backslash \ or repeated single-quote '' used for escaping)
  • U&'..', u&'..' string with unicode escapes
  • $$..$$, $delim$..$delim$ dollar-quoted string with optional custom delimiters
  • B'..', b'..' bit string
  • X'..', x'..' hex string
• Redshift: '..' (two quotes '' are used for escaping)
• SingleStoreDB:⁵
  • '..', ".." (backslash \ or repeated quote ('' or "") used for escaping)
  • x'..', X'..' hex string⁶
  • b'..', B'..' a binary string⁶
• Snowflake:
  • '..' (backslash \ or repeated quote '' used for escaping)
  • $$..$$ (no escaping)
• Spark:
  • '..', ".." (backslash \ used for escaping)
  • r'..', r"..", R'..', R".." raw string (no escaping)
  • X'..', X".." hex string
• SQLite:
  • '..', (".."⁴) (two quotes '' are used for escaping)
  • X'..', x'..' hex string
• TiDB:
  • '..', ".."² (backslash \¹ or repeated quote ('' or "") used for escaping)
  • x'..', X'..' hex string⁶
  • N'..', n'..' a string using a natural character set⁶
• Transact-SQL:
  • '..' (two single quotes '' are used for escaping)
  • (".."³)
  • N'..' (N".."³) unicode strings
• Trino:
  • '..' (two single quotes '' are used for escaping)
  • X'..', x'..' hex string
  • U&'..', u&'..' string with unicode escapes (two single quotes '' are used for escaping)

Notes:

1. unless the SQL_MODE has been set to NO_BACKSLASH_ESCAPES.
2. unless ANSI_QUOTES mode is enabled.
3. if the QUOTED_IDENTIFIER option has been set OFF.
4. SQLite sometimes bends its quoting rules. It treats double-quoted identifiers as strings when they appear in the context where strings are expected, but identifiers are not.
5. Tested SingleStoreDB manually. Haven't found any actual documentation about string syntax.
6. Only single-quoted versions are supported.