String Manipulation Functions

Details of the following string manipulation functions are available.

• LEN
• LTRIM
• REPLACE
• RTRIM
• SUB_STR
• TO_LOWER
• TO_UPPER
• TRIM
• URL_DECODE

LEN Function

LEN(<field>) returns the length of a string valued <field>.

Syntax

LEN(<eventAttribute>) - eventAttribute must be a STRING type.

LEN(Function(<eventAttribute>)) - The Function must return a STRING value.

Scope

Available for ClickHouse and Elasticsearch queries from 7.0.0 onwards.

In Elasticsearch, nesting is not supported.

In ClickHouse, these nested function categories are allowed:

• Evaluate-and-Set Function, Extraction Function, String Manipulation Functions

Example

eventType	rawEvent	LEN(rawEvent)
Event1	This is a short event	21
Event2	This is a medium event	23
Event3	This is a very very long event	30

LTRIM Function

LTRIM(<field>,<chars>) returns the string valued <field> after trimming characters in <chars> from left side. If <chars> is not specified, then only spaces and tabs are removed.

Syntax

LTRIM(<eventAttribute >,<chars>)

• <eventAttribute> must be a STRING type
• <chars> is a list of characters

LTRIM(Function(<eventAttribute>), <chars>)

• Function must return a STRING value
• <chars> is a list of characters

In Elasticsearch, nesting is not supported.

In ClickHouse, these nested function categories are allowed:

• Evaluate-and-Set Function, Extraction Function, String Manipulation Functions

Scope

Available for ClickHouse and Elasticsearch queries from 7.0.0 onwards.

Example

user	LTRIM(user)	LTRIM(user, “FORTINET\\”)
“ user1”	“user1”	“ user1”
“FORTINET\user3”	" “FORTINET\user3”	"user3”

REPLACE Function

REPLACE(<field>,<pattern>,<replaceWith>) returns a string formed by substituting string <replaceWith> for every occurrence of regex string <pattern> in string valued <field>.

Syntax

REPLACE(<eventAttribute>,<pattern>,<replaceWith>)

• <eventAttribute> must be a STRING type
• <pattern> must be a regular expression and
• <replaceWith> is a STRING type

REPLACE (Function(<eventAttribute>),<pattern>,<replaceWith>)

• Function must return a STRING value
• <pattern> must be a regular expression and
• <replaceWith> is a STRING type

In Elasticsearch, nesting is not supported.

In ClickHouse, these nested function categories are allowed:

• Evaluate-and-Set Function, Extraction Function, String Manipulation Functions

Scope

Available for ClickHouse and Elasticsearch queries from 7.0.0 onwards.

Example

user	REPLACE(user, "FORTINET\\", "")	REPLACE(user, "Administrator", "admin")
Bob	Bob	Bob
FORTINET\John	John	John
alice@fortinet.com	alice@fortinet.com	alice@fortinet.com
Administrator	Administrator	admin

RTRIM Function

RTRIM(<field>,<chars>) returns the string valued <field> after trimming characters in <chars> from the right side. If <chars> is not specified, then only spaces and tabs are removed.

Syntax

RTRIM(<eventAttribute >,<chars>)

• <eventAttribute> must be STRING type
• <chars> is a list of characters

RTRIM(Function(<eventAttribute>), <chars>)

• Function must return a STRING value
• <chars> is a list of characters

In Elasticsearch, nesting is not supported.

In ClickHouse, these nested function categories are allowed:

• Evaluate-and-Set Function, Extraction Function, String Manipulation Functions

Scope

Available for ClickHouse and Elasticsearch queries from 7.0.0 onwards.

Example

user	RTRIM(user)	RTRIM(user, “@fortinet.com”)
“user1 ”	“user1”	“user1 ”
“user2@fortinet.com”	“user2@fortinet.com”	"user2”

Restrictions

None

SUB_STR Function

SUB_STR(<field>,<start>,<num>) returns a substring of string valued <field>, starting at the index specified by <start> with the number of characters specified by <num>. If <num> is not specified then the string <field> starting at <start> to the end is returned.

Syntax

SUB_STR(<eventAttribute>,<start>[,<num>])

• <eventAttribute> must be STRING type
• <start> is an integer between 0 and the length of <eventAttribute>
• <num> is an integer and must be less than length of <eventAttribute> minus <start>

SUB_STR(Function(<eventAttribute>),<start>[,<num>])

• Function must return a STRING value
• <start> is an integer between 0 and the length of <eventAttribute>
• <num> is an integer and must be less than length of <eventAttribute> minus <start>

SUM(<eventAttribute>) or SUM (Function(<eventAttribute>))

In Elasticsearch, nesting is not supported.

In ClickHouse, these nested function categories are allowed:

• Evaluate-and-Set Function, Extraction Function, String Manipulation Functions

Scope

Available for ClickHouse and Elasticsearch queries from 7.0.0 onwards.

Example

user	SUB_STR(user,2)	SUB_STR(user,2,2)
aabbcc	bbcc	bb
123456	3456	34

TO_LOWER Function

TO_LOWER(<field>) changes the case of a string valued <field> to all lower case.

Syntax

TO_LOWER (<eventAttribute>) - eventAttribute must be a STRING type.

TO_LOWER (Function(<eventAttribute>)) - The Function must return a STRING value.

In Elasticsearch, nesting is not supported.

In ClickHouse, these nested function categories are allowed:

• Evaluate-and-Set Function, Extraction Function, String Manipulation Functions

Scope

Available for ClickHouse and Elasticsearch queries from 7.0.0 onwards.

Example

eventType	user	TO_LOWER(user)
E1	Bob	bob
E2	ACME/John	acme/john
E3	ALICE	alice

TO_UPPER Function

TO_UPPER(<field>) changes the case of a string valued <field> to all upper case.

Syntax

TO_UPPER (<eventAttribute>) - eventAttribute must be a STRING type.

TO_UPPER (Function(<eventAttribute>)) - The Function must return a STRING value.

In Elasticsearch, nesting is not supported.

In ClickHouse, these nested function categories are allowed:

• Evaluate-and-Set Function, Extraction Function, String Manipulation Functions

Scope

Available for ClickHouse and Elasticsearch queries from 7.0.0 onwards.

Example

eventType	user	TO_UPPER(user)
E1	Bob	BOB
E2	ACME/John	ACME/JOHN
E3	ALICE	ALICE

TRIM Function

TRIM(<field>,<chars>) returns the string valued <field> after trimming characters in <chars> from both sides. If <chars> is not specified, then only spaces and tabs are removed.

Syntax

TRIM(<eventAttribute >,<chars>)

• <eventAttribute> must be a STRING type.
• <chars> is a list of characters

TRIM(Function(<eventAttribute>), <chars>)

• Function must return a STRING value
• <chars> is a list of characters

In Elasticsearch, nesting is not supported.

In ClickHouse, these nested function categories are allowed:

• Evaluate-and-Set Function, Extraction Function, String Manipulation Functions

Scope

Available for ClickHouse and Elasticsearch queries from 7.0.0 onwards.

Example

user	TRIM(user)	TRIM(user, “FORTINET\\”)
“ user1”	“user1”	“ user1”
“user2 ”	"user2”	“user2 ”
“FORTINET\user3”	" “FORTINET\user3”	"user3”

URL_DECODE Function

URL_DECODE(<field>) returns a decoded URL string of event attribute <field> which represents an encoded URL.

Syntax

URL_DECODE (<eventAttribute>) - eventAttribute must be a STRING type.

URL_DECODE (Function(<eventAttribute>)) - The Function must return a STRING value.

In ClickHouse, these nested function categories are allowed:

• Evaluate-and-Set Function, Extraction Function, String Manipulation Functions

Scope

Not available for Elasticsearch.

Available for ClickHouse queries from 7.0.0 onwards.

Example

infoUrl	URL_DECODE(infoUrl)
http://127.0.0.1:8123/? query=SELECT%201%3B	http://127.0.0.1:8123/?query=SELECT 1;
http://127.0.0.1:8123/? SELECT%20%2A%20FROM%20Users%20WHERE%20custId%20%3D%20105%20OR%201%3D1	http://127.0.0.1:8123/?SELECT * FROM Users WHERE custId= 105 OR 1=1