String Functionsedit

Functions for performing string manipulation.

ASCIIedit

Input: string, Output: integer

Returns the ASCII code value of the leftmost character of string_exp as an integer.

SELECT ASCII('Elastic');

ASCII(Elastic)
---------------
69

BIT_LENGTHedit

Input: string, Output: integer

Returns the length in bits of the input.

SELECT BIT_LENGTH('Elastic');

BIT_LENGTH(Elastic)
-------------------
56

CHARedit

Input: numeric, Output: string

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_LENGTHedit

Input: string, Output: integer

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

CONCATedit

Input: string1, string2, Output: string

turns a character string that is the result of concatenating string1 to string2. If one of the string is NULL, the other string will be returned.

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

CONCAT(Elasticsearch, SQL)
--------------------------
Elasticsearch SQL

INSERTedit

Input: string1, start, length, string2, Output: string

Returns a string where length characters have been deleted from string1, beginning at start, and where string2 has been inserted into string1, beginning at start.

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

INSERT(Elastic ,8,1,search)
---------------------------
Elasticsearch

LCASEedit

Input: string, Output: string

Returns a string equal to that in string, with all uppercase characters converted to lowercase.

SELECT LCASE('Elastic');

LCASE(Elastic)
---------------
elastic

LEFTedit

Input: string, Output: string

Returns the leftmost count characters of string.

SELECT LEFT('Elastic',3);

LEFT(Elastic,3)
---------------
Ela

LENGTHedit

Input: string, Output: integer

Returns the number of characters in string, excluding trailing blanks.

SELECT LENGTH('Elastic   ');

LENGTH(Elastic   )
------------------
7

LOCATEedit

Input: string1, string2[, start], *Output*: `integer

Returns the starting position of the first occurrence of string1 within string2. The search for the first occurrence of string1 begins with the first character position in string2 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 string2 is indicated by the value 1. If string1 is not found within string2, the value 0 is returned.

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

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

LOCATE(a,Elasticsearch,5)
-------------------------
10

LTRIMedit

Input: string, Output: string

Returns the characters of string_exp, with leading blanks removed.

SELECT LTRIM('   Elastic');

LTRIM(   Elastic)
-----------------
Elastic

POSITIONedit

Input: string1, string2, Output: integer

Returns the position of the string1 in string2. The result is an exact numeric.

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

POSITION(Elastic,Elasticsearch)
-------------------------------
1

REPEATedit

Input: string, count, Output: string

Returns a character string composed of string1 repeated count times.

SELECT REPEAT('La', 3);

 REPEAT(La,3)
---------------
LaLaLa

REPLACEedit

Input: string1, string2, string3, Output: string

Search string1 for occurrences of string2, and replace with string3.

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

  REPLACE(Elastic, El, Fant)
-----------------------------
Fantastic

RIGHTedit

Input: string, count, Output: string

Returns the rightmost count characters of string.

SELECT RIGHT('Elastic',3);

RIGHT(Elastic,3)
----------------
tic

RTRIMedit

Input: string, Output: string

Returns the characters of string with trailing blanks removed.

SELECT RTRIM('Elastic   ');

RTRIM(Elastic   )
-----------------
Elastic

SPACEedit

Input: integer, Output: string

Returns a character string consisting of count spaces.

SELECT SPACE(3);

   SPACE(3)
---------------

SUBSTRINGedit

Input: string, start, length, Output: integer

Returns a character string that is derived from the string, beginning at the character position specified by start for length characters.

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

SUBSTRING(Elasticsearch,0,7)
----------------------------
Elastic

UCASEedit

Input: string, Output: string

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

SELECT UCASE('Elastic');

UCASE(Elastic)
---------------
ELASTIC