NOTE: You are looking at documentation for an older release. For the latest information, see the current release documentation.

« Mathematical Functions Type Conversion Functions »

› › ›

String Functions

edit

String Functions

edit

Functions for performing string manipulation.

`ASCII`

edit

Synopsis:

ASCII(string_exp)

Input:

string expression

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`

edit

Synopsis:

BIT_LENGTH(string_exp)

Input:

string expression

Output: integer

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

SELECT BIT_LENGTH('Elastic');

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

`CHAR`

edit

Synopsis:

CHAR(code)

Input:

integer expression

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`

edit

Synopsis:

CHAR_LENGTH(string_exp)

Input:

string expression

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`

edit

Synopsis:

CONCAT(
    string_exp1, 
    string_exp2)

Input:

	string expression
	string expression

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`

edit

Synopsis:

INSERT(
    source,      
    start,       
    length,      
    replacement)

Input:

	string expression
	integer expression
	integer expression
	string expression

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`

edit

Synopsis:

LCASE(string_exp)

Input:

string expression

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`

edit

Synopsis:

LEFT(
    string_exp, 
    count)

Input:

	string expression
	integer expression

Output: string

Description:Returns the leftmost count characters of string_exp.

SELECT LEFT('Elastic',3);

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

`LENGTH`

edit

Synopsis:

LENGTH(string_exp)

Input:

string expression

Output: integer

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

SELECT LENGTH('Elastic   ');

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

`LOCATE`

edit

Synopsis:

LOCATE(
    pattern, 
    source   
    [, start]
)

Input:

	string expression
	string expression
	integer expression; optional

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`

edit

Synopsis:

LTRIM(string_exp)

Input:

string expression

Output: string

Description:Returns the characters of string_exp, with leading blanks removed.

SELECT LTRIM('   Elastic');

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

`OCTET_LENGTH`

edit

Synopsis:

OCTET_LENGTH(string_exp)

Input:

string expression

Output: integer

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

SELECT OCTET_LENGTH('Elastic');

OCTET_LENGTH('Elastic')
-----------------------
7

`POSITION`

edit

Synopsis:

POSITION(
    string_exp1, 
    string_exp2)

Input:

	string expression
	string expression

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`

edit

Synopsis:

REPEAT(
    string_exp, 
    count)

Input:

	string expression
	integer expression

Output: string

Description:Returns a character string composed of string_exp repeated count times.

SELECT REPEAT('La', 3);

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

`REPLACE`

edit

Synopsis:

REPLACE(
    source,      
    pattern,     
    replacement)

Input:

	string expression
	string expression
	string expression

Output: string

Description:Search source for occurrences of pattern, and replace with replacement.

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

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

`RIGHT`

edit

Synopsis:

RIGHT(
    string_exp, 
    count)

Input:

	string expression
	integer expression

Output: string

Description:Returns the rightmost count characters of string_exp.

SELECT RIGHT('Elastic',3);

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

`RTRIM`

edit

Synopsis:

RTRIM(string_exp)

Input:

string expression

Output: string

Description:Returns the characters of string_exp with trailing blanks removed.

SELECT RTRIM('Elastic   ');

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

`SPACE`

edit

Synopsis:

SPACE(count)

Input:

integer expression

Output: string

Description:Returns a character string consisting of count spaces.

SELECT SPACE(3);

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

`SUBSTRING`

edit

Synopsis:

SUBSTRING(
    source, 
    start,  
    length)

Input:

	string expression
	integer expression
	integer expression

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`

edit

Synopsis:

UCASE(string_exp)

Input:

string expression

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

« Mathematical Functions Type Conversion Functions »