elasticsearch/docs/reference/sql/functions/string.asciidoc

[role="xpack"]
[testenv="basic"]
[[sql-functions-string]]
=== String Functions

Functions for performing string manipulation.

[[sql-functions-string-ascii]]
==== `ASCII`

.Synopsis:
[source, sql]
--------------------------------------------------
ASCII(string_exp) <1>
--------------------------------------------------

*Input*:

<1> string expression. If `null`, the function returns `null`.

*Output*: integer

*Description*: Returns the ASCII code value of the leftmost character of `string_exp` as an integer.

[source, sql]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[stringAscii]
--------------------------------------------------

[[sql-functions-string-bit-length]]
==== `BIT_LENGTH`

.Synopsis:
[source, sql]
--------------------------------------------------
BIT_LENGTH(string_exp) <1>
--------------------------------------------------
*Input*:

<1> string expression. If `null`, the function returns `null`.

*Output*: integer

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

[source, sql]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[stringBitLength]
--------------------------------------------------

[[sql-functions-string-char]]
==== `CHAR`

.Synopsis:
[source, sql]
--------------------------------------------------
CHAR(code) <1>
--------------------------------------------------
*Input*:

<1> integer expression between `0` and `255`. If `null`, negative, or greater
than `255`, the function returns `null`.

*Output*: string

*Description*: Returns the character that has the ASCII code value specified by the numeric input.

[source, sql]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[stringChar]
--------------------------------------------------

[[sql-functions-string-char-length]]
==== `CHAR_LENGTH`

.Synopsis:
[source, sql]
--------------------------------------------------
CHAR_LENGTH(string_exp) <1>
--------------------------------------------------
*Input*:

<1> string expression. If `null`, the function returns `null`.

*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).

[source, sql]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[stringCharLength]
--------------------------------------------------

[[sql-functions-string-concat]]
==== `CONCAT`

.Synopsis:
[source, sql]
--------------------------------------------------
CONCAT(
    string_exp1, <1>
    string_exp2) <2>
--------------------------------------------------
*Input*:

<1> string expression. Treats `null` as an empty string.
<2> string expression. Treats `null` as an empty string.

*Output*: string

*Description*: Returns a character string that is the result of concatenating `string_exp1` to `string_exp2`.

[source, sql]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[stringConcat]
--------------------------------------------------

[[sql-functions-string-insert]]
==== `INSERT`

.Synopsis:
[source, sql]
--------------------------------------------------
INSERT(
    source,      <1>
    start,       <2>
    length,      <3>
    replacement) <4>
--------------------------------------------------
*Input*:

<1> string expression. If `null`, the function returns `null`.
<2> integer expression. If `null`, the function returns `null`.
<3> integer expression. If `null`, the function returns `null`.
<4> string expression. If `null`, the function returns `null`.

*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`.

[source, sql]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[stringInsert]
--------------------------------------------------

[[sql-functions-string-lcase]]
==== `LCASE`

.Synopsis:
[source, sql]
--------------------------------------------------
LCASE(string_exp) <1>
--------------------------------------------------
*Input*:

<1> string expression. If `null`, the function returns `null`.

*Output*: string

*Description*: Returns a string equal to that in `string_exp`, with all uppercase characters converted to lowercase.

[source, sql]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[stringLCase]
--------------------------------------------------

[[sql-functions-string-left]]
==== `LEFT`

.Synopsis:
[source, sql]
--------------------------------------------------
LEFT(
    string_exp, <1>
    count)      <2>
--------------------------------------------------
*Input*:

<1> string expression. If `null`, the function returns `null`.
<2> integer expression. If `null`, the function returns `null`. If `0` or
negative, the function returns an empty string.

*Output*: string

*Description*: Returns the leftmost count characters of `string_exp`.

[source, sql]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[stringLeft]
--------------------------------------------------

[[sql-functions-string-length]]
==== `LENGTH`

.Synopsis:
[source, sql]
--------------------------------------------------
LENGTH(string_exp) <1>
--------------------------------------------------
*Input*:

<1> string expression. If `null`, the function returns `null`.

*Output*: integer

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

[source, sql]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[stringLength]
--------------------------------------------------

[[sql-functions-string-locate]]
==== `LOCATE`

.Synopsis:
[source, sql]
--------------------------------------------------
LOCATE(
    pattern, <1>
    source   <2>
    [, start]<3>
)
--------------------------------------------------
*Input*:

<1> string expression.  If `null`, the function returns `null`.
<2> string expression.  If `null`, the function returns `null`.
<3> integer expression; optional. If `null`, `0`, `1`, negative, or not
specified, the search starts at the first character position.

*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`.

[source, sql]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[stringLocateWoStart]
--------------------------------------------------

[source, sql]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[stringLocateWithStart]
--------------------------------------------------

[[sql-functions-string-ltrim]]
==== `LTRIM`

.Synopsis:
[source, sql]
--------------------------------------------------
LTRIM(string_exp) <1>
--------------------------------------------------
*Input*:

<1> string expression. If `null`, the function returns `null`.

*Output*: string

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

[source, sql]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[stringLTrim]
--------------------------------------------------

[[sql-functions-string-octet-length]]
==== `OCTET_LENGTH`

.Synopsis:
[source, sql]
--------------------------------------------------
OCTET_LENGTH(string_exp) <1>
--------------------------------------------------
*Input*:

<1> string expression. If `null`, the function returns `null`.

*Output*: integer

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

[source, sql]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[stringOctetLength]
--------------------------------------------------

[[sql-functions-string-position]]
==== `POSITION`

.Synopsis:
[source, sql]
--------------------------------------------------
POSITION(
    string_exp1, <1>
    string_exp2) <2>
--------------------------------------------------
*Input*:

<1> string expression. If `null`, the function returns `null`.
<2> string expression. If `null`, the function returns `null`.

*Output*: integer

*Description*: Returns the position of the `string_exp1` in `string_exp2`. The result is an exact numeric.

[source, sql]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[stringPosition]
--------------------------------------------------

[[sql-functions-string-repeat]]
==== `REPEAT`

.Synopsis:
[source, sql]
--------------------------------------------------
REPEAT(
    string_exp, <1>
    count)      <2>
--------------------------------------------------
*Input*:

<1> string expression. If `null`, the function returns `null`.
<2> integer expression. If `0`, negative, or `null`, the function returns `null`.

*Output*: string

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

[source, sql]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[stringRepeat]
--------------------------------------------------

[[sql-functions-string-replace]]
==== `REPLACE`

.Synopsis:
[source, sql]
--------------------------------------------------
REPLACE(
    source,      <1>
    pattern,     <2>
    replacement) <3>
--------------------------------------------------
*Input*:

<1> string expression. If `null`, the function returns `null`.
<2> string expression. If `null`, the function returns `null`.
<3> string expression. If `null`, the function returns `null`.

*Output*: string

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

[source, sql]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[stringReplace]
--------------------------------------------------

[[sql-functions-string-right]]
==== `RIGHT`

.Synopsis:
[source, sql]
--------------------------------------------------
RIGHT(
    string_exp, <1>
    count)      <2>
--------------------------------------------------
*Input*:

<1> string expression. If `null`, the function returns `null`.
<2> integer expression. If `null`, the function returns `null`. If `0` or
negative, the function returns an empty string.

*Output*: string

*Description*: Returns the rightmost count characters of `string_exp`.

[source, sql]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[stringRight]
--------------------------------------------------

[[sql-functions-string-rtrim]]
==== `RTRIM`

.Synopsis:
[source, sql]
--------------------------------------------------
RTRIM(string_exp) <1>
--------------------------------------------------
*Input*:

<1> string expression. If `null`, the function returns `null`.

*Output*: string

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

[source, sql]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[stringRTrim]
--------------------------------------------------

[[sql-functions-string-space]]
==== `SPACE`

.Synopsis:
[source, sql]
--------------------------------------------------
SPACE(count) <1>
--------------------------------------------------
*Input*:

<1> integer expression. If `null` or negative, the function returns `null`.

*Output*: string

*Description*: Returns a character string consisting of `count` spaces.

[source, sql]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[stringSpace]
--------------------------------------------------

[[sql-functions-string-startswith]]
==== `STARTS_WITH`

.Synopsis:
[source, sql]
--------------------------------------------------
STARTS_WITH(
    source,   <1>
    pattern)  <2>
--------------------------------------------------
*Input*:

<1> string expression. If `null`, the function returns `null`.
<2> string expression. If `null`, the function returns `null`.

*Output*: boolean value

*Description*: Returns `true` if the source expression starts with the specified
pattern, `false` otherwise. The matching is case sensitive.

[source, sql]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[stringStartsWithTrue]
--------------------------------------------------

[source, sql]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[stringStartsWithFalse]
--------------------------------------------------

[[sql-functions-string-substring]]
==== `SUBSTRING`

.Synopsis:
[source, sql]
--------------------------------------------------
SUBSTRING(
    source, <1>
    start,  <2>
    length) <3>
--------------------------------------------------
*Input*:

<1> string expression. If `null`, the function returns `null`.
<2> integer expression. If `null`, the function returns `null`.
<3> integer expression. If `null`, the function returns `null`.

*Output*: string

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

[source, sql]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[stringSubString]
--------------------------------------------------
[[sql-functions-string-trim]]
==== `TRIM`

.Synopsis:
[source, sql]
--------------------------------------------------
TRIM(string_exp) <1>
--------------------------------------------------
*Input*:

<1> string expression. If `null`, the function returns `null`.

*Output*: string

*Description*: Returns the characters of `string_exp`, with leading and trailing blanks removed.

[source, sql]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[stringTrim]
--------------------------------------------------

[[sql-functions-string-ucase]]
==== `UCASE`

.Synopsis:
[source, sql]
--------------------------------------------------
UCASE(string_exp) <1>
--------------------------------------------------
*Input*:

<1> string expression. If `null`, the function returns `null`.

*Output*: string

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

[source, sql]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[stringUCase]
--------------------------------------------------