STRING type

Applies to: Databricks SQL Databricks Runtime

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

Syntax

SQL

STRING

Literals

SQL

[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 or VIEW, the default collation matches the default collation of that TABLE, or VIEW.
• 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

SQL

> 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

Related

• cast function