String

Operators

string_expr1 + string_expr2

string_expr1 || string_expr2

Concatenates string_expr2 to string_expr1.

output type:STRING
1
dual | select(s, s + "! Red fox jumps over lazy dog!");
s add
0ho0 0ho0! Red fox jumps over lazy dog!

BASE16

BASE16_DECODE(string_expr)

UNHEX(string_expr)

Returns base16 decoded string_expr. Returns NULL if string_expr evaluates to NULL.

output type:BYTES

Example:

1
2
3
4
dual
| select(encoded:'48656c6c6f20576f726c6421')
| select(encoded, decoded_bytes:BASE16_DECODE(encoded))
| select(encoded, decoded_bytes, decoded_str:STRING(decoded_bytes));
encoded decoded_bytes decoded_str
48656c6c6f20576f726c6421 [72,101,108,108,111,32,87,111,114,108,100,33] Hello World!

BASE16_ENCODE(string_expr)

HEX(string_expr)

Returns base16 encoded string_expr. Returns NULL if string_expr evaluates to NULL.

output type:STRING

Example:

1
dual | select(BASE16_ENCODE('Red fox jumps over brown dog'));
base16_encode
52656420666f78206a756d7073206f7665722062726f776e20646f67

BASE16_ENCODE(bytes)

HEX(bytes)

Returns base16 encoded bytes. Returns NULL if string_expr evaluates to NULL.

output type:STRING

Example:

1
dual | select(BASE16_ENCODE(BYTES([0,1,2])));
base16_encode
000102

BASE64

BASE64_DECODE(string_expr)

UNBASE64(string_expr)

BASE64DECODE(string_expr)

Returns base64 decoded string_expr. Returns NULL if string_expr evaluates to NULL.

output type:BYTES

Example:

1
2
3
4
dual
| select(encoded:'SGVsbG8gV29ybGQh')
| select(encoded, decoded_bytes:BASE64_DECODE(encoded))
| select(encoded, decoded_bytes, decoded_str:STRING(decoded_bytes));
encoded decoded_bytes decoded_str
SGVsbG8gV29ybGQh [72,101,108,108,111,32,87,111,114,108,100,33] Hello World!

BASE64_ENCODE(string_expr)

BASE64(string_expr)

BASE64ENCODE(string_expr)

Returns base64 encoded string_expr. Returns NULL if string_expr evaluates to NULL.

output type:STRING

Example:

1
dual | select(BASE64_ENCODE('Hello World!'));
base64_encode
SGVsbG8gV29ybGQh

CONCAT

CONCAT(string_expr, … )

Concatenates expr arguments and returns resulting string. Max number of arguments is 128.

output type:STRING

Example:

1
dual | select(CONCAT('my ', '20 ', '$cents'));
concat
my 20 $cents

CHARAT

CHARAT(string, pos)

Returns character at pos in the string.

Returns NULL if string evaluates to NULL or if pos points beyond the length of string.

output type:STRING

Example:

1
dual | select(CHARAT("foo",0));
charat
f

CONTAINS

CONTAINS(string1, string2)

Returns true if string1 contains string2. Otherwise returns false.

Returns NULL if either of the arguments evaluates to NULL.

Note

Comparison is case sensitive.

output type:BOOLEAN

See also

Contains Expr

Example:

1
dual(2) | select(s, CONTAINS(s, "0"));
s contains
0ho0 true
1ho1 false

ENDS

ENDS(string_expr1, string_expr2)

Returns true if string_expr1 ends with string_expr2. Otherwise returns false.

Returns NULL if either of the arguments evaluates to NULL.

Note

Comparison is case sensitive.

output type:BOOLEAN

Example:

1
dual(2) | select(s, ENDS(s, "0"));
s contains
0ho0 true
1ho1 false

ESCAPE

ESCAPE(string_expr)

Prepends (escapes) following characters in string_expr with backslash “”: 0x8 (backspace), 0x9 (horizontal tab), 0xA (line feed), 0xC (form feed), 0xD (carriage return), 0x22 (double quote), 0x27 (single quote) and 0x5C (backslash).

output type:STRING

Example:

1
2
3
dual
| select(bytes:BYTES([0x8, 0x9, 0xA, 0xC, 0xD, 0x22, 0x27, 0x5C]))
| select(ESCAPE(STRING(bytes)));
escape
btnfr”’\

ESCAPE_JAVA

ESCAPE_JAVA(string_expr)

Escapes the characters in the string_expr using Java String rules. Deals correctly with quotes and control-chars (tab, backslash, cr, ff, etc.) So a tab becomes the characters ‘' and ‘t’.

output type:STRING

Example:

1
2
3
dual
| select(bytes:BYTES([0x8, 0x9, 0xA, 0xC, 0xD, 0x22, 0x27, 0x5C]))
| select(ESCAPE_JAVA(STRING(bytes)));
escape_java
btnfr”’\

ESCAPE_OCTAL

ESCAPE_OCTAL(string_expr)

Translates string_expr characters into backslash escaped octal Unicode values.

ESCAPE_OCTAL(bytes)

Translates BYTES type argument into backslash escaped octal Unicode values.

output type:STRING

Example:

1
dual | select(ESCAPE_OCTAL('abc123'));
escape_octal
141142143061062063

JSON

JSON(string_expr)

Parses Json string and returns TUPLE structure with extracted members

output type:TUPLE

Example:

1
dual | select(JSON('{"name":"john", "id":1}'));
json
{“name”:john,”id”:1}

INDEXOF

INDEXOF(str, substr, start_pos)

Returns the index within str of the first occurrence of the specified substr, starting at position start_pos. If start_pos is negative the search is done backward from the start_pos. If not found then -1 is returned.

The start_pos is counted from 0.

output type:INTEGER

Example:

1
dual | select(s, INDEXOF(s,"h",0));
s indexof
0ho0 1

LDIST

LDIST(string_expr1, string_expr2)

Computes Levenshtein distance between string_expr1 and string_expr2.

output type:INTEGER

Example:

1
dual | select(s1:s, s2:'0ho1') | select(s1, s2, LDIST(s1, s2));
s1 s2 ldist
0ho0 0ho1 1

LIKE

pattern LIKE string_expr

STR_LIKE(string_expr, pattern)

Returns true if pattern matches string. If the pattern does not contain percent signs, then LIKE acts as = operator. A percent character in the pattern (%) matches any sequence of zero or more characters. Note that matching is case sensitive.

output type:BOOLEAN

See also

Like Expr

Example:

1
dual | select('aHoi' LIKE '%ho%');
str_like
false

LOWER

LOWER(string)

Converts string to lowercase and returns resulting string.

output type:STRING

Example:

1
dual | select(LOWER('HeLlo WorlD'));
lower
hello world

MATCHES

MATCHES(string, regexp)

Returns true if regular expression regexp matches string. Otherwise returns false.

output type:BOOLEAN

Example:

1
dual | select(MATCHES('hello world', '[a-z ]*'));
matches
true

PARSE

PARSE(pattern, string)

Returns extracted fields of first match of the pattern in the string.

output type:
  1. in case the pattern does not contain any exported matchers then it returns BOOLEAN, indicating success or failure of parsing.
  2. in case the pattern contains only one exported matcher then it returns matched value as a single value
  3. in case the pattern contains more than one exported matcher then it returns matched values as TUPLE

Example:

1
2
3
dual
| select(strings: "1 out of 10; 2 out of 10; 3 out of 10")
| select(strings, PARSE("INT:prefix LD:string INT:suffix", strings));
strings parse
1 out of 10; 2 out of 10; 3 out of 10 {prefix=1 string=” out of ” suffix=10}

PARSE_ALL

PARSE_ALL(pattern, string)

Returns extracted fields of every possible match of the pattern in the string.

output type:
  1. in case the pattern does not contain any exported matchers then it returns BOOLEAN, indicating success or failure of parsing.
  2. in case the pattern contains only one exported matcher then it returns matched value as a single value
  3. in case the pattern contains more than one exported matcher then it returns matched values as TUPLE

Example:

1
2
3
dual
| select(strings: "1 out of 10; 2 out of 10; 3 out of 10")
| select(strings, PARSE_ALL("INT:prefix LD:string INT:suffix", strings));
strings parse_all
1 out of 10; 2 out of 10; 3 out of 10 [{prefix=1 string=’’ out of ‘’ suffix=10}, {prefix=2 string=’’ out of ‘’ suffix=10}, {prefix=3 string=’’ out of ‘’ suffix=10}]

PRINTF

PRINTF(format, args…)

Returns a formatted string using format string (based on java.util.Formatter class) and arguments.

output type:STRING

Example:

1
dual(3) | select(PRINTF("Result: %010x string: %s double: %2.2e", i, s, d*10000));
printf
Result: 0000000000 string: 0ho0 double: 0.00e+00
Result: 0000000001 string: 1ho1 double: 1.00e+04
Result: 0000000002 string: 2ho2 double: 2.00e+04

PUNCT

PUNCT(string_expr)

Returns punctuation characters contained in string_expr.

PUNCT(string_expr, count, withSpace)

Returns the first punctuation characters (amount can be specified by the count parameter) from the string (specified by the string_expr parameter) either with or without spaces (specified by the withSpace parameter).

Boolean withSpace includes space character (ASCII 0x20 hex) in search, and is printed out as underscore (ASCII 0x5F hex).

output type:STRING

Example:

1
dual | select(str:"Hi I'm home!") | select(PUNCT(str));
punct
‘!

STR_REGEXP_LIKE

STR_REGEXP_LIKE(string, regexp)

Returns true if string matches regular expression regexp.

output type:BOOLEAN

See also

Regexp Expr

Example:

1
dual | select(STR_REGEXP_LIKE('0ho0','[0-9ho]*'));
str_regexp_like
true

REPLACE

REPLACE(string, target_str, replace_str)

Replaces each substring of string that matches the target_str with the replace_str.

output type:STRING

Example:

1
dual | select(REPLACE('hello world', 'world', 'space'));
replace
hello space

REPLACEALL

REPLACEALL(string, regex_str, replace_str)

Replaces each substring of string that matches the regular expression regex_str with the replace_str.

output type:STRING

Example:

1
dual | select(REPLACEALL("aaa bbb ccc", "[a]", "x"));
replaceall
xxx bbb ccc

SH_ENTROPY

SH_ENTROPY(string_expr)

Computes Shannon entropy of string_expr

output type:DOUBLE

Example:

1
dual | select(SH_ENTROPY("Hello world!"));
sh_entropy
3.0220552088742005

SPLIT

SPLIT(string, regex)

Splits string around matches of given regular expression regexp and returns result as ARRAY of strings.

output type:ARRAY

Example:

1
dual | select(SPLIT('p1;p2;p3', ';'));
split
[p1, p2, p3]

STRLEN

STRLEN(string)

Returns length of string.

output type:INTEGER

Example:

1
dual | select(STRLEN('hello world'));
strlen
11

STARTS

STARTS(string1, string2)

Returns true if string1 starts with string2. Otherwise returns false.

Note

Comparison is case sensitive.

output type:BOOLEAN

Example:

1
dual | select(STARTS('Hello World!', 'Hell'));
starts
true

SUBSTR

SUBSTR(string, startPos)

Takes substring of string from position startPos to the end of string. If startPos is negative, then the substring is taken from the position relative to the end of string.

output type:STRING

Example:

1
dual | select(SUBSTR('hello world', 6));
substr
world

SUBSTR(string, startPos, endPos)

Takes substring of str from position startPos to position endPos. Position is counted from 0 (i.e first element is at position 0). If startPos is negative, then substring is taken from position relative to the end of string. If endPos is negative, then substring is taken until position relative to the end of string.

output type:STRING

Example:

1
dual | select(SUBSTR('my 20 $cents', 3,5));
substr
20

TRIM

TRIM(string)

Removes leading and trailing whitespaces from string.

output type:STRING

Example:

1
dual | select(str:'  hello world         ') | select(str, TRIM(str));
str trim
hello world hello world

UNESCAPE

UNESCAPE(string)

DEESCAPE(string)

Removes escaping from string.

output type:STRING

Example:

1
dual | select(UNESCAPE("\""));
unescape

URLDECODE

URLDECODE(string)

Returns urldecoded (also known as percent-encoding/decoding) string.

output type:STRING

Example:

1
dual | select(URLDECODE("Hello+world%21"));
urldecode
Hello world!

URLENCODE

URLENCODE(string)

Returns url-encoded string

output type:STRING

Example:

1
dual | select(URLENCODE("Hello world!"));
urlencode
Hello+world%21

HTMLUNESCAPE

HTMLUNESCAPE(string)
Unescapes HTML string
output type:STRING

Example:

1
dual | select(HTMLUNESCAPE("<Français>"));
htmlunescape
<Français>

UPPER

UPPER(string)

Converts string to uppercase.

output type:STRING

Example:

1
dual | select(UPPER('HeLlo WorlD'));
upper
HELLO WORLD