ASCII(string_exp)

Input:

1. string expression. Ifnull, the function returnsnull.

Output: integer

Description: Returns the ASCII code value of the leftmost character ofstring_exp as an integer.

SELECT ASCII('Elastic');ASCII('Elastic')----------------69

BIT_LENGTH(string_exp)

Input:

1. string expression. Ifnull, the function returnsnull.

Output: integer

Description: Returns the length in bits of thestring_exp input expression.

SELECT BIT_LENGTH('Elastic');BIT_LENGTH('Elastic')---------------------56

CHAR(code)

Input:

1. integer expression between0 and255. Ifnull, negative, or greater than255, the function returnsnull.

Output: string

Description: Returns the character that has the ASCII code value specified by the numeric input.

SELECT CHAR(69);   CHAR(69)---------------E

CHAR_LENGTH(string_exp)

Input:

1. string expression. Ifnull, the function returnsnull.

Output: integer

Description: Returns the length in characters of the input, if the string expression is of a character data type; otherwise, returns the length in bytes of the string expression (the smallest integer not less than the number of bits divided by 8).

SELECT CHAR_LENGTH('Elastic');CHAR_LENGTH('Elastic')----------------------7

CONCAT(    string_exp1,    string_exp2)

Input:

1. string expression. Treatsnull as an empty string.
2. string expression. Treatsnull as an empty string.

Output: string

Description: Returns a character string that is the result of concatenatingstring_exp1 tostring_exp2.

The resulting string cannot exceed a byte length of 1 MB.

SELECT CONCAT('Elasticsearch', ' SQL');CONCAT('Elasticsearch', ' SQL')-------------------------------Elasticsearch SQL

INSERT(    source,    start,    length,    replacement)

Input:

1. string expression. Ifnull, the function returnsnull.
2. integer expression. Ifnull, the function returnsnull.
3. integer expression. Ifnull, the function returnsnull.
4. string expression. Ifnull, the function returnsnull.

Output: string

Description: Returns a string wherelength characters have been deleted fromsource, beginning atstart, and wherereplacement has been inserted intosource, beginning atstart.

The resulting string cannot exceed a byte length of 1 MB.

SELECT INSERT('Elastic ', 8, 1, 'search');INSERT('Elastic ', 8, 1, 'search')----------------------------------Elasticsearch

LCASE(string_exp)

Input:

1. string expression. Ifnull, the function returnsnull.

Output: string

Description: Returns a string equal to that instring_exp, with all uppercase characters converted to lowercase.

SELECT LCASE('Elastic');LCASE('Elastic')----------------elastic

LEFT(    string_exp,    count)

Input:

1. string expression. Ifnull, the function returnsnull.
2. integer expression. Ifnull, the function returnsnull. If0 or negative, the function returns an empty string.

Output: string

Description: Returns the leftmost count characters ofstring_exp.

SELECT LEFT('Elastic',3);LEFT('Elastic',3)-----------------Ela

LENGTH(string_exp)

Input:

1. string expression. Ifnull, the function returnsnull.

Output: integer

Description: Returns the number of characters instring_exp, excluding trailing blanks.

SELECT LENGTH('Elastic   ');LENGTH('Elastic   ')--------------------7

LOCATE(    pattern,    source    [, start]<3>)

Input:

1. string expression. Ifnull, the function returnsnull.
2. string expression. Ifnull, the function returnsnull.
3. integer expression; optional. Ifnull,0,1, negative, or not specified, the search starts at the first character position.

Output: integer

Description: Returns the starting position of the first occurrence ofpattern withinsource. The optionalstart specifies the character position to start the search with. If thepattern is not found withinsource, the function returns0.

SELECT LOCATE('a', 'Elasticsearch');LOCATE('a', 'Elasticsearch')----------------------------3

SELECT LOCATE('a', 'Elasticsearch', 5);LOCATE('a', 'Elasticsearch', 5)-------------------------------10

LTRIM(string_exp)

Input:

1. string expression. Ifnull, the function returnsnull.

Output: string

Description: Returns the characters ofstring_exp, with leading blanks removed.

SELECT LTRIM('   Elastic');LTRIM('   Elastic')-------------------Elastic

OCTET_LENGTH(string_exp)

Input:

1. string expression. Ifnull, the function returnsnull.

Output: integer

Description: Returns the length in bytes of thestring_exp input expression.

SELECT OCTET_LENGTH('Elastic');OCTET_LENGTH('Elastic')-----------------------7

POSITION(    string_exp1,    string_exp2)

Input:

1. string expression. Ifnull, the function returnsnull.
2. string expression. Ifnull, the function returnsnull.

Output: integer

Description: Returns the position of thestring_exp1 instring_exp2. The result is an exact numeric.

SELECT POSITION('Elastic', 'Elasticsearch');POSITION('Elastic', 'Elasticsearch')------------------------------------1

REPEAT(    string_exp,    count)

Input:

1. string expression. Ifnull, the function returnsnull.
2. integer expression. If0, negative, ornull, the function returnsnull.

Output: string

Description: Returns a character string composed ofstring_exp repeatedcount times.

The resulting string cannot exceed a byte length of 1 MB.

SELECT REPEAT('La', 3); REPEAT('La', 3)----------------LaLaLa

REPLACE(    source,    pattern,    replacement)

Input:

1. string expression. Ifnull, the function returnsnull.
2. string expression. Ifnull, the function returnsnull.
3. string expression. Ifnull, the function returnsnull.

Output: string

Description: Searchsource for occurrences ofpattern, and replace withreplacement.

The resulting string cannot exceed a byte length of 1 MB.

SELECT REPLACE('Elastic','El','Fant');REPLACE('Elastic','El','Fant')------------------------------Fantastic

RIGHT(    string_exp,    count)

Input:

1. string expression. Ifnull, the function returnsnull.
2. integer expression. Ifnull, the function returnsnull. If0 or negative, the function returns an empty string.

Output: string

Description: Returns the rightmost count characters ofstring_exp.

SELECT RIGHT('Elastic',3);RIGHT('Elastic',3)------------------tic

RTRIM(string_exp)

Input:

1. string expression. Ifnull, the function returnsnull.

Output: string

Description: Returns the characters ofstring_exp with trailing blanks removed.

SELECT RTRIM('Elastic   ');RTRIM('Elastic   ')-------------------Elastic

SPACE(count)

Input:

1. integer expression. Ifnull or negative, the function returnsnull.

Output: string

Description: Returns a character string consisting ofcount spaces.

The resulting string cannot exceed a byte length of 1 MB.

SELECT SPACE(3);   SPACE(3)---------------

STARTS_WITH(    source,    pattern)

Input:

1. string expression. Ifnull, the function returnsnull.
2. string expression. Ifnull, the function returnsnull.

Output: boolean value

Description: Returnstrue if the source expression starts with the specified pattern,false otherwise. The matching is case sensitive.

SELECT STARTS_WITH('Elasticsearch', 'Elastic');STARTS_WITH('Elasticsearch', 'Elastic')--------------------------------true

SELECT STARTS_WITH('Elasticsearch', 'ELASTIC');STARTS_WITH('Elasticsearch', 'ELASTIC')--------------------------------false

SUBSTRING(    source,    start,    length)

Input:

1. string expression. Ifnull, the function returnsnull.
2. integer expression. Ifnull, the function returnsnull.
3. integer expression. Ifnull, the function returnsnull.

Output: string

Description: Returns a character string that is derived fromsource, beginning at the character position specified bystart forlength characters.

SELECT SUBSTRING('Elasticsearch', 0, 7);SUBSTRING('Elasticsearch', 0, 7)--------------------------------Elastic

TRIM(string_exp)

Input:

1. string expression. Ifnull, the function returnsnull.

Output: string

Description: Returns the characters ofstring_exp, with leading and trailing blanks removed.

SELECT TRIM('   Elastic   ') AS trimmed;trimmed--------------Elastic

UCASE(string_exp)

Input:

1. string expression. Ifnull, the function returnsnull.

Output: string

Description: Returns a string equal to that of the input, with all lowercase characters converted to uppercase.

SELECT UCASE('Elastic');UCASE('Elastic')----------------ELASTIC