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)
• 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: '..'
• 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
• 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. currently disabled as it conflicts with ".." identifiers