String Functions ¶

This page lists all string functions available in Spark SQL.

char_length(expr) - Returns the character length of string data or number of bytes of binary data. The length of string data includes the trailing spaces. The length of binary data includes binary zeros.

Arguments:

expr - The string or binary value to measure the length of. An expression that evaluates to a string or binary.

Examples:

> SELECT char_length('Spark SQL ');
 10
> SELECT char_length(x'537061726b2053514c');
 9
> SELECT CHAR_LENGTH('Spark SQL ');
 10
> SELECT CHARACTER_LENGTH('Spark SQL ');
 10

Since: 2.3.0

character_length ¶

character_length(expr) - Returns the character length of string data or number of bytes of binary data. The length of string data includes the trailing spaces. The length of binary data includes binary zeros.

Arguments:

expr - The string or binary value to measure the length of. An expression that evaluates to a string or binary.

Examples:

> SELECT character_length('Spark SQL ');
 10
> SELECT character_length(x'537061726b2053514c');
 9
> SELECT CHAR_LENGTH('Spark SQL ');
 10
> SELECT CHARACTER_LENGTH('Spark SQL ');
 10

Since: 2.3.0

chr ¶

chr(expr) - Returns the ASCII character having the binary equivalent to expr. If n is larger than 256 the result is equivalent to chr(n % 256)

Arguments:

expr - The number to convert to its ASCII character. An expression that evaluates to a long.

Examples:

> SELECT chr(65);
 A

Since: 2.3.0

collate ¶

collate(expr, collationName) - Marks a given expression with the specified collation.

Arguments:

expr - String expression to perform collation on.
collationName - Foldable string expression that specifies the collation name.

Examples:

> SELECT COLLATION('Spark SQL' collate UTF8_LCASE);
SYSTEM.BUILTIN.UTF8_LCASE

Since: 4.0.0

collation ¶

collation(expr) - Returns the collation name of a given expression.

Arguments:

expr - String expression to perform collation on. An expression that evaluates to a string.

Examples:

> SELECT collation('Spark SQL');
SYSTEM.BUILTIN.UTF8_BINARY

Since: 4.0.0

concat_ws ¶

concat_ws(sep[, str | array(str)]+) - Returns the concatenation of the strings separated by sep, skipping null values.

Arguments:

sep - The separator placed between the concatenated values. An expression that evaluates to a string.
str - The values to concatenate, skipping null values. There can be one or more of them. Each is an expression that evaluates to a string or an array of strings.

Examples:

> SELECT concat_ws(' ', 'Spark', 'SQL');
  Spark SQL
> SELECT concat_ws('s');

> SELECT concat_ws('/', 'foo', null, 'bar');
  foo/bar
> SELECT concat_ws(null, 'Spark', 'SQL');
  NULL

Since: 1.5.0

contains ¶

contains(left, right) - Returns a boolean. The value is True if right is found inside left. Returns NULL if either input expression is NULL. Otherwise, returns False. Both left or right must be of STRING or BINARY type.

Arguments:

left - The expression to search within. An expression that evaluates to a string or binary.
right - The expression to search for inside the left expression. An expression that evaluates to a string or binary.

Examples:

> SELECT contains('Spark SQL', 'Spark');
 true
> SELECT contains('Spark SQL', 'SPARK');
 false
> SELECT contains('Spark SQL', null);
 NULL
> SELECT contains(x'537061726b2053514c', x'537061726b');
 true

Since: 3.3.0

decode ¶

decode(bin, charset) - Decodes the first argument using the second argument character set. If either argument is null, the result will also be null.

decode(expr, search, result [, search, result ] ... [, default]) - Compares expr to each search value in order. If expr is equal to a search value, decode returns the corresponding result. If no match is found, then it returns default. If default is omitted, it returns null.

Arguments:

bin - a binary expression to decode An expression that evaluates to a binary.
charset - one of the charsets 'US-ASCII', 'ISO-8859-1', 'UTF-8', 'UTF-16BE', 'UTF-16LE', 'UTF-16', 'UTF-32' to decode bin into a STRING. It is case insensitive. An expression that evaluates to a string.

Examples:

> SELECT decode(encode('abc', 'utf-8'), 'utf-8');
 abc
> SELECT decode(2, 1, 'Southlake', 2, 'San Francisco', 3, 'New Jersey', 4, 'Seattle', 'Non domestic');
 San Francisco
> SELECT decode(6, 1, 'Southlake', 2, 'San Francisco', 3, 'New Jersey', 4, 'Seattle', 'Non domestic');
 Non domestic
> SELECT decode(6, 1, 'Southlake', 2, 'San Francisco', 3, 'New Jersey', 4, 'Seattle');
 NULL
> SELECT decode(null, 6, 'Spark', NULL, 'SQL', 4, 'rocks');
 SQL

Note:

decode(expr, search, result [, search, result ] ... [, default]) is supported since 3.2.0

Since: 1.5.0

elt ¶

elt(n, input1, input2, ...) - Returns the n-th input, e.g., returns input2 when n is 2. The function returns NULL if the index exceeds the length of the array and spark.sql.ansi.enabled is set to false. If spark.sql.ansi.enabled is set to true, it throws ArrayIndexOutOfBoundsException for invalid indices.

Examples:

> SELECT elt(1, 'scala', 'java');
 scala
> SELECT elt(2, 'a', 1);
 1

Since: 2.0.0

encode ¶

encode(str, charset) - Encodes the first argument using the second argument character set. If either argument is null, the result will also be null.

Arguments:

str - a string expression An expression that evaluates to a string.
charset - one of the charsets 'US-ASCII', 'ISO-8859-1', 'UTF-8', 'UTF-16BE', 'UTF-16LE', 'UTF-16', 'UTF-32' to encode str into a BINARY. It is case insensitive. An expression that evaluates to a string.

Examples:

> SELECT encode('abc', 'utf-8');
 abc

Since: 1.5.0

endswith ¶

endswith(left, right) - Returns a boolean. The value is True if left ends with right. Returns NULL if either input expression is NULL. Otherwise, returns False. Both left or right must be of STRING or BINARY type.

Arguments:

left - The expression to test. An expression that evaluates to a string or binary.
right - The suffix expression to check the left expression ends with. An expression that evaluates to a string or binary.

Examples:

> SELECT endswith('Spark SQL', 'SQL');
 true
> SELECT endswith('Spark SQL', 'Spark');
 false
> SELECT endswith('Spark SQL', null);
 NULL
> SELECT endswith(x'537061726b2053514c', x'537061726b');
 false
> SELECT endswith(x'537061726b2053514c', x'53514c');
 true

Since: 3.3.0

find_in_set ¶

find_in_set(str, str_array) - Returns the index (1-based) of the given string (str) in the comma-delimited list (str_array). Returns 0, if the string was not found or if the given string (str) contains a comma.

Arguments:

str - The string to search for. An expression that evaluates to a string.
str_array - The comma-delimited list to search within. An expression that evaluates to a string.

Examples:

> SELECT find_in_set('ab','abc,b,ab,c,def');
 3

Since: 1.5.0

format_number ¶

format_number(expr1, expr2) - Formats the number expr1 like '#,###,###.##', rounded to expr2 decimal places. If expr2 is 0, the result has no decimal point or fractional part. expr2 also accept a user specified format. This is supposed to function like MySQL's FORMAT.

Arguments:

expr1 - The number to format. An expression that evaluates to a numeric.
expr2 - The number of decimal places to round to, or a format string. An expression that evaluates to an integer or string.

Examples:

> SELECT format_number(12332.123456, 4);
 12,332.1235
> SELECT format_number(12332.123456, '##################.###');
 12332.123

Since: 1.5.0

format_string ¶

format_string(strfmt, obj, ...) - Returns a formatted string from printf-style format strings.

Arguments:

strfmt - The printf-style format string. An expression that evaluates to a string.
obj - The value to be formatted into the string. An expression of any type.

Examples:

> SELECT format_string("Hello World %d %s", 100, "days");
 Hello World 100 days

Since: 1.5.0

initcap ¶

initcap(str) - Returns str with the first letter of each word in uppercase. All other letters are in lowercase. Words are delimited by white space.

Arguments:

str - The string to capitalize the first letter of each word in. An expression that evaluates to a string.

Examples:

> SELECT initcap('sPark sql');
 Spark Sql

Since: 1.5.0

instr ¶

instr(str, substr[, start[, occurrence]]) - Returns the (1-based) index of the specified occurrence of substr in str, starting the search from position start. If start is positive, the search proceeds forward; if start is negative, the search proceeds backward. start = 0 returns 0. If start is not specified, it defaults to 1. occurrence must be a positive integer and defaults to 1.

Arguments:

str - The string to search within. An expression that evaluates to a string.
substr - The substring to search for. An expression that evaluates to a string.
start - The 1-based position from which to start the search. An expression that evaluates to an integer.
occurrence - Which occurrence of the substring to find. An expression that evaluates to an integer.

Examples:

> SELECT instr('SparkSQL', 'SQL');
 6
> SELECT instr('abcabc', 'b', 1);
 2
> SELECT instr('abcabc', 'b', 1, 2);
 5
> SELECT instr('abcabc', 'b', -1, 1);
 5

Since: 1.5.0

is_valid_utf8 ¶

is_valid_utf8(str) - Returns true if str is a valid UTF-8 string, otherwise returns false.

Arguments:

str - a string expression An expression that evaluates to a string.

Examples:

> SELECT is_valid_utf8('Spark');
 true
> SELECT is_valid_utf8(x'61');
 true
> SELECT is_valid_utf8(x'80');
 false
> SELECT is_valid_utf8(x'61C262');
 false

Since: 4.0.0

jaro_winkler_similarity ¶

jaro_winkler_similarity(str1, str2) - Returns the Jaro-Winkler similarity between the two given strings. The result is a double between 0 and 1, where 1 means identical.

Arguments:

str1 - The first string to compare. An expression that evaluates to a string.
str2 - The second string to compare. An expression that evaluates to a string.

Examples:

> SELECT jaro_winkler_similarity('MARTHA', 'MARHTA');
 0.9611111111111111
> SELECT jaro_winkler_similarity('kitten', 'sitting');
 0.746031746031746
> SELECT jaro_winkler_similarity('ABC', 'XYZ');
 0.0

Since: 4.3.0

lcase ¶

lcase(str) - Returns str with all characters changed to lowercase.

Arguments:

str - The string to convert to lowercase. An expression that evaluates to a string.

Examples:

> SELECT lcase('SparkSql');
 sparksql

Since: 1.0.1

left ¶

left(str, len) - Returns the leftmost len(len can be string type) characters from the string str,if len is less or equal than 0 the result is an empty string.

Arguments:

str - The string to take the leftmost characters from. An expression that evaluates to a string or binary.
len - The number of characters to take from the left. An expression that evaluates to an integer.

Examples:

> SELECT left('Spark SQL', 3);
 Spa
> SELECT left(encode('Spark SQL', 'utf-8'), 3);
 Spa

Since: 2.3.0

len ¶

len(expr) - Returns the character length of string data or number of bytes of binary data. The length of string data includes the trailing spaces. The length of binary data includes binary zeros.

Arguments:

expr - The string or binary value to measure the length of. An expression that evaluates to a string or binary.

Examples:

> SELECT len('Spark SQL ');
 10
> SELECT len(x'537061726b2053514c');
 9
> SELECT CHAR_LENGTH('Spark SQL ');
 10
> SELECT CHARACTER_LENGTH('Spark SQL ');
 10

Since: 3.4.0

length ¶

length(expr) - Returns the character length of string data or number of bytes of binary data. The length of string data includes the trailing spaces. The length of binary data includes binary zeros.

Arguments:

expr - The string or binary value to measure the length of. An expression that evaluates to a string or binary.

Examples:

> SELECT length('Spark SQL ');
 10
> SELECT length(x'537061726b2053514c');
 9
> SELECT CHAR_LENGTH('Spark SQL ');
 10
> SELECT CHARACTER_LENGTH('Spark SQL ');
 10

Since: 1.5.0

levenshtein ¶

levenshtein(str1, str2[, threshold]) - Returns the Levenshtein distance between the two given strings. If threshold is set and distance more than it, return -1.

Arguments:

str1 - The first string to compare. An expression that evaluates to a string.
str2 - The second string to compare. An expression that evaluates to a string.
threshold - The maximum distance to compute; if exceeded, -1 is returned. An expression that evaluates to an integer.

Examples:

> SELECT levenshtein('kitten', 'sitting');
 3
> SELECT levenshtein('kitten', 'sitting', 2);
 -1

Since: 1.5.0

locate ¶

locate(substr, str[, pos]) - Returns the position of the first occurrence of substr in str after position pos. The given pos and return value are 1-based.

Arguments:

substr - The substring to search for. An expression that evaluates to a string.
str - The string to search within. An expression that evaluates to a string.
pos - The 1-based position from which to start the search. An expression that evaluates to an integer.

Examples:

> SELECT locate('bar', 'foobarbar');
 4
> SELECT locate('bar', 'foobarbar', 5);
 7
> SELECT POSITION('bar' IN 'foobarbar');
 4

Since: 1.5.0

lower ¶

lower(str) - Returns str with all characters changed to lowercase.

Arguments:

str - The string to convert to lowercase. An expression that evaluates to a string.

Examples:

> SELECT lower('SparkSql');
 sparksql

Since: 1.0.1

lpad ¶

lpad(str, len[, pad]) - Returns str, left-padded with pad to a length of len. If str is longer than len, the return value is shortened to len characters or bytes. If pad is not specified, str will be padded to the left with space characters if it is a character string, and with zeros if it is a byte sequence.

Arguments:

str - The string to be left-padded. An expression that evaluates to a string or binary.
len - The target length of the resulting string. An expression that evaluates to an integer.
pad - The string to pad with on the left. An expression that evaluates to a string or binary.

Examples:

> SELECT lpad('hi', 5, '??');
 ???hi
> SELECT lpad('hi', 1, '??');
 h
> SELECT lpad('hi', 5);
    hi
> SELECT hex(lpad(unhex('aabb'), 5));
 000000AABB
> SELECT hex(lpad(unhex('aabb'), 5, unhex('1122')));
 112211AABB

Since: 1.5.0

ltrim ¶

ltrim(str) - Removes the leading space characters from str.

Arguments:

str - a string expression An expression that evaluates to a string.
trimStr - the trim string characters to trim, the default value is a single space An expression that evaluates to a string.

Examples:

> SELECT ltrim('    SparkSQL   ');
 SparkSQL

Since: 1.5.0

luhn_check ¶

luhn_check(str ) - Checks that a string of digits is valid according to the Luhn algorithm. This checksum function is widely applied on credit card numbers and government identification numbers to distinguish valid numbers from mistyped, incorrect numbers.

Arguments:

str - The string of digits to validate against the Luhn algorithm. An expression that evaluates to a string.

Examples:

> SELECT luhn_check('8112189876');
 true
> SELECT luhn_check('79927398713');
 true
> SELECT luhn_check('79927398714');
 false

Since: 3.5.0

make_valid_utf8 ¶

make_valid_utf8(str) - Returns the original string if str is a valid UTF-8 string, otherwise returns a new string whose invalid UTF8 byte sequences are replaced using the UNICODE replacement character U+FFFD.

Arguments:

str - a string expression An expression that evaluates to a string.

Examples:

> SELECT make_valid_utf8('Spark');
 Spark
> SELECT make_valid_utf8(x'61');
 a
> SELECT make_valid_utf8(x'80');
 �
> SELECT make_valid_utf8(x'61C262');
 a�b

Since: 4.0.0

mask ¶

mask(input[, upperChar, lowerChar, digitChar, otherChar]) - masks the given string value. The function replaces characters with 'X' or 'x', and numbers with 'n'. This can be useful for creating copies of tables with sensitive information removed.

Arguments:

input - string value to mask. Supported types: STRING, VARCHAR, CHAR An expression that evaluates to a string.
upperChar - character to replace upper-case characters with. Specify NULL to retain original character. Default value: 'X' An expression that evaluates to a string. Must be a constant.
lowerChar - character to replace lower-case characters with. Specify NULL to retain original character. Default value: 'x' An expression that evaluates to a string. Must be a constant.
digitChar - character to replace digit characters with. Specify NULL to retain original character. Default value: 'n' An expression that evaluates to a string. Must be a constant.
otherChar - character to replace all other characters with. Specify NULL to retain original character. Default value: NULL An expression that evaluates to a string. Must be a constant.

Examples:

> SELECT mask('abcd-EFGH-8765-4321');
  xxxx-XXXX-nnnn-nnnn
> SELECT mask('abcd-EFGH-8765-4321', 'Q');
  xxxx-QQQQ-nnnn-nnnn
> SELECT mask('AbCD123-@$#', 'Q', 'q');
  QqQQnnn-@$#
> SELECT mask('AbCD123-@$#');
  XxXXnnn-@$#
> SELECT mask('AbCD123-@$#', 'Q');
  QxQQnnn-@$#
> SELECT mask('AbCD123-@$#', 'Q', 'q');
  QqQQnnn-@$#
> SELECT mask('AbCD123-@$#', 'Q', 'q', 'd');
  QqQQddd-@$#
> SELECT mask('AbCD123-@$#', 'Q', 'q', 'd', 'o');
  QqQQdddoooo
> SELECT mask('AbCD123-@$#', NULL, 'q', 'd', 'o');
  AqCDdddoooo
> SELECT mask('AbCD123-@$#', NULL, NULL, 'd', 'o');
  AbCDdddoooo
> SELECT mask('AbCD123-@$#', NULL, NULL, NULL, 'o');
  AbCD123oooo
> SELECT mask(NULL, NULL, NULL, NULL, 'o');
  NULL
> SELECT mask(NULL);
  NULL
> SELECT mask('AbCD123-@$#', NULL, NULL, NULL, NULL);
  AbCD123-@$#

Since: 3.4.0

octet_length ¶

octet_length(expr) - Returns the byte length of string data or number of bytes of binary data.

Arguments:

expr - The string or binary value to measure the byte length of. An expression that evaluates to a string or binary.

Examples:

> SELECT octet_length('Spark SQL');
 9
> SELECT octet_length(x'537061726b2053514c');
 9

Since: 2.3.0

overlay ¶

overlay(input, replace, pos[, len]) - Replace input with replace that starts at pos and is of length len.

Arguments:

input - The string to have part of its content replaced. An expression that evaluates to a string or binary.
replace - The replacement content to insert. An expression that evaluates to a string or binary.
pos - The 1-based start position of the replacement. An expression that evaluates to an integer.
len - The number of characters to replace. An expression that evaluates to an integer.

Examples:

> SELECT overlay('Spark SQL' PLACING '_' FROM 6);
 Spark_SQL
> SELECT overlay('Spark SQL' PLACING 'CORE' FROM 7);
 Spark CORE
> SELECT overlay('Spark SQL' PLACING 'ANSI ' FROM 7 FOR 0);
 Spark ANSI SQL
> SELECT overlay('Spark SQL' PLACING 'tructured' FROM 2 FOR 4);
 Structured SQL
> SELECT overlay(encode('Spark SQL', 'utf-8') PLACING encode('_', 'utf-8') FROM 6);
 Spark_SQL
> SELECT overlay(encode('Spark SQL', 'utf-8') PLACING encode('CORE', 'utf-8') FROM 7);
 Spark CORE
> SELECT overlay(encode('Spark SQL', 'utf-8') PLACING encode('ANSI ', 'utf-8') FROM 7 FOR 0);
 Spark ANSI SQL
> SELECT overlay(encode('Spark SQL', 'utf-8') PLACING encode('tructured', 'utf-8') FROM 2 FOR 4);
 Structured SQL

Since: 3.0.0

position ¶

position(substr, str[, pos]) - Returns the position of the first occurrence of substr in str after position pos. The given pos and return value are 1-based.

Arguments:

substr - The substring to search for. An expression that evaluates to a string.
str - The string to search within. An expression that evaluates to a string.
pos - The 1-based position from which to start the search. An expression that evaluates to an integer.

Examples:

> SELECT position('bar', 'foobarbar');
 4
> SELECT position('bar', 'foobarbar', 5);
 7
> SELECT POSITION('bar' IN 'foobarbar');
 4

Since: 2.3.0

printf ¶

printf(strfmt, obj, ...) - Returns a formatted string from printf-style format strings.

Arguments:

strfmt - The printf-style format string. An expression that evaluates to a string.
obj - The value to be formatted into the string. An expression of any type.

Examples:

> SELECT printf("Hello World %d %s", 100, "days");
 Hello World 100 days

Since: 1.5.0

quote ¶

quote(str) - Returns str enclosed by single quotes and each instance of single quote in it is preceded by a backslash.

Arguments:

str - The string to enclose in single quotes and escape. An expression that evaluates to a string.

Examples:

> SELECT quote('Don\'t');
 'Don\'t'

Since: 4.1.0

randstr ¶

randstr(length[, seed]) - Returns a string of the specified length whose characters are chosen uniformly at random from the following pool of characters: 0-9, a-z, A-Z. The random seed is optional. The string length must be a constant two-byte or four-byte integer (SMALLINT or INT, respectively).

Arguments:

length - The length of the random string to generate. An expression that evaluates to an integer. Must be a constant.
seed - The seed used to produce reproducible random results. An expression that evaluates to an integer or long. Must be a constant.

Examples:

> SELECT randstr(3, 0) AS result;
 ceV

Since: 4.0.0

regexp_count ¶

regexp_count(str, regexp) - Returns a count of the number of times that the regular expression pattern regexp is matched in the string str.

Arguments:

str - a string expression. An expression that evaluates to a string.
regexp - a string representing a regular expression. The regex string should be a Java regular expression. An expression that evaluates to a string.

Examples:

> SELECT regexp_count('Steven Jones and Stephen Smith are the best players', 'Ste(v|ph)en');
 2
> SELECT regexp_count('abcdefghijklmnopqrstuvwxyz', '[a-z]{3}');
 8

Since: 3.4.0

regexp_extract ¶

regexp_extract(str, regexp[, idx]) - Extract the first string in the str that match the regexp expression and corresponding to the regex group index.

Arguments:

str - a string expression. An expression that evaluates to a string.
regexp - a string representing a regular expression. The regex string should be a Java regular expression.

Since Spark 2.0, string literals (including regex patterns) are unescaped in our SQL parser, see the unescaping rules at String Literal. For example, to match "\abc", a regular expression for regexp can be "^\abc$".

There is a SQL config 'spark.sql.parser.escapedStringLiterals' that can be used to fallback to the Spark 1.6 behavior regarding string literal parsing. For example, if the config is enabled, the regexp that can match "\abc" is "^\abc$".

It's recommended to use a raw string literal (with the r prefix) to avoid escaping special characters in the pattern string if exists. An expression that evaluates to a string.
idx - an integer expression that representing the group index. The regex maybe contains multiple groups. idx indicates which regex group to extract. The group index should be non-negative. The minimum value of idx is 0, which means matching the entire regular expression. If idx is not specified, the default group index value is 1. The idx parameter is the Java regex Matcher group() method index. An expression that evaluates to an integer.

Examples:

> SELECT regexp_extract('100-200', '(\\d+)-(\\d+)', 1);
 100
> SELECT regexp_extract('100-200', r'(\d+)-(\d+)', 1);
 100

Since: 1.5.0

regexp_extract_all ¶

regexp_extract_all(str, regexp[, idx]) - Extract all strings in the str that match the regexp expression and corresponding to the regex group index.

Arguments:

str - a string expression. An expression that evaluates to a string.
regexp - a string representing a regular expression. The regex string should be a Java regular expression.

Since Spark 2.0, string literals (including regex patterns) are unescaped in our SQL parser, see the unescaping rules at String Literal. For example, to match "\abc", a regular expression for regexp can be "^\abc$".

There is a SQL config 'spark.sql.parser.escapedStringLiterals' that can be used to fallback to the Spark 1.6 behavior regarding string literal parsing. For example, if the config is enabled, the regexp that can match "\abc" is "^\abc$".

It's recommended to use a raw string literal (with the r prefix) to avoid escaping special characters in the pattern string if exists. An expression that evaluates to a string.
idx - an integer expression that representing the group index. The regex may contains multiple groups. idx indicates which regex group to extract. The group index should be non-negative. The minimum value of idx is 0, which means matching the entire regular expression. If idx is not specified, the default group index value is 1. The idx parameter is the Java regex Matcher group() method index. An expression that evaluates to an integer.

Examples:

> SELECT regexp_extract_all('100-200, 300-400', '(\\d+)-(\\d+)', 1);
 ["100","300"]
> SELECT regexp_extract_all('100-200, 300-400', r'(\d+)-(\d+)', 1);
 ["100","300"]

Since: 3.1.0

regexp_instr ¶

regexp_instr(str, regexp) - Searches a string for a regular expression and returns an integer that indicates the beginning position of the matched substring. Positions are 1-based, not 0-based. If no match is found, returns 0.

Arguments:

str - a string expression. An expression that evaluates to a string.
regexp - a string representing a regular expression. The regex string should be a Java regular expression.

Since Spark 2.0, string literals (including regex patterns) are unescaped in our SQL parser, see the unescaping rules at String Literal. For example, to match "\abc", a regular expression for regexp can be "^\abc$".

There is a SQL config 'spark.sql.parser.escapedStringLiterals' that can be used to fallback to the Spark 1.6 behavior regarding string literal parsing. For example, if the config is enabled, the regexp that can match "\abc" is "^\abc$".

It's recommended to use a raw string literal (with the r prefix) to avoid escaping special characters in the pattern string if exists. An expression that evaluates to a string.
idx - the group index of the regular expression to search for. The default value is 0 if not specified. An expression that evaluates to an integer.

Examples:

> SELECT regexp_instr(r"\abc", r"^\\abc$");
 1
> SELECT regexp_instr('user@spark.apache.org', '@[^.]*');
 5

Since: 3.4.0

regexp_replace ¶

regexp_replace(str, regexp, rep[, position]) - Replaces all substrings of str that match regexp with rep.

Arguments:

str - a string expression to search for a regular expression pattern match. An expression that evaluates to a string.
regexp - a string representing a regular expression. The regex string should be a Java regular expression.

Since Spark 2.0, string literals (including regex patterns) are unescaped in our SQL parser, see the unescaping rules at String Literal. For example, to match "\abc", a regular expression for regexp can be "^\abc$".

There is a SQL config 'spark.sql.parser.escapedStringLiterals' that can be used to fallback to the Spark 1.6 behavior regarding string literal parsing. For example, if the config is enabled, the regexp that can match "\abc" is "^\abc$".

It's recommended to use a raw string literal (with the r prefix) to avoid escaping special characters in the pattern string if exists. An expression that evaluates to a string.
rep - a string expression to replace matched substrings. An expression that evaluates to a string.
position - a positive integer literal that indicates the position within str to begin searching. The default is 1. If position is greater than the number of characters in str, the result is str. An expression that evaluates to an integer. Must be a constant.

Examples:

> SELECT regexp_replace('100-200', '(\\d+)', 'num');
 num-num
> SELECT regexp_replace('100-200', r'(\d+)', 'num');
 num-num

Since: 1.5.0

regexp_substr ¶

regexp_substr(str, regexp) - Returns the substring that matches the regular expression regexp within the string str. If the regular expression is not found, the result is null.

Arguments:

str - a string expression. An expression that evaluates to a string.
regexp - a string representing a regular expression. The regex string should be a Java regular expression. An expression that evaluates to a string.

Examples:

> SELECT regexp_substr('Steven Jones and Stephen Smith are the best players', 'Ste(v|ph)en');
 Steven
> SELECT regexp_substr('Steven Jones and Stephen Smith are the best players', 'Jeck');
 NULL

Since: 3.4.0

repeat ¶

repeat(str, n) - Returns the string which repeats the given string value n times.

Arguments:

str - The string to repeat. An expression that evaluates to a string.
n - The number of times to repeat the string. An expression that evaluates to an integer.

Examples:

> SELECT repeat('123', 2);
 123123

Since: 1.5.0

replace ¶

replace(str, search[, replace]) - Replaces all occurrences of search with replace.

Arguments:

str - a string expression An expression that evaluates to a string.
search - a string expression. If search is not found in str, str is returned unchanged. An expression that evaluates to a string.
replace - a string expression. If replace is not specified or is an empty string, nothing replaces the string that is removed from str. An expression that evaluates to a string.

Examples:

> SELECT replace('ABCabc', 'abc', 'DEF');
 ABCDEF

Since: 2.3.0

right ¶

right(str, len) - Returns the rightmost len(len can be string type) characters from the string str,if len is less or equal than 0 the result is an empty string.

Arguments:

str - The string to take the rightmost characters from. An expression that evaluates to a string.
len - The number of characters to take from the right. An expression that evaluates to an integer.

Examples:

> SELECT right('Spark SQL', 3);
 SQL

Since: 2.3.0

rpad ¶

rpad(str, len[, pad]) - Returns str, right-padded with pad to a length of len. If str is longer than len, the return value is shortened to len characters. If pad is not specified, str will be padded to the right with space characters if it is a character string, and with zeros if it is a binary string.

Arguments:

str - The string to right-pad. An expression that evaluates to a string or binary.
len - The length to pad the string to. An expression that evaluates to an integer.
pad - The string used to pad on the right. An expression that evaluates to a string or binary.

Examples:

> SELECT rpad('hi', 5, '??');
 hi???
> SELECT rpad('hi', 1, '??');
 h
> SELECT rpad('hi', 5);
 hi
> SELECT hex(rpad(unhex('aabb'), 5));
 AABB000000
> SELECT hex(rpad(unhex('aabb'), 5, unhex('1122')));
 AABB112211

Since: 1.5.0

rtrim ¶

rtrim(str) - Removes the trailing space characters from str.

Arguments:

str - a string expression An expression that evaluates to a string.
trimStr - the trim string characters to trim, the default value is a single space An expression that evaluates to a string.

Examples:

> SELECT rtrim('    SparkSQL   ');
 SparkSQL

Since: 1.5.0

sentences ¶

sentences(str[, lang[, country]]) - Splits str into an array of array of words.

Arguments:

str - A STRING expression to be parsed. An expression that evaluates to a string.
lang - An optional STRING expression with a language code from ISO 639 Alpha-2 (e.g. 'DE'), Alpha-3, or a language subtag of up to 8 characters. An expression that evaluates to a string.
country - An optional STRING expression with a country code from ISO 3166 alpha-2 country code or a UN M.49 numeric-3 area code. An expression that evaluates to a string.

Examples:

> SELECT sentences('Hi there! Good morning.');
 [["Hi","there"],["Good","morning"]]
> SELECT sentences('Hi there! Good morning.', 'en');
 [["Hi","there"],["Good","morning"]]
> SELECT sentences('Hi there! Good morning.', 'en', 'US');
 [["Hi","there"],["Good","morning"]]

Since: 2.0.0

soundex ¶

soundex(str) - Returns Soundex code of the string.

Arguments:

str - The string to compute the Soundex code of. An expression that evaluates to a string.

Examples:

> SELECT soundex('Miller');
 M460

Since: 1.5.0

space ¶

space(n) - Returns a string consisting of n spaces.

Arguments:

n - The number of spaces to produce. An expression that evaluates to an integer.

Examples:

> SELECT concat(space(2), '1');
   1

Since: 1.5.0

split ¶

split(str, regex, limit) - Splits str around occurrences that match regex and returns an array with a length of at most limit

Arguments:

str - a string expression to split. An expression that evaluates to a string.
regex - a string representing a regular expression. The regex string should be a Java regular expression. An expression that evaluates to a string.
limit - an integer expression which controls the number of times the regex is applied.
- limit > 0: The resulting array's length will not be more than limit, and the resulting array's last entry will contain all input beyond the last matched regex.
- limit <= 0: regex will be applied as many times as possible, and the resulting array can be of any size. An expression that evaluates to an integer.

Examples:

> SELECT split('oneAtwoBthreeC', '[ABC]');
 ["one","two","three",""]
> SELECT split('oneAtwoBthreeC', '[ABC]', -1);
 ["one","two","three",""]
> SELECT split('oneAtwoBthreeC', '[ABC]', 2);
 ["one","twoBthreeC"]

Since: 1.5.0

split_part ¶

split_part(str, delimiter, partNum) - Splits str by delimiter and return requested part of the split (1-based). If any input is null, returns null. if partNum is out of range of split parts, returns empty string. If partNum is 0, throws an error. If partNum is negative, the parts are counted backward from the end of the string. If the delimiter is an empty string, the str is not split.

Arguments:

str - The string to split. An expression that evaluates to a string.
delimiter - The string used to split the input into parts. An expression that evaluates to a string.
partNum - The 1-based index of the split part to return. An expression that evaluates to an integer.

Examples:

> SELECT split_part('11.12.13', '.', 3);
 13

Since: 3.3.0

startswith ¶

startswith(left, right) - Returns a boolean. The value is True if left starts with right. Returns NULL if either input expression is NULL. Otherwise, returns False. Both left or right must be of STRING or BINARY type.

Arguments:

left - The string to test. An expression that evaluates to a string or binary.
right - The prefix to check for at the start of the string. An expression that evaluates to a string or binary.

Examples:

> SELECT startswith('Spark SQL', 'Spark');
 true
> SELECT startswith('Spark SQL', 'SQL');
 false
> SELECT startswith('Spark SQL', null);
 NULL
> SELECT startswith(x'537061726b2053514c', x'537061726b');
 true
> SELECT startswith(x'537061726b2053514c', x'53514c');
 false

Since: 3.3.0

substr ¶

substr(str, pos[, len]) - Returns the substring of str that starts at pos and is of length len, or the slice of byte array that starts at pos and is of length len.

substr(str FROM pos[ FOR len]]) - Returns the substring of str that starts at pos and is of length len, or the slice of byte array that starts at pos and is of length len.

Arguments:

str - The string or byte array to take the substring from. An expression that evaluates to a string or binary.
pos - The 1-based starting position of the substring. An expression that evaluates to an integer.
len - The length of the substring to return. An expression that evaluates to an integer.

Examples:

> SELECT substr('Spark SQL', 5);
 k SQL
> SELECT substr('Spark SQL', -3);
 SQL
> SELECT substr('Spark SQL', 5, 1);
 k
> SELECT substr('Spark SQL' FROM 5);
 k SQL
> SELECT substr('Spark SQL' FROM -3);
 SQL
> SELECT substr('Spark SQL' FROM 5 FOR 1);
 k
> SELECT substr(encode('Spark SQL', 'utf-8'), 5);
 k SQL

Since: 1.5.0

substring ¶

substring(str, pos[, len]) - Returns the substring of str that starts at pos and is of length len, or the slice of byte array that starts at pos and is of length len.

substring(str FROM pos[ FOR len]]) - Returns the substring of str that starts at pos and is of length len, or the slice of byte array that starts at pos and is of length len.

Arguments:

str - The string or byte array to take the substring from. An expression that evaluates to a string or binary.
pos - The 1-based starting position of the substring. An expression that evaluates to an integer.
len - The length of the substring to return. An expression that evaluates to an integer.

Examples:

> SELECT substring('Spark SQL', 5);
 k SQL
> SELECT substring('Spark SQL', -3);
 SQL
> SELECT substring('Spark SQL', 5, 1);
 k
> SELECT substring('Spark SQL' FROM 5);
 k SQL
> SELECT substring('Spark SQL' FROM -3);
 SQL
> SELECT substring('Spark SQL' FROM 5 FOR 1);
 k
> SELECT substring(encode('Spark SQL', 'utf-8'), 5);
 k SQL

Since: 1.5.0

substring_index ¶

substring_index(str, delim, count) - Returns the substring from str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. The function substring_index performs a case-sensitive match when searching for delim.

Arguments:

str - The string to take the substring from. An expression that evaluates to a string.
delim - The delimiter to count occurrences of. An expression that evaluates to a string.
count - The number of delimiter occurrences that bound the returned substring. An expression that evaluates to an integer.

Examples:

> SELECT substring_index('www.apache.org', '.', 2);
 www.apache

Since: 1.5.0

to_binary ¶

to_binary(str[, fmt]) - Converts the input str to a binary value based on the supplied fmt. fmt can be a case-insensitive string literal of "hex", "utf-8", "utf8", or "base64". By default, the binary format for conversion is "hex" if fmt is omitted. The function returns NULL if at least one of the input parameters is NULL.

Arguments:

str - The string to convert to a binary value. An expression that evaluates to a string.
fmt - The format describing how to interpret the string (e.g. "hex", "utf-8", "base64"). An expression that evaluates to a string. Must be a constant.

Examples:

> SELECT to_binary('abc', 'utf-8');
 abc

Since: 3.3.0

to_char ¶

to_char(expr, format) - Convert expr to a string based on the format. Throws an exception if the conversion fails. The format can consist of the following characters, case insensitive: '0' or '9': Specifies an expected digit between 0 and 9. A sequence of 0 or 9 in the format string matches a sequence of digits in the input value, generating a result string of the same length as the corresponding sequence in the format string. The result string is left-padded with zeros if the 0/9 sequence comprises more digits than the matching part of the decimal value, starts with 0, and is before the decimal point. Otherwise, it is padded with spaces. '.' or 'D': Specifies the position of the decimal point (optional, only allowed once). ',' or 'G': Specifies the position of the grouping (thousands) separator (,). There must be a 0 or 9 to the left and right of each grouping separator. '$': Specifies the location of the $ currency sign. This character may only be specified once. 'S' or 'MI': Specifies the position of a '-' or '+' sign (optional, only allowed once at the beginning or end of the format string). Note that 'S' prints '+' for positive values but 'MI' prints a space. 'PR': Only allowed at the end of the format string; specifies that the result string will be wrapped by angle brackets if the input value is negative. ('<1>'). If expr is a datetime, format shall be a valid datetime pattern, see Datetime Patterns. If expr is a binary, it is converted to a string in one of the formats: 'base64': a base 64 string. 'hex': a string in the hexadecimal format. 'utf-8': the input binary is decoded to UTF-8 string.

Arguments:

expr - The expression to convert to a string. An expression that evaluates to a numeric, date, timestamp, time, or binary.
format - The format that specifies how to render the value as a string. An expression that evaluates to a string. Must be a constant when expr is numeric or binary.

Examples:

> SELECT to_char(454, '999');
 454
> SELECT to_char(454.00, '000D00');
 454.00
> SELECT to_char(12454, '99G999');
 12,454
> SELECT to_char(78.12, '$99.99');
 $78.12
> SELECT to_char(-12454.8, '99G999D9S');
 12,454.8-
> SELECT to_char(date'2016-04-08', 'y');
 2016
> SELECT to_char(x'537061726b2053514c', 'base64');
 U3BhcmsgU1FM
> SELECT to_char(x'537061726b2053514c', 'hex');
 537061726B2053514C
> SELECT to_char(encode('abc', 'utf-8'), 'utf-8');
 abc

Since: 3.4.0

to_number ¶

to_number(expr, fmt) - Convert string 'expr' to a number based on the string format 'fmt'. Throws an exception if the conversion fails. The format can consist of the following characters, case insensitive: '0' or '9': Specifies an expected digit between 0 and 9. A sequence of 0 or 9 in the format string matches a sequence of digits in the input string. If the 0/9 sequence starts with 0 and is before the decimal point, it can only match a digit sequence of the same size. Otherwise, if the sequence starts with 9 or is after the decimal point, it can match a digit sequence that has the same or smaller size. '.' or 'D': Specifies the position of the decimal point (optional, only allowed once). ',' or 'G': Specifies the position of the grouping (thousands) separator (,). There must be a 0 or 9 to the left and right of each grouping separator. 'expr' must match the grouping separator relevant for the size of the number. '$': Specifies the location of the $ currency sign. This character may only be specified once. 'S' or 'MI': Specifies the position of a '-' or '+' sign (optional, only allowed once at the beginning or end of the format string). Note that 'S' allows '-' but 'MI' does not. 'PR': Only allowed at the end of the format string; specifies that 'expr' indicates a negative number with wrapping angled brackets. ('<1>').

Arguments:

expr - The string to convert to a number. An expression that evaluates to a string.
fmt - The format that specifies how to parse the string as a number. An expression that evaluates to a string. Must be a constant.

Examples:

> SELECT to_number('454', '999');
 454
> SELECT to_number('454.00', '000.00');
 454.00
> SELECT to_number('12,454', '99,999');
 12454
> SELECT to_number('$78.12', '$99.99');
 78.12
> SELECT to_number('12,454.8-', '99,999.9S');
 -12454.8

Since: 3.3.0

to_varchar ¶

to_varchar(expr, format) - Convert expr to a string based on the format. Throws an exception if the conversion fails. The format can consist of the following characters, case insensitive: '0' or '9': Specifies an expected digit between 0 and 9. A sequence of 0 or 9 in the format string matches a sequence of digits in the input value, generating a result string of the same length as the corresponding sequence in the format string. The result string is left-padded with zeros if the 0/9 sequence comprises more digits than the matching part of the decimal value, starts with 0, and is before the decimal point. Otherwise, it is padded with spaces. '.' or 'D': Specifies the position of the decimal point (optional, only allowed once). ',' or 'G': Specifies the position of the grouping (thousands) separator (,). There must be a 0 or 9 to the left and right of each grouping separator. '$': Specifies the location of the $ currency sign. This character may only be specified once. 'S' or 'MI': Specifies the position of a '-' or '+' sign (optional, only allowed once at the beginning or end of the format string). Note that 'S' prints '+' for positive values but 'MI' prints a space. 'PR': Only allowed at the end of the format string; specifies that the result string will be wrapped by angle brackets if the input value is negative. ('<1>'). If expr is a datetime, format shall be a valid datetime pattern, see Datetime Patterns. If expr is a binary, it is converted to a string in one of the formats: 'base64': a base 64 string. 'hex': a string in the hexadecimal format. 'utf-8': the input binary is decoded to UTF-8 string.

Arguments:

expr - The expression to convert to a string. An expression that evaluates to a numeric, date, timestamp, time, or binary.
format - The format that specifies how to render the value as a string. An expression that evaluates to a string. Must be a constant when expr is numeric or binary.

Examples:

> SELECT to_varchar(454, '999');
 454
> SELECT to_varchar(454.00, '000D00');
 454.00
> SELECT to_varchar(12454, '99G999');
 12,454
> SELECT to_varchar(78.12, '$99.99');
 $78.12
> SELECT to_varchar(-12454.8, '99G999D9S');
 12,454.8-
> SELECT to_varchar(date'2016-04-08', 'y');
 2016
> SELECT to_varchar(x'537061726b2053514c', 'base64');
 U3BhcmsgU1FM
> SELECT to_varchar(x'537061726b2053514c', 'hex');
 537061726B2053514C
> SELECT to_varchar(encode('abc', 'utf-8'), 'utf-8');
 abc

Since: 3.5.0

translate ¶

translate(input, from, to) - Translates the input string by replacing the characters present in the from string with the corresponding characters in the to string.

Arguments:

input - The string to translate. An expression that evaluates to a string.
from - The characters to be replaced. An expression that evaluates to a string.
to - The corresponding replacement characters. An expression that evaluates to a string.

Examples:

> SELECT translate('AaBbCc', 'abc', '123');
 A1B2C3

Since: 1.5.0

trim ¶

trim(str) - Removes the leading and trailing space characters from str.

trim(BOTH FROM str) - Removes the leading and trailing space characters from str.

trim(LEADING FROM str) - Removes the leading space characters from str.

trim(TRAILING FROM str) - Removes the trailing space characters from str.

trim(trimStr FROM str) - Remove the leading and trailing trimStr characters from str.

trim(BOTH trimStr FROM str) - Remove the leading and trailing trimStr characters from str.

trim(LEADING trimStr FROM str) - Remove the leading trimStr characters from str.

trim(TRAILING trimStr FROM str) - Remove the trailing trimStr characters from str.

Arguments:

str - a string expression An expression that evaluates to a string.
trimStr - the trim string characters to trim, the default value is a single space An expression that evaluates to a string.
BOTH, FROM - these are keywords to specify trimming string characters from both ends of the string
LEADING, FROM - these are keywords to specify trimming string characters from the left end of the string
TRAILING, FROM - these are keywords to specify trimming string characters from the right end of the string

Examples:

> SELECT trim('    SparkSQL   ');
 SparkSQL
> SELECT trim(BOTH FROM '    SparkSQL   ');
 SparkSQL
> SELECT trim(LEADING FROM '    SparkSQL   ');
 SparkSQL
> SELECT trim(TRAILING FROM '    SparkSQL   ');
     SparkSQL
> SELECT trim('SL' FROM 'SSparkSQLS');
 parkSQ
> SELECT trim(BOTH 'SL' FROM 'SSparkSQLS');
 parkSQ
> SELECT trim(LEADING 'SL' FROM 'SSparkSQLS');
 parkSQLS
> SELECT trim(TRAILING 'SL' FROM 'SSparkSQLS');
 SSparkSQ

Since: 1.5.0

try_to_binary ¶

try_to_binary(str[, fmt]) - This is a special version of to_binary that performs the same operation, but returns a NULL value instead of raising an error if the conversion cannot be performed.

Arguments:

str - The string to convert to a binary value. An expression that evaluates to a string.
fmt - The format of the input string. An expression that evaluates to a string. Must be a constant.

Examples:

> SELECT try_to_binary('abc', 'utf-8');
 abc
> select try_to_binary('a!', 'base64');
 NULL
> select try_to_binary('abc', 'invalidFormat');
 NULL

Since: 3.3.0

try_to_number ¶

try_to_number(expr, fmt) - Convert string 'expr' to a number based on the string format fmt. Returns NULL if the string 'expr' does not match the expected format. The format follows the same semantics as the to_number function.

Arguments:

expr - The string to convert to a number. An expression that evaluates to a string.
fmt - The format that specifies how to parse the string as a number. An expression that evaluates to a string. Must be a constant.

Examples:

> SELECT try_to_number('454', '999');
 454
> SELECT try_to_number('454.00', '000.00');
 454.00
> SELECT try_to_number('12,454', '99,999');
 12454
> SELECT try_to_number('$78.12', '$99.99');
 78.12
> SELECT try_to_number('12,454.8-', '99,999.9S');
 -12454.8

Since: 3.3.0

try_validate_utf8 ¶

try_validate_utf8(str) - Returns the original string if str is a valid UTF-8 string, otherwise returns NULL.

Arguments:

str - a string expression An expression that evaluates to a string.

Examples:

> SELECT try_validate_utf8('Spark');
 Spark
> SELECT try_validate_utf8(x'61');
 a
> SELECT try_validate_utf8(x'80');
 NULL
> SELECT try_validate_utf8(x'61C262');
 NULL

Since: 4.0.0

ucase ¶

ucase(str) - Returns str with all characters changed to uppercase.

Arguments:

str - The string to convert to uppercase. An expression that evaluates to a string.

Examples:

> SELECT ucase('SparkSql');
 SPARKSQL

Since: 1.0.1

unbase64 ¶

unbase64(str) - Converts the argument from a base 64 string str to a binary.

Arguments:

str - The base 64 string to decode to binary. An expression that evaluates to a string.

Examples:

> SELECT unbase64('U3BhcmsgU1FM');
 Spark SQL

Since: 1.5.0

upper ¶

upper(str) - Returns str with all characters changed to uppercase.

Arguments:

str - The string to convert to uppercase. An expression that evaluates to a string.

Examples:

> SELECT upper('SparkSql');
 SPARKSQL

Since: 1.0.1

validate_utf8 ¶

validate_utf8(str) - Returns the original string if str is a valid UTF-8 string, otherwise throws an exception.

Arguments:

str - a string expression An expression that evaluates to a string.

Examples:

> SELECT validate_utf8('Spark');
 Spark
> SELECT validate_utf8(x'61');
 a

Since: 4.0.0

||¶

expr1 || expr2 - Returns the concatenation of expr1 and expr2.

Examples:

> SELECT 'Spark' || 'SQL';
 SparkSQL
> SELECT array(1, 2, 3) || array(4, 5) || array(6);
 [1,2,3,4,5,6]

Note:

|| for arrays is available since 2.4.0.

Since: 2.3.0