---
title: String functions
description: Functions for performing string manipulation. Output: integer Description: Returns the ASCII code value of the leftmost character of string_exp as an...
url: https://www.elastic.co/elastic/docs-builder/docs/3028/reference/query-languages/sql/sql-functions-string
products:
  - Elasticsearch
---

# String functions
Functions for performing string manipulation.

## `ASCII`

```sql
ASCII(string_exp) 
```

**Output**: integer
**Description**: Returns the ASCII code value of the leftmost character of `string_exp` as an integer.
```sql
SELECT ASCII('Elastic');

ASCII('Elastic')
----------------
69
```


## `BIT_LENGTH`

```sql
BIT_LENGTH(string_exp) 
```

**Output**: integer
**Description**: Returns the length in bits of the `string_exp` input expression.
```sql
SELECT BIT_LENGTH('Elastic');

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


## `CHAR`

```sql
CHAR(code) 
```

**Output**: string
**Description**: Returns the character that has the ASCII code value specified by the numeric input.
```sql
SELECT CHAR(69);

   CHAR(69)
---------------
E
```


## `CHAR_LENGTH`

```sql
CHAR_LENGTH(string_exp) 
```

**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).
```sql
SELECT CHAR_LENGTH('Elastic');

CHAR_LENGTH('Elastic')
----------------------
7
```


## `CONCAT`

```sql
CONCAT(
    string_exp1, 
    string_exp2) 
```

**Output**: string
**Description**: Returns a character string that is the result of concatenating `string_exp1` to `string_exp2`.
The resulting string cannot exceed a byte length of 1 MB.
```sql
SELECT CONCAT('Elasticsearch', ' SQL');

CONCAT('Elasticsearch', ' SQL')
-------------------------------
Elasticsearch SQL
```


## `INSERT`

```sql
INSERT(
    source,      
    start,       
    length,      
    replacement) 
```

**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`.
The resulting string cannot exceed a byte length of 1 MB.
```sql
SELECT INSERT('Elastic ', 8, 1, 'search');

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


## `LCASE`

```sql
LCASE(string_exp) 
```

**Output**: string
**Description**: Returns a string equal to that in `string_exp`, with all uppercase characters converted to lowercase.
```sql
SELECT LCASE('Elastic');

LCASE('Elastic')
----------------
elastic
```


## `LEFT`

```sql
LEFT(
    string_exp, 
    count)      
```

**Output**: string
**Description**: Returns the leftmost count characters of `string_exp`.
```sql
SELECT LEFT('Elastic',3);

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


## `LENGTH`

```sql
LENGTH(string_exp) 
```

**Output**: integer
**Description**: Returns the number of characters in `string_exp`, excluding trailing blanks.
```sql
SELECT LENGTH('Elastic   ');

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


## `LOCATE`

```sql
LOCATE(
    pattern, 
    source   
    [, start]<3>
)
```

**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`.
```sql
SELECT LOCATE('a', 'Elasticsearch');

LOCATE('a', 'Elasticsearch')
----------------------------
3
```

```sql
SELECT LOCATE('a', 'Elasticsearch', 5);

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


## `LTRIM`

```sql
LTRIM(string_exp) 
```

**Output**: string
**Description**: Returns the characters of `string_exp`, with leading blanks removed.
```sql
SELECT LTRIM('   Elastic');

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


## `OCTET_LENGTH`

```sql
OCTET_LENGTH(string_exp) 
```

**Output**: integer
**Description**: Returns the length in bytes of the `string_exp` input expression.
```sql
SELECT OCTET_LENGTH('Elastic');

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


## `POSITION`

```sql
POSITION(
    string_exp1, 
    string_exp2) 
```

**Output**: integer
**Description**: Returns the position of the `string_exp1` in `string_exp2`. The result is an exact numeric.
```sql
SELECT POSITION('Elastic', 'Elasticsearch');

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


## `REPEAT`

```sql
REPEAT(
    string_exp, 
    count)      
```

**Output**: string
**Description**: Returns a character string composed of `string_exp` repeated `count` times.
The resulting string cannot exceed a byte length of 1 MB.
```sql
SELECT REPEAT('La', 3);

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


## `REPLACE`

```sql
REPLACE(
    source,      
    pattern,     
    replacement) 
```

**Output**: string
**Description**: Search `source` for occurrences of `pattern`, and replace with `replacement`.
The resulting string cannot exceed a byte length of 1 MB.
```sql
SELECT REPLACE('Elastic','El','Fant');

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


## `RIGHT`

```sql
RIGHT(
    string_exp, 
    count)      
```

**Output**: string
**Description**: Returns the rightmost count characters of `string_exp`.
```sql
SELECT RIGHT('Elastic',3);

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


## `RTRIM`

```sql
RTRIM(string_exp) 
```

**Output**: string
**Description**: Returns the characters of `string_exp` with trailing blanks removed.
```sql
SELECT RTRIM('Elastic   ');

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


## `SPACE`

```sql
SPACE(count) 
```

**Output**: string
**Description**: Returns a character string consisting of `count` spaces.
The resulting string cannot exceed a byte length of 1 MB.
```sql
SELECT SPACE(3);

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


## `STARTS_WITH`

```sql
STARTS_WITH(
    source,   
    pattern)  
```

**Output**: boolean value
**Description**: Returns `true` if the source expression starts with the specified pattern, `false` otherwise. The matching is case sensitive.
```sql
SELECT STARTS_WITH('Elasticsearch', 'Elastic');

STARTS_WITH('Elasticsearch', 'Elastic')
--------------------------------
true
```

```sql
SELECT STARTS_WITH('Elasticsearch', 'ELASTIC');

STARTS_WITH('Elasticsearch', 'ELASTIC')
--------------------------------
false
```


## `SUBSTRING`

```sql
SUBSTRING(
    source, 
    start,  
    length) 
```

**Output**: string
**Description**: Returns a character string that is derived from `source`, beginning at the character position specified by `start` for `length` characters.
```sql
SELECT SUBSTRING('Elasticsearch', 0, 7);

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


## `TRIM`

```sql
TRIM(string_exp) 
```

**Output**: string
**Description**: Returns the characters of `string_exp`, with leading and trailing blanks removed.
```sql
SELECT TRIM('   Elastic   ') AS trimmed;

trimmed
--------------
Elastic
```


## `UCASE`

```sql
UCASE(string_exp) 
```

**Output**: string
**Description**: Returns a string equal to that of the input, with all lowercase characters converted to uppercase.
```sql
SELECT UCASE('Elastic');

UCASE('Elastic')
----------------
ELASTIC
```