String Functions

ascii(string str)

INT

Returns the numeric value of the first character in a string.

concat(string A, string B...)

STRING

Return a string resulting from concatenating the input strings. This function can take any number of input strings.

concat_ws(string SEP, string A, string B...)

STRING

Return a string resulting from concatenating the input strings, which are separated by specified separators.

encode(string src, string charset)

BINARY

Encode src in the encoding mode specified by charset.

find_in_set(string str, string strList)

INT

Return the position of the first occurrence of str in strList. If the value of any parameter is NULL, NULL is returned. If the first parameter contains a comma (,), 0 is returned.

get_json_object(string json_string, string path)

STRING

Parse the JSON object in a specified JSON path. The function will return NULL if the JSON object is invalid.

instr(string str, string substr)

INT

Return the position of the first occurrence of substr in str. Return NULL if NULL is contained in the parameters and return 0 if substr does not exist in str. Note that the subscripts start from 1.

length(string A)

INT

Return the length of a string.

locate(string substr, string str[, int pos])

INT

Return the position of the first occurrence of substr in str after position pos (starting from 1).

lower(string A) lcase(string A)

STRING

Convert all characters of a string to lower case.

lpad(string str, int len, string pad)

STRING

Return a string of a specified length. If the length of the given string (str) is shorter than the specified length (len), the given string is left-padded with pad to the specified length.

ltrim(string A)

STRING

Trim spaces from the left hand side of a string.

parse_url(string urlString, string partToExtract [, string keyToExtract])

STRING

Return the specified part of the specified URL. Valid values of partToExtract include HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO.

For example, parse_url ('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1 ',' HOST ') returns 'facebook.com'.

When the second parameter is QUERY, the third parameter can be used to extract the value of a specific parameter. For example, parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY', 'k1') returns 'v1'.

printf(String format, Obj... args)

STRING

Print the input according to a specified format.

regexp_extract(string subject, string pattern, int index)

STRING

Extract the string specified by the regular expression. regexp_extract ('foothebar ',' foo (.*?) (bar) '2) returns 'bar.'

regexp_replace(string A, string B, string C)

STRING

Replace character B in string A with character C.

repeat(string str, int n)

STRING

Repeat a string N times.

reverse(string A)

STRING

Return the reversed string.

rpad(string str, int len, string pad)

STRING

Return a string of a specified length. If the length of the given string (str) is shorter than the specified length (len), the given string is right-padded with pad to the specified length.

rtrim(string A)

STRING

Trim spaces from the right hand side of a string.

space(int n)

STRING

Returns a specified number of spaces.

substr(string A, int start) substring(string A, int start)

STRING

Return the substring starting from the specified start position in string A till the end of the string.

substr(string A, int start, int len) substring(string A, int start, int len)

STRING

Return the substring of a specified length starting from the specified start position in A string.

substring_index(string A, string delim, int count)

STRING

Return the substring from string A before count occurrences of the delimiter delim.

translate(string|char|varchar input, string|char|varchar from, string|char|varchar to)

STRING

Translate the input string by replacing the characters or string specified by from with the characters or string specified by to. For example, replace bcd in abcde with BCD using translate ("abcde", "bcd", "BCD").

trim(string A)

STRING

Trim spaces from both ends of a string.

upper(string A) ucase(string A)

STRING

Convert all characters of a string to upper case.

initcap(string A)

STRING

Convert the first letter of each word of a string to upper case and all other letters to lower case.

levenshtein(string A, string B)

INT

Return the Levenshtein distance between two strings. Example: levenshtein ('kitten ',' sitting ') = 3.

soundex(string A)

STRING

Return the soundex string from str. Example: soundex ('Miller ') = M460.