You're viewing version 3.4 of the OpenSearch documentation. This version is no longer maintained. For the latest version, see the current documentation. For information about OpenSearch version maintenance, see Release Schedule and Maintenance Policy.

String functions

The following string functions are supported in PPL.

CONCAT

Usage: CONCAT(str1, str2, ...., str_9)

Concatenates up to 9 strings.

Parameters:

str1, str2, ..., str_9 (Required): Up to 9 strings to concatenate.

Return type: STRING

Example

source=people
| eval `CONCAT('hello', 'world')` = CONCAT('hello', 'world'), `CONCAT('hello ', 'whole ', 'world', '!')` = CONCAT('hello ', 'whole ', 'world', '!')
| fields `CONCAT('hello', 'world')`, `CONCAT('hello ', 'whole ', 'world', '!')`

The query returns the following results:

CONCAT(‘hello’, ‘world’)	CONCAT(‘hello ‘, ‘whole ‘, ‘world’, ‘!’)
helloworld	hello whole world!

CONCAT_WS

Usage: CONCAT_WS(sep, str1, str2)

Returns str1 concatenated with str2, using sep as a separator between them.

Parameters:

sep (Required): The separator string to place between concatenated strings.
str1 (Required): The first string to concatenate.
str2 (Required): The second string to concatenate.

Return type: STRING

Example

source=people
| eval `CONCAT_WS(',', 'hello', 'world')` = CONCAT_WS(',', 'hello', 'world')
| fields `CONCAT_WS(',', 'hello', 'world')`

The query returns the following results:

CONCAT_WS(‘,’, ‘hello’, ‘world’)
hello,world

LENGTH

Usage: length(str)

Returns the length of the string measured in bytes.

Parameters:

str (Required): The string for which to calculate the length.

Return type: INTEGER

Example

source=people
| eval `LENGTH('helloworld')` = LENGTH('helloworld')
| fields `LENGTH('helloworld')`

The query returns the following results:

LENGTH(‘helloworld’)
10

LIKE

Usage: like(string, PATTERN[, case_sensitive])

Returns TRUE if the string matches the pattern, FALSE otherwise.

Parameters:

string (Required): The string to match against the pattern.
PATTERN (Required): The pattern to match, supporting wildcards.
case_sensitive (Optional): Whether the pattern matching is case-sensitive. Default determined by plugins.ppl.syntax.legacy.preferred.

Wildcards:

% - Represents zero, one, or multiple characters.
_ - Represents a single character.

Configuration:

When plugins.ppl.syntax.legacy.preferred=true, case_sensitive defaults to false.
When plugins.ppl.syntax.legacy.preferred=false, case_sensitive defaults to true.

Return type: BOOLEAN

Example

source=people
| eval `LIKE('hello world', '_ello%')` = LIKE('hello world', '_ello%'), `LIKE('hello world', '_ELLo%', true)` = LIKE('hello world', '_ELLo%', true), `LIKE('hello world', '_ELLo%', false)` = LIKE('hello world', '_ELLo%', false)
| fields `LIKE('hello world', '_ello%')`, `LIKE('hello world', '_ELLo%', true)`, `LIKE('hello world', '_ELLo%', false)`

The query returns the following results:

LIKE(‘hello world’, ‘_ello%’)	LIKE(‘hello world’, ‘_ELLo%’, true)	LIKE(‘hello world’, ‘_ELLo%’, false)
True	False	True

Limitation: The pushdown of the LIKE function to a DSL wildcard query is supported only for keyword fields.

ILIKE

Usage: ilike(string, PATTERN)

Returns TRUE if the string matches the pattern (case-insensitive), FALSE otherwise.

Parameters:

string (Required): The string to match against the pattern.
PATTERN (Required): The case-insensitive pattern to match, supporting wildcards.

Wildcards:

% - Represents zero, one, or multiple characters.
_ - Represents a single character.

Return type: BOOLEAN

Example

source=people
| eval `ILIKE('hello world', '_ELLo%')` = ILIKE('hello world', '_ELLo%')
| fields `ILIKE('hello world', '_ELLo%')`

The query returns the following results:

ILIKE(‘hello world’, ‘_ELLo%’)
True

Limitation: The pushdown of the ILIKE function to a DSL wildcard query is supported only for keyword fields.

LOCATE

Usage: locate(substr, str[, start])

Returns the position of the first occurrence of substr in str, starting at position start. If start is not specified, the search begins at position 1. Returns 0 if substr is not found. If any argument is NULL, the function returns NULL.

Parameters:

substr (Required): The substring to search for.
str (Required): The string to search within.
start (Optional): The position to start searching from. Defaults to 1.

Return type: INTEGER

Example

source=people
| eval `LOCATE('world', 'helloworld')` = LOCATE('world', 'helloworld'), `LOCATE('invalid', 'helloworld')` = LOCATE('invalid', 'helloworld'), `LOCATE('world', 'helloworld', 6)` = LOCATE('world', 'helloworld', 6)
| fields `LOCATE('world', 'helloworld')`, `LOCATE('invalid', 'helloworld')`, `LOCATE('world', 'helloworld', 6)`

The query returns the following results:

LOCATE(‘world’, ‘helloworld’)	LOCATE(‘invalid’, ‘helloworld’)	LOCATE(‘world’, ‘helloworld’, 6)
6	0	6

LOWER

Usage: lower(string)

Converts the string to lowercase.

Parameters:

string (Required): The string to convert to lowercase.

Return type: STRING

Example

source=people
| eval `LOWER('helloworld')` = LOWER('helloworld'), `LOWER('HELLOWORLD')` = LOWER('HELLOWORLD')
| fields `LOWER('helloworld')`, `LOWER('HELLOWORLD')`

The query returns the following results:

LOWER(‘helloworld’)	LOWER(‘HELLOWORLD’)
helloworld	helloworld

LTRIM

Usage: ltrim(str)

Trims leading space characters from the string.

Parameters:

str (Required): The string from which to remove leading spaces.

Return type: STRING

Example

source=people
| eval `LTRIM('   hello')` = LTRIM('   hello'), `LTRIM('hello   ')` = LTRIM('hello   ')
| fields `LTRIM('   hello')`, `LTRIM('hello   ')`

The query returns the following results:

LTRIM(‘ hello’)	LTRIM(‘hello ‘)
hello	hello

POSITION

Usage: POSITION(substr IN str)

Returns the position of the first occurrence of substr in str. Returns 0 if substr is not found. Returns NULL if any argument is NULL.

Parameters:

substr (Required): The substring to search for.
str (Required): The string to search within.

Return type: INTEGER

Example

source=people
| eval `POSITION('world' IN 'helloworld')` = POSITION('world' IN 'helloworld'), `POSITION('invalid' IN 'helloworld')` = POSITION('invalid' IN 'helloworld')
| fields `POSITION('world' IN 'helloworld')`, `POSITION('invalid' IN 'helloworld')`

The query returns the following results:

POSITION(‘world’ IN ‘helloworld’)	POSITION(‘invalid’ IN ‘helloworld’)
6	0

REPLACE

Usage: replace(str, pattern, replacement)

Returns a string in which all occurrences of the pattern in str are replaced with the replacement string. Returns NULL if any argument is NULL.

Parameters:

str (Required): The input string to perform replacements on.
pattern (Required): The regex pattern to match (supports Java regex syntax).
replacement (Required): The replacement string.

Return type: STRING

Regular expression support: The pattern argument supports Java regex syntax.

Regular expression special characters: The pattern is interpreted as a regular expression (regex). The following characters have special meaning in regex: ., *, +, [, ], (, ), {, }, ^, $, |, ?, and \. To match these characters literally, escape them with backslashes:

example.com becomes 'example\\.com' (escaped dots).
value* becomes 'value\\*' (escaped asterisk).
price+tax becomes 'price\\+tax' (escaped plus).

Strings containing multiple special characters can be quoted using \\Q...\\E to treat the entire string literally. For example, '\\Qhttps://example.com/path?id=123\\E' treats the entire URL as a literal string.

Example: Literal string replacement

source=people
| eval `REPLACE('helloworld', 'world', 'universe')` = REPLACE('helloworld', 'world', 'universe'), `REPLACE('helloworld', 'invalid', 'universe')` = REPLACE('helloworld', 'invalid', 'universe')
| fields `REPLACE('helloworld', 'world', 'universe')`, `REPLACE('helloworld', 'invalid', 'universe')`

The query returns the following results:

REPLACE(‘helloworld’, ‘world’, ‘universe’)	REPLACE(‘helloworld’, ‘invalid’, ‘universe’)
hellouniverse	helloworld

Example: Escaping special characters

source=people
| eval `Replace domain` = REPLACE('api.example.com', 'example\\.com', 'newsite.org'), `Replace with quote` = REPLACE('https://api.example.com/v1', '\\Qhttps://api.example.com\\E', 'http://localhost:8080')
| fields `Replace domain`, `Replace with quote`

The query returns the following results:

Replace domain	Replace with quote
api.newsite.org	http://localhost:8080/v1

Example: Regex patterns

source=people
| eval `Remove digits` = REPLACE('test123', '\\d+', ''), `Collapse spaces` = REPLACE('hello  world', ' +', ' '), `Remove special` = REPLACE('hello@world!', '[^a-zA-Z]', '')
| fields `Remove digits`, `Collapse spaces`, `Remove special`

The query returns the following results:

Remove digits	Collapse spaces	Remove special
test	hello world	helloworld

Example: Capture groups and backreference

source=people
| eval `Swap date` = REPLACE('1/14/2023', '^(\\d{1,2})/(\\d{1,2})/', '$2/$1/'), `Reverse words` = REPLACE('Hello World', '(\\w+) (\\w+)', '$2 $1'), `Extract domain` = REPLACE('user@example.com', '.*@(.+)', '$1')
| fields `Swap date`, `Reverse words`, `Extract domain`

The query returns the following results:

Swap date	Reverse words	Extract domain
14/1/2023	World Hello	example.com

Example: Advanced regex

source=people
| eval `Clean phone` = REPLACE('(555) 123-4567', '[^0-9]', ''), `Remove vowels` = REPLACE('hello world', '[aeiou]', ''), `Add prefix` = REPLACE('test', '^', 'pre_')
| fields `Clean phone`, `Remove vowels`, `Add prefix`

The query returns the following results:

Clean phone	Remove vowels	Add prefix
5551234567	hll wrld	pre_test

Notes for regex patterns in PPL queries:

Backslashes must be escaped by doubling them: \\ instead of \. Examples: \\d for digit patterns, \\w+ for word characters.
Backreferences support both PCRE-style (\1, \2) and Java-style ($1, $2) syntax. PCRE-style backreferences are automatically converted to Java-style internally.

REVERSE

Usage: REVERSE(str)

Returns the reverse of the provided string.

Parameters:

str (Required): The string to reverse.

Return type: STRING

Example

source=people
| eval `REVERSE('abcde')` = REVERSE('abcde')
| fields `REVERSE('abcde')`

The query returns the following results:

REVERSE(‘abcde’)
edcba

RIGHT

Usage: right(str, len)

Returns the last len number of characters of str. Returns NULL if any argument is NULL.

Parameters:

str (Required): The input string.
len (Required): The number of characters to return from the right side.

Return type: STRING

Example

source=people
| eval `RIGHT('helloworld', 5)` = RIGHT('helloworld', 5), `RIGHT('HELLOWORLD', 0)` = RIGHT('HELLOWORLD', 0)
| fields `RIGHT('helloworld', 5)`, `RIGHT('HELLOWORLD', 0)`

The query returns the following results:

RIGHT(‘helloworld’, 5)	RIGHT(‘HELLOWORLD’, 0)
world

RTRIM

Usage: rtrim(str)

Trims trailing space characters from the string.

Parameters:

str (Required): The string from which to remove trailing spaces.

Return type: STRING

Example

source=people
| eval `RTRIM('   hello')` = RTRIM('   hello'), `RTRIM('hello   ')` = RTRIM('hello   ')
| fields `RTRIM('   hello')`, `RTRIM('hello   ')`

The query returns the following results:

RTRIM(‘ hello’)	RTRIM(‘hello ‘)
hello	hello

SUBSTRING

Usage: substring(str, start[, length])

Returns a substring of str starting at start for length characters. If length is not specified, returns the substring from start to the end of the string.

Parameters:

str (Required): The input string.
start (Required): The starting position for the substring.
length (Optional): The length of the substring. If not specified, returns from start to the end.

Return type: STRING

Synonyms: SUBSTR

Example

source=people
| eval `SUBSTRING('helloworld', 5)` = SUBSTRING('helloworld', 5), `SUBSTRING('helloworld', 5, 3)` = SUBSTRING('helloworld', 5, 3)
| fields `SUBSTRING('helloworld', 5)`, `SUBSTRING('helloworld', 5, 3)`

The query returns the following results:

SUBSTRING(‘helloworld’, 5)	SUBSTRING(‘helloworld’, 5, 3)
oworld	owo

TRIM

Usage: trim(str)

Trims leading and trailing space characters from the string.

Parameters:

str (Required): The string from which to remove leading and trailing spaces.

Return type: STRING

Example

source=people
| eval `TRIM('   hello')` = TRIM('   hello'), `TRIM('hello   ')` = TRIM('hello   ')
| fields `TRIM('   hello')`, `TRIM('hello   ')`

The query returns the following results:

TRIM(‘ hello’)	TRIM(‘hello ‘)
hello	hello

UPPER

Usage: upper(string)

Converts the string to uppercase.

Parameters:

string (Required): The string to convert to uppercase.

Return type: STRING

Example

source=people
| eval `UPPER('helloworld')` = UPPER('helloworld'), `UPPER('HELLOWORLD')` = UPPER('HELLOWORLD')
| fields `UPPER('helloworld')`, `UPPER('HELLOWORLD')`

The query returns the following results:

UPPER(‘helloworld’)	UPPER(‘HELLOWORLD’)
HELLOWORLD	HELLOWORLD

REGEXP_REPLACE

Usage: regexp_replace(str, pattern, replacement)

Replaces all substrings in str that match pattern with replacement and returns the resulting string.

Parameters:

str (Required): The input string to perform replacements on.
pattern (Required): The regular expression pattern to match.
replacement (Required): The replacement string.

Return type: STRING

Synonyms: REPLACE

Example

source=people
| eval `DOMAIN` = REGEXP_REPLACE('https://opensearch.org/downloads/', '^https?://(?:www\.)?([^/]+)/.*$', '\1')
| fields `DOMAIN`

The query returns the following results:

DOMAIN
opensearch.org

WAS THIS PAGE HELPFUL?

✔ Yes ✖ No

Tell us why

350 characters left

Have a question? Ask us on the OpenSearch forum.

Want to contribute? Edit this page or create an issue.