String functions

This page lists every Spark SQL string and URL function: 79 work on Databasin Sail as in Apache Spark. Each function shows its status on Sail, its usage, and examples whose results on Sail match Spark's.

Every supported function also works from PySpark through pyspark.sql.functions when PySpark has a wrapper for it. The Sail SQL reference lists every function page.

String

|| · ascii · base64 · bit_length · btrim · char · char_length · character_length · chr · collate · collation · concat_ws · contains · decode · elt · encode · endswith · find_in_set · format_number · format_string · initcap · instr · is_valid_utf8 · lcase · left · len · length · levenshtein · locate · lower · lpad · ltrim · luhn_check · make_valid_utf8 · mask · octet_length · overlay · position · printf · quote · randstr · regexp_count · regexp_extract · regexp_extract_all · regexp_instr · regexp_replace · regexp_substr · repeat · replace · right · rpad · rtrim · sentences · soundex · space · split · split_part · startswith · substr · substring · substring_index · to_binary · to_char · to_number · to_varchar · translate · trim · try_to_binary · try_to_number · try_validate_utf8 · ucase · unbase64 · upper · validate_utf8

||

Supported.

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

ascii

Supported.

ascii(str): Returns the numeric value of the first character of str.

Since Spark 1.5.0.

SELECT ascii('222');
-- 50

SELECT ascii(2);
-- 50

base64

Supported.

base64(bin): Converts the argument from a binary bin to a base 64 string.

Since Spark 1.5.0.

SELECT base64('Spark SQL');
-- U3BhcmsgU1FM

SELECT base64(x'537061726b2053514c');
-- U3BhcmsgU1FM

bit_length

Supported.

bit_length(expr): Returns the bit length of string data or number of bits of binary data.

Since Spark 2.3.0.

SELECT bit_length('Spark SQL');
-- 72

SELECT bit_length(x'537061726b2053514c');
-- 72

btrim

Supported.

btrim(str): Removes the leading and trailing space characters from str.

btrim(str, trimStr): Remove the leading and trailing trimStr characters from str.

• str: a string expression
• trimStr: the trim string characters to trim, the default value is a single space

Since Spark 3.2.0.

SELECT btrim('    SparkSQL   ');
-- SparkSQL

SELECT btrim(encode('    SparkSQL   ', 'utf-8'));
-- SparkSQL

SELECT btrim('SSparkSQLS', 'SL');
-- parkSQ

SELECT btrim(encode('SSparkSQLS', 'utf-8'), encode('SL', 'utf-8'));
-- parkSQ

char

Supported.

char(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)

Since Spark 2.3.0.

SELECT char(65);
-- A

char_length

Supported.

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.

Since Spark 2.3.0.

SELECT char_length('Spark SQL ');
-- 10

SELECT char_length(x'537061726b2053514c');
-- 9

SELECT CHAR_LENGTH('Spark SQL ');
-- 10

SELECT CHARACTER_LENGTH('Spark SQL ');
-- 10

character_length

Supported.

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.

Since Spark 2.3.0.

SELECT character_length('Spark SQL ');
-- 10

SELECT character_length(x'537061726b2053514c');
-- 9

SELECT CHAR_LENGTH('Spark SQL ');
-- 10

SELECT CHARACTER_LENGTH('Spark SQL ');
-- 10

chr

Supported.

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)

Since Spark 2.3.0.

SELECT chr(65);
-- A

collate

Supported. UTF8_BINARY and UTF8_LCASE supported in comparisons, IN, LIKE, joins, ORDER BY and windows; UTF8_LCASE grouping / set operations and ICU collations are refused.

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

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

Since Spark 4.0.0.

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

collation

Supported. UTF8_BINARY and UTF8_LCASE supported; ICU collations are refused.

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

• expr: String expression to perform collation on.

Since Spark 4.0.0.

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

concat_ws

Supported.

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

Since Spark 1.5.0.

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

contains

Supported.

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.

Since Spark 3.3.0.

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

decode

Supported.

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.

• bin: a binary expression to decode
• 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.

Note from Spark: _FUNC_(expr, search, result [, search, result ] ... [, default]) is supported since 3.2.0

Since Spark 1.5.0.

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

elt

Supported.

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.

Since Spark 2.0.0.

SELECT elt(1, 'scala', 'java');
-- scala

SELECT elt(2, 'a', 1);
-- 1

encode

Supported.

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

• str: a string expression
• 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.

Since Spark 1.5.0.

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

endswith

Supported.

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.

Since Spark 3.3.0.

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

find_in_set

Supported.

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.

Since Spark 1.5.0.

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

format_number

Supported.

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.

Since Spark 1.5.0.

SELECT format_number(12332.123456, 4);
-- 12,332.1235

SELECT format_number(12332.123456, '##################.###');
-- 12332.123

format_string

Supported.

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

Since Spark 1.5.0.

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

initcap

Supported.

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.

Since Spark 1.5.0.

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

instr

Supported.

instr(str, substr): Returns the (1-based) index of the first occurrence of substr in str.

Since Spark 1.5.0.

SELECT instr('SparkSQL', 'SQL');
-- 6

is_valid_utf8

Supported.

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

• str: a string expression

Since Spark 4.0.0.

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

lcase

Supported.

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

Since Spark 1.0.1.

SELECT lcase('SparkSql');
-- sparksql

left

Supported.

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.

Since Spark 2.3.0.

SELECT left('Spark SQL', 3);
-- Spa

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

len

Supported.

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.

Since Spark 3.4.0.

SELECT len('Spark SQL ');
-- 10

SELECT len(x'537061726b2053514c');
-- 9

SELECT CHAR_LENGTH('Spark SQL ');
-- 10

SELECT CHARACTER_LENGTH('Spark SQL ');
-- 10

length

Supported.

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.

Since Spark 1.5.0.

SELECT length('Spark SQL ');
-- 10

SELECT length(x'537061726b2053514c');
-- 9

SELECT CHAR_LENGTH('Spark SQL ');
-- 10

SELECT CHARACTER_LENGTH('Spark SQL ');
-- 10

levenshtein

Supported.

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

Since Spark 1.5.0.

SELECT levenshtein('kitten', 'sitting');
-- 3

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

locate

Supported. Results match Spark; the auto-generated result column name can differ in some examples.

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.

Since Spark 1.5.0.

SELECT locate('bar', 'foobarbar');
-- 4

SELECT locate('bar', 'foobarbar', 5);
-- 7

SELECT POSITION('bar' IN 'foobarbar');
-- 4

lower

Supported.

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

Since Spark 1.0.1.

SELECT lower('SparkSql');
-- sparksql

lpad

Supported. Results match Spark; the auto-generated result column name can differ in some examples.

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.

Since Spark 1.5.0.

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

ltrim

Supported.

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

• str: a string expression
• trimStr: the trim string characters to trim, the default value is a single space

Since Spark 1.5.0.

SELECT ltrim('    SparkSQL   ');
-- SparkSQL

luhn_check

Supported.

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.

Since Spark 3.5.0.

SELECT luhn_check('8112189876');
-- true

SELECT luhn_check('79927398713');
-- true

SELECT luhn_check('79927398714');
-- false

make_valid_utf8

Supported.

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.

• str: a string expression

Since Spark 4.0.0.

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

mask

Supported.

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.

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

Since Spark 3.4.0.

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-@$#

octet_length

Supported.

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

Since Spark 2.3.0.

SELECT octet_length('Spark SQL');
-- 9

SELECT octet_length(x'537061726b2053514c');
-- 9

overlay

Supported.

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

Since Spark 3.0.0.

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

position

Supported. Results match Spark; the auto-generated result column name can differ in some examples.

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.

Since Spark 2.3.0.

SELECT position('bar', 'foobarbar');
-- 4

SELECT position('bar', 'foobarbar', 5);
-- 7

SELECT POSITION('bar' IN 'foobarbar');
-- 4

printf

Supported.

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

Since Spark 1.5.0.

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

quote

Supported. New in Spark 4.1.0.

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

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

randstr

Supported.

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).

Since Spark 4.0.0.

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

regexp_count

Supported.

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

• str: a string expression.
• regexp: a string representing a regular expression. The regex string should be a Java regular expression.

Since Spark 3.4.0.

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

regexp_extract

Supported.

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

• str: a string expression.
• 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.
• 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.

Since Spark 1.5.0.

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

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

regexp_extract_all

Supported.

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

• str: a string expression.
• 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.
• 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.

Since Spark 3.1.0.

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"]

regexp_instr

Supported.

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.

• str: a string expression.
• 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.

Since Spark 3.4.0.

SELECT regexp_instr(r"\abc", r"^\\abc$");
-- 1

SELECT regexp_instr('user@spark.apache.org', '@[^.]*');
-- 5

regexp_replace

Supported.

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

• str: a string expression to search for a regular expression pattern match.
• 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.
• rep: a string expression to replace matched substrings.
• 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.

Since Spark 1.5.0.

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

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

regexp_substr

Supported.

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.

• str: a string expression.
• regexp: a string representing a regular expression. The regex string should be a Java regular expression.

Since Spark 3.4.0.

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

repeat

Supported.

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

Since Spark 1.5.0.

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

replace

Supported.

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

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

Since Spark 2.3.0.

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

right

Supported.

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.

Since Spark 2.3.0.

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

rpad

Supported. Results match Spark; the auto-generated result column name can differ in some examples.

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.

Since Spark 1.5.0.

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

rtrim

Supported.

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

• str: a string expression
• trimStr: the trim string characters to trim, the default value is a single space

Since Spark 1.5.0.

SELECT rtrim('    SparkSQL   ');
-- SparkSQL

sentences

Supported.

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

• str: A STRING expression to be parsed.
• 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.
• 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.

Since Spark 2.0.0.

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"]]

soundex

Supported.

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

Since Spark 1.5.0.

SELECT soundex('Miller');
-- M460

space

Supported.

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

Since Spark 1.5.0.

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

split

Supported.

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

• str: a string expression to split.
• regex: a string representing a regular expression. The regex string should be a Java regular expression.
• 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.

Since Spark 1.5.0.

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

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

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

split_part

Supported.

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.

Since Spark 3.3.0.

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

startswith

Supported.

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.

Since Spark 3.3.0.

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

substr

Supported.

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.

Since Spark 1.5.0.

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

substring

Supported.

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.

Since Spark 1.5.0.

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

substring_index

Supported.

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.

Since Spark 1.5.0.

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

to_binary

Supported.

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.

Since Spark 3.3.0.

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

to_char

Supported. Results match Spark; the auto-generated result column name can differ in some examples.

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.

Since Spark 3.4.0.

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

to_number

Supported.

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>').

Since Spark 3.3.0.

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

to_varchar

Supported. Results match Spark; the auto-generated result column name can differ in some examples.

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.

Since Spark 3.5.0.

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

translate

Supported.

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.

Since Spark 1.5.0.

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

trim

Supported.

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.

• str: a string expression
• trimStr: the trim string characters to trim, the default value is a single space
• 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

Since Spark 1.5.0.

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

try_to_binary

Supported.

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.

Since Spark 3.3.0.

SELECT try_to_binary('abc', 'utf-8');
-- abc

select try_to_binary('a!', 'base64');
-- NULL

select try_to_binary('abc', 'invalidFormat');
-- NULL

try_to_number

Supported.

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.

Since Spark 3.3.0.

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

try_validate_utf8

Supported.

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

• str: a string expression

Since Spark 4.0.0.

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

ucase

Supported.

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

Since Spark 1.0.1.

SELECT ucase('SparkSql');
-- SPARKSQL

unbase64

Supported.

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

Since Spark 1.5.0.

SELECT unbase64('U3BhcmsgU1FM');
-- Spark SQL

upper

Supported.

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

Since Spark 1.0.1.

SELECT upper('SparkSql');
-- SPARKSQL

validate_utf8

Supported.

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

• str: a string expression

Since Spark 4.0.0.

SELECT validate_utf8('Spark');
-- Spark

SELECT validate_utf8(x'61');
-- a

URL

parse_url · try_parse_url · try_url_decode · url_decode · url_encode

parse_url

Supported.

parse_url(url, partToExtract[, key]): Extracts a part from a URL.

Since Spark 2.0.0.

SELECT parse_url('http://spark.apache.org/path?query=1', 'HOST');
-- spark.apache.org

SELECT parse_url('http://spark.apache.org/path?query=1', 'QUERY');
-- query=1

SELECT parse_url('http://spark.apache.org/path?query=1', 'QUERY', 'query');
-- 1

try_parse_url

Supported.

try_parse_url(url, partToExtract[, key]): This is a special version of parse_url that performs the same operation, but returns a NULL value instead of raising an error if the parsing cannot be performed.

Since Spark 4.0.0.

SELECT try_parse_url('http://spark.apache.org/path?query=1', 'HOST');
-- spark.apache.org

SELECT try_parse_url('http://spark.apache.org/path?query=1', 'QUERY');
-- query=1

SELECT try_parse_url('inva lid://spark.apache.org/path?query=1', 'QUERY');
-- NULL

SELECT try_parse_url('http://spark.apache.org/path?query=1', 'QUERY', 'query');
-- 1

try_url_decode

Supported.

try_url_decode(str): This is a special version of url_decode that performs the same operation, but returns a NULL value instead of raising an error if the decoding cannot be performed.

• str: a string expression to decode

Since Spark 4.0.0.

SELECT try_url_decode('https%3A%2F%2Fspark.apache.org');
-- https://spark.apache.org

url_decode

Supported.

url_decode(str): Decodes a str in 'application/x-www-form-urlencoded' format using a specific encoding scheme.

• str: a string expression to decode

Since Spark 3.4.0.

SELECT url_decode('https%3A%2F%2Fspark.apache.org');
-- https://spark.apache.org

url_encode

Supported.

url_encode(str): Translates a string into 'application/x-www-form-urlencoded' format using a specific encoding scheme.

• ``: str - a string expression to be translated

Since Spark 3.4.0.

SELECT url_encode('https://spark.apache.org');
-- https%3A%2F%2Fspark.apache.org

Function names, usage text, argument descriptions and examples are from the Apache Spark SQL built-in functions documentation, Copyright The Apache Software Foundation, licensed under the Apache License, Version 2.0. Apache Spark is a trademark of the Apache Software Foundation. Sail status and notes are from Databasin.

Where to go next