String Functions
editString Functions
editFunctions for performing string manipulation.
ASCII
editSynopsis:
Input:
Output: integer
Description: Returns the ASCII code value of the leftmost character of string_exp
as an integer.
SELECT ASCII('Elastic'); ASCII('Elastic') ---------------- 69
BIT_LENGTH
editSynopsis:
Input:
Output: integer
Description: Returns the length in bits of the string_exp
input expression.
SELECT BIT_LENGTH('Elastic'); BIT_LENGTH('Elastic') --------------------- 56
CHAR
editSynopsis:
Input:
integer expression between |
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
editSynopsis:
Input:
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
editSynopsis:
Input:
string expression. Treats |
|
string expression. Treats |
Output: string
Description: Returns a character string that is the result of concatenating string_exp1
to string_exp2
.
The resulting string cannot exceed a byte length of 1 MB.
SELECT CONCAT('Elasticsearch', ' SQL'); CONCAT('Elasticsearch', ' SQL') ------------------------------- Elasticsearch SQL
INSERT
editSynopsis:
Input:
string expression. If |
|
integer expression. If |
|
integer expression. If |
|
string expression. If |
Output: string
Description: Returns a string where length
characters have been deleted from source
, beginning at start
, and where replacement
has been inserted into source
, beginning at start
.
The resulting string cannot exceed a byte length of 1 MB.
SELECT INSERT('Elastic ', 8, 1, 'search'); INSERT('Elastic ', 8, 1, 'search') ---------------------------------- Elasticsearch
LCASE
editSynopsis:
Input:
Output: string
Description: Returns a string equal to that in string_exp
, with all uppercase characters converted to lowercase.
SELECT LCASE('Elastic'); LCASE('Elastic') ---------------- elastic
LEFT
editSynopsis:
Input:
string expression. If |
|
integer expression. If |
Output: string
Description: Returns the leftmost count characters of string_exp
.
SELECT LEFT('Elastic',3); LEFT('Elastic',3) ----------------- Ela
LENGTH
editSynopsis:
Input:
Output: integer
Description: Returns the number of characters in string_exp
, excluding trailing blanks.
SELECT LENGTH('Elastic '); LENGTH('Elastic ') -------------------- 7
LOCATE
editSynopsis:
Input:
string expression. If |
|
string expression. If |
|
integer expression; optional. If |
Output: integer
Description: Returns the starting position of the first occurrence of
pattern
within source
. The optional start
specifies the character position
to start the search with. If the pattern
is not found within source
, the
function returns 0
.
SELECT LOCATE('a', 'Elasticsearch'); LOCATE('a', 'Elasticsearch') ---------------------------- 3
SELECT LOCATE('a', 'Elasticsearch', 5); LOCATE('a', 'Elasticsearch', 5) ------------------------------- 10
LTRIM
editSynopsis:
Input:
Output: string
Description: Returns the characters of string_exp
, with leading blanks removed.
SELECT LTRIM(' Elastic'); LTRIM(' Elastic') ------------------- Elastic
OCTET_LENGTH
editSynopsis:
Input:
Output: integer
Description: Returns the length in bytes of the string_exp
input expression.
SELECT OCTET_LENGTH('Elastic'); OCTET_LENGTH('Elastic') ----------------------- 7
POSITION
editSynopsis:
Input:
string expression. If |
|
string expression. If |
Output: integer
Description: Returns the position of the string_exp1
in string_exp2
. The result is an exact numeric.
SELECT POSITION('Elastic', 'Elasticsearch'); POSITION('Elastic', 'Elasticsearch') ------------------------------------ 1
REPEAT
editSynopsis:
Input:
string expression. If |
|
integer expression. If |
Output: string
Description: Returns a character string composed of string_exp
repeated count
times.
The resulting string cannot exceed a byte length of 1 MB.
SELECT REPEAT('La', 3); REPEAT('La', 3) ---------------- LaLaLa
REPLACE
editSynopsis:
Input:
string expression. If |
|
string expression. If |
|
string expression. If |
Output: string
Description: Search source
for occurrences of pattern
, and replace with replacement
.
The resulting string cannot exceed a byte length of 1 MB.
SELECT REPLACE('Elastic','El','Fant'); REPLACE('Elastic','El','Fant') ------------------------------ Fantastic
RIGHT
editSynopsis:
Input:
string expression. If |
|
integer expression. If |
Output: string
Description: Returns the rightmost count characters of string_exp
.
SELECT RIGHT('Elastic',3); RIGHT('Elastic',3) ------------------ tic
RTRIM
editSynopsis:
Input:
Output: string
Description: Returns the characters of string_exp
with trailing blanks removed.
SELECT RTRIM('Elastic '); RTRIM('Elastic ') ------------------- Elastic
SPACE
editSynopsis:
Input:
Output: string
Description: Returns a character string consisting of count
spaces.
The resulting string cannot exceed a byte length of 1 MB.
SELECT SPACE(3); SPACE(3) ---------------
STARTS_WITH
editSynopsis:
Input:
string expression. If |
|
string expression. If |
Output: boolean value
Description: Returns true
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
editSynopsis:
Input:
string expression. If |
|
integer expression. If |
|
integer expression. If |
Output: string
Description: Returns a character string that is derived from source
, beginning at the character position specified by start
for length
characters.
SELECT SUBSTRING('Elasticsearch', 0, 7); SUBSTRING('Elasticsearch', 0, 7) -------------------------------- Elastic
TRIM
editSynopsis:
Input:
Output: string
Description: Returns the characters of string_exp
, with leading and trailing blanks removed.
SELECT TRIM(' Elastic ') AS trimmed; trimmed -------------- Elastic
UCASE
editSynopsis:
Input:
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