PPL string functions
Note
To see which AWS data source integrations support this PPL function, see Functions.
CONCAT
Usage: CONCAT(str1,
str2, ...., str_9)
adds up to 9 strings together.
Argument type:
STRING, STRING, ...., STRING
Return type: STRING
Example:
os> 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', '!')` fetched rows / total rows = 1/1 +----------------------------+--------------------------------------------+ | CONCAT('hello', 'world') | CONCAT('hello ', 'whole ', 'world', '!') | |----------------------------+--------------------------------------------| | helloworld | hello whole world! | +----------------------------+--------------------------------------------+
CONCAT_WS
Usage: CONCAT_WS(sep, str1,
str2)
concatenates two or more strings using a specified
separator between them.
Argument type:
STRING, STRING, ...., STRING
Return type: STRING
Example:
os> source=people | eval `CONCAT_WS(',', 'hello', 'world')` = CONCAT_WS(',', 'hello', 'world') | fields `CONCAT_WS(',', 'hello', 'world')` fetched rows / total rows = 1/1 +------------------------------------+ | CONCAT_WS(',', 'hello', 'world') | |------------------------------------| | hello,world | +------------------------------------+
LENGTH
Usage:
length(str)
returns the length of the input string
measured in bytes.
Argument type:
STRING
Return type: INTEGER
Example:
os> source=people | eval `LENGTH('helloworld')` = LENGTH('helloworld') | fields `LENGTH('helloworld')` fetched rows / total rows = 1/1 +------------------------+ | LENGTH('helloworld') | |------------------------| | 10 | +------------------------+
LOWER
Usage:
lower(string)
converts the input string to
lowercase.
Argument type:
STRING
Return type: STRING
Example:
os> source=people | eval `LOWER('helloworld')` = LOWER('helloworld'), `LOWER('HELLOWORLD')` = LOWER('HELLOWORLD') | fields `LOWER('helloworld')`, `LOWER('HELLOWORLD')` fetched rows / total rows = 1/1 +-----------------------+-----------------------+ | LOWER('helloworld') | LOWER('HELLOWORLD') | |-----------------------+-----------------------| | helloworld | helloworld | +-----------------------+-----------------------+
LTRIM
Usage:
ltrim(str)
removes leading space characters from
the input string.
Argument type:
STRING
Return type: STRING
Example:
os> source=people | eval `LTRIM(' hello')` = LTRIM(' hello'), `LTRIM('hello ')` = LTRIM('hello ') | fields `LTRIM(' hello')`, `LTRIM('hello ')` fetched rows / total rows = 1/1 +---------------------+---------------------+ | LTRIM(' hello') | LTRIM('hello ') | |---------------------+---------------------| | hello | hello | +---------------------+---------------------+
POSITION
Usage: POSITION(substr
IN str)
returns the position of the first occurrence of
substring in string. It returns 0 if the substring is not in the string. It
returns NULL if any argument is NULL.
Argument type:
STRING, STRING
Return type INTEGER
Example:
os> 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')` fetched rows / total rows = 1/1 +-------------------------------------+---------------------------------------+ | POSITION('world' IN 'helloworld') | POSITION('invalid' IN 'helloworld') | |-------------------------------------+---------------------------------------| | 6 | 0 | +-------------------------------------+---------------------------------------+
REVERSE
Usage:
REVERSE(str)
returns the reversed string of the
input string.
Argument type:
STRING
Return type: STRING
Example:
os> source=people | eval `REVERSE('abcde')` = REVERSE('abcde') | fields `REVERSE('abcde')` fetched rows / total rows = 1/1 +--------------------+ | REVERSE('abcde') | |--------------------| | edcba | +--------------------+
RIGHT
Usage: right(str,
len)
returns the rightmost characters from the input string.
It returns 0 if the substring is not in the string. It returns NULL if any
argument is NULL.
Argument type:
STRING, INTEGER
Return type: STRING
Example:
os> source=people | eval `RIGHT('helloworld', 5)` = RIGHT('helloworld', 5), `RIGHT('HELLOWORLD', 0)` = RIGHT('HELLOWORLD', 0) | fields `RIGHT('helloworld', 5)`, `RIGHT('HELLOWORLD', 0)` fetched rows / total rows = 1/1 +--------------------------+--------------------------+ | RIGHT('helloworld', 5) | RIGHT('HELLOWORLD', 0) | |--------------------------+--------------------------| | world | | +--------------------------+--------------------------+
RTRIM
Usage:
rtrim(str)
trims trailing space characters from the
input string.
Argument type:
STRING
Return type: STRING
Example:
os> source=people | eval `RTRIM(' hello')` = RTRIM(' hello'), `RTRIM('hello ')` = RTRIM('hello ') | fields `RTRIM(' hello')`, `RTRIM('hello ')` fetched rows / total rows = 1/1 +---------------------+---------------------+ | RTRIM(' hello') | RTRIM('hello ') | |---------------------+---------------------| | hello | hello | +---------------------+---------------------+
SUBSTRING
Usage: substring(str,
start)
or substring(str, start, length)
returns a substring of the input string. With no length specified, it
returns the entire string from the start position.
Argument type:
STRING, INTEGER, INTEGER
Return type: STRING
Example:
os> 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)` fetched rows / total rows = 1/1 +------------------------------+---------------------------------+ | SUBSTRING('helloworld', 5) | SUBSTRING('helloworld', 5, 3) | |------------------------------+---------------------------------| | oworld | owo | +------------------------------+---------------------------------+
TRIM
Usage: trim(string)
removes leading and trailing whitespace from the input string.
Argument type:
STRING
Return type: STRING
Example:
os> source=people | eval `TRIM(' hello')` = TRIM(' hello'), `TRIM('hello ')` = TRIM('hello ') | fields `TRIM(' hello')`, `TRIM('hello ')` fetched rows / total rows = 1/1 +--------------------+--------------------+ | TRIM(' hello') | TRIM('hello ') | |--------------------+--------------------| | hello | hello | +--------------------+--------------------+
UPPER
Usage:
upper(string)
converts the input string to
uppercase.
Argument type:
STRING
Return type: STRING
Example:
os> source=people | eval `UPPER('helloworld')` = UPPER('helloworld'), `UPPER('HELLOWORLD')` = UPPER('HELLOWORLD') | fields `UPPER('helloworld')`, `UPPER('HELLOWORLD')` fetched rows / total rows = 1/1 +-----------------------+-----------------------+ | UPPER('helloworld') | UPPER('HELLOWORLD') | |-----------------------+-----------------------| | HELLOWORLD | HELLOWORLD | +-----------------------+-----------------------+