String Functions
editString Functions
editThis functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features.
Functions 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