STRING type

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

The type supports character sequences of any length greater or equal to 0.

Syntax

STRING

Literals

[r|R]'c [ ... ]'

  • r or R

    Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 10.4 LTS and above

    Optional prefix denoting a raw-literal.

  • c

    Any character from the Unicode character set.

The following escape sequences are recognized in regular string literals (without the r prefix) and replaced according to the following rules:

  • \0 -> \u0000, unicode character with the code 0;
  • \b -> \u0008, backspace;
  • \n -> \u000a, linefeed;
  • \r -> \u000d, carriage return;
  • \t -> \u0009, horizontal tab;
  • \Z -> \u001A, substitute;
  • \% -> \%;
  • \_ -> \_;
  • \<other char> -> <other char>, skip the slash and leave the character as is.

If the string is prefixed with r there is no escape character.

The inherent collation of a STRING type is inherited from the context in which it is used:

  • Strings produced by functions derive the collation from the function's input arguments.
  • Within a CREATE or ALTER of a TABLE, VIEW, or FUNCTION, the default collation matches the default collation of that TABLE, VIEW, or FUNCTION.
  • Within the context of a top level UPDATE , DELETE, INSERT, MERGE or query statement the default collation is UTF8_BINARY.

For collation that ignores trailing spaces, use the COLLATE UTF8_BINARY_RTRIM clause.

See the collation precedence rules for more information.

Note

While you can use double quotes (") instead of single quotes (') to delimit a string literal, this is discouraged as it is not standard SQL.

Warning

Some client interfaces perform macro substitutions on strings before sending them to the SQL parser. For example in a Databricks notebook the $ (dollar) sign introduces a widget and needs to be escaped with \$ to be preserved in a string literal.

Examples

> SELECT 'Spark';
  Spark

> SELECT CAST(5 AS STRING);
  5

> SELECT 'O\'Connell'
  O'Connell

> SELECT 'Some\nText'
  Some
  Text

> SELECT r'Some\nText'
  Some\nText

> SELECT '서울시'
  서울시

> SELECT ''

> SELECT '\\'
  \

> SELECT r'\\'
  \\

-- From a notbook cell (without a widget 'e' being defined)
> SELECT 'Hou$e', 'Hou\$e', r'Hou$e', r'Hou\$e'
  Hou  Hou$e  Hou  Hou$e

> SELECT COLLATION('hello');
  UTF8_BINARY

> CREATE TEMPORARY VIEW v DEFAULT COLLATION UNICODE_CI
  AS SELECT 'hello' AS c1;
> SELECT COLLATION FOR(c1) FROM v;
  UNICODE_CI