STRING type

Applies to: Databricks SQL 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: Databricks SQL 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.

Chaining literals

Applies to: Databricks Runtime 18.0 and later

You can chain string literals directly without the need to use a concat function or || operator. This is convenient to:

• break long literals into multiple lines
• combine string literals and string parameter markers where you can't use expressions, such as in a table LOCATION, OPTIONS, or COMMENT clause. For example a table LOCATION, OPTIONS, or COMMENT clause.

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

> EXECUTE IMMEDIATE 'SELECT \'Hello \' :loc \'!\'' USING 'world' AS loc;
  Hello World!

> EXECUTE IMMEDIATE
    'SELECT '
        '1 AS one,'
        '2 AS two';
  1 2

Related

• cast function