# String Functions in Confluent Cloud for Apache Flink Confluent Cloud for Apache Flink® provides these built-in string functions to use in SQL queries: | [ASCII](#flink-sql-ascii-function) | [BTRIM](#flink-sql-btrim-function) | [CHARACTER_LENGTH](#flink-sql-char-length-function) | [CHR](#flink-sql-chr-function) | |------------------------------------------------------|--------------------------------------------------------------|-------------------------------------------------------|------------------------------------------------------| | [string1 || string2](#flink-sql-concat-2-function) | [CONCAT](#flink-sql-concat-function) | [CONCAT_WS](#flink-sql-concat-ws-function) | [DECODE](#flink-sql-decode-function) | | [ELT](#flink-sql-elt-function) | [ENCODE](#flink-sql-encode-function) | [ENDSWITH](#flink-sql-endswith-function) | [FROM_BASE64](#flink-sql-from-base64-function) | | [INITCAP](#flink-sql-initcap-function) | [INSTR](#flink-sql-instr-function) | [LEFT](#flink-sql-left-function) | [LOCATE](#flink-sql-locate-function) | | [LOWER](#flink-sql-lower-function) | [LPAD](#flink-sql-lpad-function) | [LTRIM](#flink-sql-ltrim-function) | [OVERLAY](#flink-sql-overlay-function) | | [PARSE_URL](#flink-sql-parse-url-function) | [POSITION](#flink-sql-position-function) | [REGEXP](#flink-sql-regexp-function) | [REGEXP_COUNT](#flink-sql-regexp-count-function) | | [REGEXP_EXTRACT](#flink-sql-regexp-extract-function) | [REGEXP_EXTRACT_ALL](#flink-sql-regexp-extract-all-function) | [REGEXP_INSTR](#flink-sql-regexp-instr-function) | [REGEXP_REPLACE](#flink-sql-regexp-replace-function) | | [REGEXP_SUBSTR](#flink-sql-regexp-substr-function) | [REPEAT](#flink-sql-repeat-function) | [REPLACE](#flink-sql-replace-function) | [REVERSE](#flink-sql-reverse-function) | | [RIGHT](#flink-sql-right-function) | [RPAD](#flink-sql-rpad-function) | [RTRIM](#flink-sql-rtrim-function) | [SPLIT](#flink-sql-split-function) | | [SPLIT_INDEX](#flink-sql-split-index-function) | [STARTSWITH](#flink-sql-startswith-function) | [STR_TO_MAP](#flink-sql-str-to-map-function) | [SUBSTRING](#flink-sql-substring-function) | | [TO_BASE64](#flink-sql-to-base64-function) | [TRANSLATE](#flink-sql-translate-function) | [TRIM](#flink-sql-trim-function) | [UPPER](#flink-sql-upper-function) | | [URL_DECODE](#flink-sql-url-decode-function) | [URL_ENCODE](#flink-sql-url-encode-function) | | | ## ASCII Gets the ASCII value of the first character of a string. Syntax : ```sql ASCII(string) ``` Description : The `ASCII` function returns the numeric value of the first character of the specified string. Returns NULL if `string` is NULL. Examples : ```sql -- returns 97 SELECT ASCII('abc');
-- returns NULL SELECT ASCII(CAST(NULL AS VARCHAR)); ``` ## string1 || string2 Concatenates two strings. Syntax : ```sql string1 || string2 ``` Description : The `||` function returns the concatenation of `string1` and `string2`. Examples : ```sql -- returns "FlinkSQL" SELECT 'Flink' || 'SQL'; ``` Related functions : - [CONCAT](#flink-sql-concat-function) - [CONCAT_WS](#flink-sql-concat-ws-function) ## BTRIM Trim both sides of a string. Syntax : ```sql BTRIM(str[, trimStr]) ``` Arguments : - `str`: A source STRING expression. - `trimStr`: An optional STRING expression that has characters to be trimmed. The default is the space character. Returns : A trimmed STRING. Description : The `BTRIM` function trims the leading and trailing characters from `str`. Examples : ```sql -- returns 'www.apache.org' SELECT BTRIM(" www.apache.org ");
-- returns 'www.apache.org' SELECT BTRIM('/www.apache.org/', '/');
-- returns 'www.apache.org' SELECT BTRIM('/*www.apache.org*/', '/*'); ``` Related functions : - [LTRIM](#flink-sql-ltrim-function) - [RTRIM](#flink-sql-rtrim-function) - [TRIM](#flink-sql-trim-function) ## CHARACTER_LENGTH Gets the length of a string. Syntax : ```sql CHARACTER_LENGTH(string) ``` Description : The `CHARACTER_LENGTH` function returns the number of characters in the specified string.
This function can be abbreviated to `CHAR_LENGTH(string)`. Examples : ```sql -- returns 18 SELECT CHAR_LENGTH('Thomas A. Anderson'); ``` ## CHR Gets the character for an ASCII code. Syntax : ```sql CHR(integer) ``` Description : The `CHR` function returns the ASCII character that has the binary equivalent to the specified integer. Returns NULL if `integer` is NULL.
If `integer` is larger than *255*, the function computes the modulus of `integer` divided by *255* first and returns `CHR` of the modulus. Examples : ```sql -- returns 'a' SELECT CHR(97);
-- returns 'a' SELECT CHR(353); ``` ## CONCAT Concatenates a list of strings. Syntax : ```sql CONCAT(string1, string2, ...) ``` Description : The `CONCAT` function returns the concatenation of the specified strings. Returns NULL if any argument is NULL. Example : ```sql -- returns "AABBCC" SELECT CONCAT('AA', 'BB', 'CC'); ``` Related functions : - [string1 || string2](#flink-sql-concat-2-function) - [CONCAT_WS](#flink-sql-concat-ws-function) ## CONCAT_WS Concatenates a list of strings with a separator. Syntax : ```sql CONCAT_WS(string1, string2, string3, ...) ``` Description : The `CONCAT_WS` function returns a string that concatenates `string2, string3, ...` with the separator specified by `string1`.
The separator is added between the strings to be concatenated.
Returns NULL if `string1` is NULL. Example : ```sql -- returns "AA~BB~~CC" SELECT CONCAT_WS('~', 'AA', 'BB', '', 'CC'); ``` Related functions : - [string1 || string2](#flink-sql-concat-2-function) - [CONCAT](#flink-sql-concat-function) ## DECODE Decodes a binary into a string. Syntax : ```sql DECODE(binary, string) ``` Description : The `DECODE` function decodes the binary argument into a string using the specified character set. Returns NULL if either argument is null.
These are the supported character set strings:
- ‘ISO-8859-1’ - ‘US-ASCII’ - ‘UTF-8’ - ‘UTF-16BE’ - ‘UTF-16LE’ - ‘UTF-16’ Related function : - [ENCODE](#flink-sql-encode-function) ## ELT Gets the expression at the specified index. Syntax : ```sql ELT(index, expr[, exprs]*) ``` Arguments : - `index`: The 1-based index of the expression to get. `index` must be an integer between 1 and the number of expressions. - `expr`: An expression that resolves to CHAR, VARCHAR, BINARY, or VARBINARY. Returns : The expression at the location in the argument list specified by `index`. The result has the type of the least common type of all expressions.
Returns NULL if index is NULL or out of range. Description : Returns the index-th expression. Example : ```sql -- returns java-2 SELECT ELT(2, 'scala-1', 'java-2', 'go-3'); ``` ## ENCODE Encodes a string to a BINARY. Syntax : ```sql ENCODE(string1, string2) ``` Description : The `ENCODE` function encodes `string1` into a BINARY using the specified `string2` character set. Returns NULL if either argument is null.
These are the supported character set strings:
- ‘ISO-8859-1’ - ‘US-ASCII’ - ‘UTF-8’ - ‘UTF-16BE’ - ‘UTF-16LE’ - ‘UTF-16’ Related function : - [DECODE](#flink-sql-decode-function) ## ENDSWITH Syntax : ```sql ENDSWITH(expr, endExpr) ``` Returns : A BOOLEAN, or NULL if either `expr` or `endExpr` is NULL. Description : Returns whether `expr` ends with `endExpr`. If `endExpr` is empty, the result is TRUE.
`expr` and `endExpr` must have the same type:
```sql expr , endExpr
expr , endExpr ``` ## FROM_BASE64 Decodes a base-64 encoded string. Syntax : ```sql FROM_BASE64(string) ``` Description : The `FROM_BASE64` function returns the base64-decoded result from the specified string. Returns NULL if `string` is NULL. Example : ```sql -- returns "hello world" SELECT FROM_BASE64('aGVsbG8gd29ybGQ='); ``` Related function : - [TO_BASE64](#flink-sql-to-base64-function) ## INITCAP Titlecase a string. Syntax : ```sql INITCAP(string) ``` Description : The `INITCAP` function returns a string that has the first character of each word converted to uppercase and the other characters converted to lowercase.
A “word” is assumed to be a sequence of alphanumeric characters. Example : ```sql -- returns "Title Case This String" SELECT INITCAP('title case this string'); ``` Related functions : - [LOWER](#flink-sql-lower-function) - [UPPER](#flink-sql-upper-function) ## INSTR Find a substring in a string. Syntax : ```sql INSTR(string1, string2) ``` Description : The `INSTR` function returns the position of the first occurrence of `string2` in `string1`. Returns NULL if either argument is NULL.
The search is case-sensitive. Example : ```sql -- returns 33 SELECT INSTR('The quick brown fox jumped over the lazy dog.', 'the'); ``` Related function : - [LOCATE](#flink-sql-locate-function) ## LEFT Gets the leftmost characters in a string. Syntax : ```sql LEFT(string, integer) ``` Description : The `LEFT` function returns the leftmost `integer` characters from the specified string. Returns an empty string if `integer` is negative. Returns NULL if either argument is NULL. Example : ```sql -- returns "Morph" SELECT LEFT('Morpheus', 5); ``` Related function : - [RIGHT](#flink-sql-right-function) ## LOCATE Finds a substring in a string after a specified position. Syntax : ```sql LOCATE(string1, string2[, integer]) ``` Description : The `LOCATE` function returns the position of the first occurrence of `string1` in `string2` after position `integer`. Returns *0* if `string1` isn’t found. Returns NULL if any of the arguments is NULL. Example : ```sql -- returns 12 SELECT LOCATE('the', 'the play’s the thing', 10); ``` ## LOWER Lowercases a string. Syntax : ```sql LOWER(string) ``` Description : The `LOWER` function returns the specified string in lowercase.
To uppercase a string, use the [UPPER](#flink-sql-upper-function) function. Example : ```sql -- returns "the quick brown fox jumped over the lazy dog." SELECT LOWER('The Quick Brown Fox Jumped Over The Lazy Dog.'); ``` Related functions : - [INITCAP](#flink-sql-initcap-function) - [UPPER](#flink-sql-upper-function) ## LPAD Left-pad a string. Syntax : ```sql LPAD(string1, integer, string2) ``` Description : The `LPAD` function returns a new string from `string1` that’s left-padded with `string2` to a length of `integer` characters.
If the length of `string1` is shorter than `integer`, the `LPAD` function returns `string1` shortened to `integer` characters.
To right-pad a string, use the [RPAD](#flink-sql-rpad-function) function. Examples : ```sql -- returns "??hi" SELECT LPAD('hi', 4, '??');
-- returns "h" SELECT LPAD('hi', 1, '??'); ``` Related function - [RPAD](#flink-sql-rpad-function) ## LTRIM Removes left whitespaces from a string. Syntax : ```sql LTRIM(string) ``` Description : The `LTRIM` function removes the left whitespaces from the specified string.
To remove the right whitespaces from a string, use the [RTRIM](#flink-sql-rtrim-function) function. Example : ```sql -- returns "This is a test string." SELECT LTRIM(' This is a test string.'); ``` Related functions : - [BTRIM](#flink-sql-btrim-function) - [RTRIM](#flink-sql-rtrim-function) - [TRIM](#flink-sql-trim-function) ## OVERLAY Replaces characters in a string with another string. Syntax : ```sql OVERLAY(string1 PLACING string2 FROM integer1 [ FOR integer2 ]) ``` Description : The `OVERLAY` function returns a string that replaces `integer2` characters of `string1` with `string2`, starting from position `integer1`.
If `integer2` isn’t specified, the default is the length of `string2`. Examples : ```sql -- returns "xxxxxxxxx" SELECT OVERLAY('xxxxxtest' PLACING 'xxxx' FROM 6);
-- returns "xxxxxxxxxst" SELECT OVERLAY('xxxxxtest' PLACING 'xxxx' FROM 6 FOR 2); ``` Related functions : - [REGEXP_REPLACE](#flink-sql-regexp-replace-function) - [REPLACE](#flink-sql-replace-function) - [TRANSLATE](#flink-sql-translate-function) ## PARSE_URL Gets parts of a URL. Syntax : ```sql PARSE_URL(string1, string2[, string3]) ``` Description : The `PARSE_URL` function returns the part specified by `string2` from the URL in `string1`.
For a URL that has a query, the optional `string3` argument specifies the key to extract from the query string.
Returns NULL if `string1` or `string2` is NULL.
These are the valid values for `string2`:
- ‘AUTHORITY’ - ‘FILE’ - ‘HOST’ - ‘PATH’ - ‘PROTOCOL’ - ‘QUERY’ - ‘REF’ - ‘USERINFO’ Example : ```sql -- returns 'confluent.io' SELECT PARSE_URL('http://confluent.io/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST');
-- returns 'v1' SELECT PARSE_URL('http://confluent.io/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY', 'k1'); ``` ## POSITION Finds a substring in a string. Syntax : ```sql POSITION(string1 IN string2) ``` Description : The `POSITION` function returns the position of the first occurrence of `string1` in `string2`. Returns *0* if `string1` isn’t found in `string2`.
The position is 1-based, so the index of the first character is *1*. Examples : ```sql -- returns 1 SELECT POSITION('the' IN 'the quick brown fox');
-- returns 17 SELECT POSITION('fox' IN 'the quick brown fox'); ``` ## REGEXP Matches a string against a regular expression. Syntax : ```sql REGEXP(string1, string2) ``` Description : The `REGEXP` function returns TRUE if any (possibly empty) substring of `string1` matches the regular expression in `string2`; otherwise, FALSE. Returns NULL if either of the arguments is NULL. Examples : ```sql -- returns TRUE SELECT REGEXP('800 439 3207', '.?(\d{3}).*(\d{3}).*(\d{4})');
-- returns TRUE SELECT REGEXP('2023-05-04', '((\d{4}.\d{2}).(\d{2}))'); ``` ## REGEXP_COUNT Gets the number of times a string matches a regular expression. Syntax : ```sql REGEXP_COUNT(str, regex) ``` Description : The `REGEXP_COUNT` function returns an INTEGER representation of the number of times `str` matches the `regex` pattern. `regex` must be a Java regular expression.
The function returns NULL if:
- Either `str` or `regex` is NULL. - The regular expression is invalid. - No match is found. Example : ```sql -- returns 2 SELECT REGEXP_COUNT('foobar', 'oo|ar'); ``` ## REGEXP_EXTRACT Gets a string from a regular expression matching group. Syntax : ```sql REGEXP_EXTRACT(string1, string2[, integer]) ``` Description : The `REGEXP_EXTRACT` function returns a string from `string1` that’s extracted with the regular expression specified in `string2` and a regex match group index integer.
The regex match group index starts from *1*, and *0* specifies matching the whole regex.
The regex match group index must not exceed the number of the defined groups. Example : ```sql -- returns "bar" SELECT REGEXP_EXTRACT('foothebar', 'foo(.*?)(bar)', 2); ``` ## REGEXP_EXTRACT_ALL Gets all substrings that match a regular expression. Syntax : ```sql REGEXP_EXTRACT_ALL(str, regex[, extractIndex]) ``` Description : The `REGEXP_EXTRACT_ALL` function returns an ARRAY of strings from `str` that are extracted with the regular expression in `regex`.
The regular expression can contain multiple groups. If `extractIndex` is specified, it indicates which group to extract. The index is 1-based. A value of 0 indicates matching the entire regular expression. Examples : ```sql -- returns ['the'] SELECT REGEXP_EXTRACT_ALL('foothebar', 'foo(.*?)(bar)');
-- Extract only the first capture group (the digits) from the pattern. -- returns ['123', '456', '789'] SELECT REGEXP_EXTRACT_ALL('ID: 123, Code: 456, Ref: 789', '(\d+)', 1);
-- Extract the second capture group (the values) from the pattern. -- returns ['123', '456', '789'] SELECT REGEXP_EXTRACT_ALL('ID: 123, Code: 456, Ref: 789', '(\w+): (\d+)', 2);
-- Match the entire regular expression pattern and return the first capture group (the key). -- returns ['ID: 123', 'Code: 456', 'Ref: 789'] SELECT REGEXP_EXTRACT_ALL('ID: 123, Code: 456, Ref: 789', '(\w+): (\d+)', 0); ``` ## REGEXP_INSTR Gets the position of a substring that matches a regular expression. Syntax : ```sql REGEXP_INSTR(str, regex) ``` Description : The `REGEXP_INSTR` function returns the position of the first substring in `str` that matches `regex`, or 0 if there is no match.
The result index is 1-based. Example : ```sql -- returns 17 SELECT REGEXP_INSTR('the quick brown fox', 'fox');
-- returns 0 SELECT REGEXP_INSTR('the quick brown fox', 'dog');
-- returns 5 SELECT REGEXP_INSTR('the quick brown fox', 'quick|brown'); ``` ## REGEXP_REPLACE Replaces substrings in a string that match a regular expression. Syntax : ```sql REGEXP_REPLACE(string1, string2, string3) ``` Description : The `REGEXP_REPLACE` function returns a string from `string1` with all of the substrings that match the regular expression in `string2` consecutively replaced with `string3`. Example : ```sql -- returns "fb" SELECT REGEXP_REPLACE('foobar', 'oo|ar', ''); ``` Related functions : - [OVERLAY](#flink-sql-overlay-function) - [REPLACE](#flink-sql-replace-function) - [TRANSLATE](#flink-sql-translate-function) ## REGEXP_SUBSTR Gets a substring that matches a regular expression. Syntax : ```sql REGEXP_SUBSTR(str, regex) ``` Description : The `REGEXP_SUBSTR` function returns a STRING representation of the first substring that matches the regular expression `regex` in the string `str`.
The function returns NULL if:
- Either `str` or `regex` is NULL. - The regular expression is invalid. - No match is found. Example : ```sql -- returns "oo" SELECT REGEXP_SUBSTR('foobar', 'oo|ar'); ``` ## REPEAT Concatenates copies of a string. Syntax : ```sql REPEAT(string, integer) ``` Description : The `REPEAT` function returns a string that repeats the base string `integer` times. Example : ```sql -- returns "TestingTesting" SELECT REPEAT('Testing', 2); ``` ## REPLACE Replace substrings in a string. Syntax : ```sql REPLACE(string1, string2, string3) ``` Description : The `REPLACE` function returns a new string that replaces all occurrences of `string2` with `string3` (non-overlapping) from `string1`. Examples : ```sql -- returns "hello flink" SELECT REPLACE('hello world', 'world', 'flink');
-- returns "zab" SELECT REPLACE('ababab', 'abab', 'z'); ``` Related functions : - [OVERLAY](#flink-sql-overlay-function) - [REGEXP_REPLACE](#flink-sql-regexp-replace-function) - [TRANSLATE](#flink-sql-translate-function) ## REVERSE Reverses a string. Syntax : ```sql REVERSE(string) ``` Description : The `REVERSE` function returns the reversed string. Returns NULL if `string` is NULL. Example : ```sql -- returns "xof nworb kciuq eht" SELECT REVERSE('the quick brown fox'); ``` ## RIGHT Gets the rightmost characters in a string. Syntax : ```sql RIGHT(string, integer) ``` Description : The `RIGHT` function returns the rightmost `integer` characters from the specified string. Returns an empty string if `integer` is negative. Returns NULL if either argument is NULL. Example : ```sql -- returns "Anderson" SELECT RIGHT('Thomas A. Anderson', 8); ``` Related function : - [LEFT](#flink-sql-left-function) ## RPAD Right-pad a string. Syntax : ```sql RPAD(string1, integer, string2) ``` Description : The `RPAD` function returns a new string from `string1` that’s right-padded with `string2` to a length of `integer` characters.
If the length of `string1` is shorter than `integer`, returns `string1` shortened to `integer` characters.
To left-pad a string, use the [LPAD](#flink-sql-lpad-function) function. Examples : ```sql -- returns "hi??" SELECT RPAD('hi', 4, '??');
-- returns "h" SELECT RPAD('hi', 1, '??'); ``` Related function : - [LPAD](#flink-sql-lpad-function) ## RTRIM Removes right whitespaces from a string. Syntax : ```sql RTRIM(string) ``` Description : The `RTRIM` function removes the right whitespaces from the specified string.
To remove the left whitespaces from a string, use the [LTRIM](#flink-sql-ltrim-function) function. Example : ```sql -- returns "This is a test string." SELECT RTRIM('This is a test string. '); ``` Related functions : - [BTRIM](#flink-sql-btrim-function) - [LTRIM](#flink-sql-ltrim-function) - [TRIM](#flink-sql-trim-function) ## SPLIT Splits a string into an array of substrings. Syntax : ```sql SPLIT(string, delimiter) ``` Arguments : - `string`: A source STRING expression. - `delimiter`: A STRING expression that specifies the delimiter to split by. Returns : An ARRAY of STRING values. Returns NULL if either argument is NULL. Description : The `SPLIT` function returns an array of substrings by splitting the input string based on the given delimiter.
If the delimiter is not found, the original string is returned as a single array element.
An empty delimiter causes each character to be split individually.
Leading, trailing, or consecutive delimiters produce empty strings in the result array. Examples : ```sql -- returns ['123', '123', '23'] SELECT SPLIT('123,123,23', ',');
-- returns ['', '123', '123'] SELECT SPLIT(',123,123', ',');
-- delimiter not found -- returns ['12345'] SELECT SPLIT('12345', ',');
-- empty strings preserved -- returns ['', '123', '', '', '123', ''] SELECT SPLIT(',123,,,123,', ',');
-- empty delimiter splits each character -- returns ['t', 'h', 'e'] SELECT SPLIT('the', ''); ``` Related function : - [SPLIT_INDEX](#flink-sql-split-index-function) ## SPLIT_INDEX Splits a string by a delimiter. Syntax : ```sql SPLIT_INDEX(string1, string2, integer1) ``` Description : The `SPLIT_INDEX` function splits `string1` by the delimiter in `string2` and returns the `integer1` zero-based string of the split strings. Returns NULL if `integer` is negative. Returns NULL if any of the arguments is NULL. Example : ```sql -- returns "fox" SELECT SPLIT_INDEX('The quick brown fox', ' ', 3); ``` Related function : - [SPLIT](#flink-sql-split-function) ## STARTSWITH Syntax : ```sql STARTSWITH(expr, startExpr) ``` Returns : A BOOLEAN, or NULL if either `expr` or `startExpr` is NULL. Description : Returns whether `expr` starts with `startExpr`. If `startExpr` is empty, the result is TRUE.
`expr` and `startExpr` must have the same type:
```sql expr , startExpr
expr , startExpr ``` ## STR_TO_MAP Creates a map from a list of key-value strings. Syntax : ```sql STR_TO_MAP(string1[, string2, string3]) ``` Description : The `STR_TO_MAP` function returns a map after splitting `string1` into key/value pairs using the pair delimiter specified in `string2`. The default is `','`. The `string3` argument specifies the key-value delimiter. The default is `'='`.
Both the pair delimiter and the key-value delimiter are treated as regular expressions, so special characters, like `<([{\^-=$!|]})?*+.>)`, must be properly escaped before using as a delimiter literal. Example : ```sql -- returns {a=1, b=2, c=3} SELECT STR_TO_MAP('a=1,b=2,c=3');
-- returns {a=1, b=2, c=3} SELECT STR_TO_MAP('a:1;b:2;c:3', ';', ':'); ``` ## SUBSTRING Finds a substring in a string. Syntax : ```sql SUBSTRING(string, integer1 [ FOR integer2 ]) ``` Description : The `SUBSTRING` function returns a substring of the specified string, starting from position `integer1` with length `integer2`.
If `integer2` isn’t specified, the substring runs to the end of `string`.
This function can be abbreviated to `SUBSTR(string, integer1[, integer2])`, but `SUBSTR` doesn’t support the `FROM` and `FOR` keywords. Examples : ```sql -- returns "fox" SELECT SUBSTR('The quick brown fox', 17);
-- returns "The" SELECT SUBSTR('The quick brown fox', 1, 3); ``` ## TO_BASE64 Encodes a string to base64. Syntax : ```sql TO_BASE64(string) ``` Description : The `TO_BASE64` function returns the base64-encoded representation of the specified string. Returns NULL if `string` is NULL. Example : ```sql -- returns "aGVsbG8gd29ybGQ=" SELECT TO_BASE64('hello world'); ``` Related function : - [FROM_BASE64](#flink-sql-from-base64-function) ## TRANSLATE Substitutes characters in a string. Syntax : ```sql TRANSLATE(expr, from, to) ``` Arguments : - `expr`: A source STRING expression. - `from`: A STRING expression that specifies a set of characters to be replaced. - `to`: A STRING expression that specifies a corresponding set of replacement characters. Returns : A STRING that has the characters of `expr` replaced with the characters specified in the `to` string. Description : The `TRANSLATE` function replaces the characters in the `expr` source string according to the replacement rules specified in the `from` and `to` strings.
If `to` has a shorter length than `from`, unmatched characters are removed.
The replacement is case-sensitive. Examples : ```sql -- returns A1B2C3 SELECT TRANSLATE('AaBbCc', 'abc', '123');
-- returns A1BC SELECT TRANSLATE('AaBbCc', 'abc', '1');
-- returns ABC SELECT TRANSLATE('AaBbCc', 'abc', '');
-- returns .APACHE.com SELECT TRANSLATE('www.apache.org', 'wapcheorg', ' APCHEcom'); ``` Related functions : - [OVERLAY](#flink-sql-overlay-function) - [REGEXP_REPLACE](#flink-sql-regexp-replace-function) - [REPLACE](#flink-sql-replace-function) ## TRIM Removes leading and/or trailing characters from a string. Syntax : ```sql TRIM([ BOTH | LEADING | TRAILING ] string1 FROM string2) ``` Description : The `TRIM` function returns a string that removes leading and/or trailing characters `string2` from `string1`. Examples : ```sql -- returns "The quick brown " SELECT TRIM(TRAILING 'fox' FROM 'The quick brown fox');
-- returns " quick brown fox" SELECT TRIM(LEADING 'The' FROM 'The quick brown fox');
-- returns " The quick brown fox " SELECT TRIM(BOTH 'yyy' FROM 'yyy The quick brown fox yyy'); ``` Related functions : - [BTRIM](#flink-sql-btrim-function) - [LTRIM](#flink-sql-ltrim-function) - [RTRIM](#flink-sql-rtrim-function) ## UPPER Uppercases a string. Syntax : ```sql UPPER(string) ``` Description : The `UPPER` function returns the specified string in uppercase.
To lowercase a string, use the [LOWER](#flink-sql-lower-function) function. Example : ```sql -- returns "THE QUICK BROWN FOX" SELECT UPPER('The quick brown fox'); ``` ## URL_DECODE Decodes a URL string. Syntax : ```sql URL_DECODE(string) ``` Description : The `URL_DECODE` function decodes the specified string in `application/x-www-form-urlencoded` format using the UTF-8 encoding scheme.
The function returns NULL if:
- The input string is NULL. - There is an issue with the decoding process, like encountering an illegal escape pattern. - The encoding scheme is not supported. Example : ```sql -- returns "http://confluent.io" SELECT URL_DECODE('http%3A%2F%2Fconfluent.io'); ``` ## URL_ENCODE Encodes a URL string. Syntax : ```sql URL_ENCODE(string) ``` Description : The `URL_ENCODE` function translates the specified string into `application/x-www-form-urlencoded` format using the UTF-8 encoding scheme.
The function returns NULL if:
- The input string is NULL. - There is an issue with the encoding process. - The encoding scheme is not supported. Example : ```sql -- returns "http%3A%2F%2Fconfluent.io" SELECT URL_ENCODE('http://confluent.io'); ``` ## Other built-in functions - [Aggregate Functions](aggregate-functions.md#flink-sql-aggregate-functions) - [Changelog Conversion Functions](changelog-conversion.md#flink-sql-changelog-conversion-functions) - [Collection Functions](collection-functions.md#flink-sql-collection-functions) - [Comparison Functions](comparison-functions.md#flink-sql-comparison-functions) - [Conditional Functions](conditional-functions.md#flink-sql-conditional-functions) - [Datetime Functions](datetime-functions.md#flink-sql-datetime-functions) - [Hash Functions](hash-functions.md#flink-sql-hash-functions) - [JSON Functions](json-functions.md#flink-sql-json-functions) - [ML Preprocessing Functions](ml-preprocessing-functions.md#flink-sql-ml-preprocessing-functions) - [Model Inference Functions](model-inference-functions.md#flink-sql-model-inference-functions) - [Numeric Functions](numeric-functions.md#flink-sql-numeric-functions) - [Search Functions](search-functions.md#flink-sql-search-functions) - [String Functions](#flink-sql-string-functions) - [Table API Functions](table-api-functions.md#flink-table-api-functions) ## Related content - [User-defined Functions](../../concepts/user-defined-functions.md#flink-sql-udfs) - [Create a User Defined Function](../../how-to-guides/create-udf.md#flink-sql-create-udf) #### NOTE This website includes content developed at the [Apache Software Foundation](https://www.apache.org/) under the terms of the [Apache License v2](https://www.apache.org/licenses/LICENSE-2.0.html).