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:
Output: string
Description:Returns the character that has the ASCII code value specified by the numeric input. The value should be between 0 and 255; otherwise, the return value is data source–dependent.
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:
Output: string
Description:Returns a character string that is the result of concatenating string_exp1
to string_exp2
. If one of the string is NULL
, the other string will be returned.
SELECT CONCAT('Elasticsearch', ' SQL'); CONCAT('Elasticsearch', ' SQL') ------------------------------- Elasticsearch SQL
INSERT
editSynopsis:
Input:
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
.
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:
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:
Output: integer
Description:Returns the starting position of the first occurrence of pattern
within source
. The search for the first occurrence of pattern
begins with the first character position in source
unless the optional argument, start
, is specified. If start
is specified, the search begins with the character position indicated by the value of start
. The first character position in source
is indicated by the value 1. If pattern
is not found within source
, the value 0 is returned.
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:
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:
Output: string
Description:Returns a character string composed of string_exp
repeated count
times.
SELECT REPEAT('La', 3); REPEAT('La', 3) ---------------- LaLaLa
REPLACE
editSynopsis:
Input:
Output: string
Description:Search source
for occurrences of pattern
, and replace with replacement
.
SELECT REPLACE('Elastic','El','Fant'); REPLACE('Elastic','El','Fant') ------------------------------ Fantastic
RIGHT
editSynopsis:
Input:
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.
SELECT SPACE(3); SPACE(3) ---------------
SUBSTRING
editSynopsis:
Input:
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
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