# Miscellaneous operators

Use these functions in the **[Advanced Search](https://docs.kloudfuse.com/platform/4.0.2/signals/logs/advanced-search/)** interface to perform relatively complex data transformations. The **Logs Query Builder** does not natively support these functions.

### backshift
Shifts a column in a table down by the specified number.

**Syntax**  
```none
| backshift <field>
| backshift <field> as <alias>
| backshift <field>, <number>
| backshift <field>, <number> as <alias>
```

### base64Decode
Converts a base64 string into to an ASCII/UTF-8 string.

**Syntax**  
```none
| base64Decode(<base64String>) as <alias>
```

**Example**  
```none
| base64Decode("SGVsbG8gV29ybGQ=") as K
```

### base64Encode
Converts an ASCII/UTF-8 string to a base64 string.

**Syntax**  
```none
| base64Encode(<string>) as <alias>
```

**Example**  
```none
| base64Encode("Hello World") as K
```

### compareCIDRPrefix
Checks if the network prefixes of two IPv4 addresses match.

**Syntax**  
```none
| compareCIDRPrefix(<ipv4String>, <ipv4String>) as <alias>
```

**Examples**  
```none
| compareCIDRPrefix("10.10.1.35", "10.10.1.35", 24) as K
| compareCIDRPrefix("10.10.1.35", "11.11.2.35", 24) as K
```

### concat
Concatenates multiple strings and numbers into a new string.

**Syntax**  
```none
| concat(<field1>, <field2>, ...) as <alias>
```

**Example**  
```none
| concat("Hello", " ", "World", " ", 1) as K
```

### decToHex
Converts long value to hexadecimal value.

**Syntax**  
```none
| decToHex(<longField>) as <alias>
```

**Example**  
```none
| decToHex("70") as K
```

### dedup
Removes duplicate results in a table by a set of columns.

**Syntax**  
```none
| dedup by <field1>, <field2>, ...
| dedup by <number> <field1>, <field2>, ...
```

### format
Returns a formatted string given a format specifier and arguments.

**Syntax**  
```none
| format(<formatSpecifierString>, <field1>, ...) as <alias>
```

**Example**  
```none
| format("%s : %s", "The count is", 1) as K
```

### formatDate
Returns a date string given a date, format, and timezone (default UTC).

**Syntax**  
```none
| formatDate(<dateMilliseconds>) as <alias>
| formatDate(<dateMilliseconds>, <formatString>) as <alias>
| formatDate(<dateMilliseconds>, <formatString>, <timezoneString>) as <alias>
```

**Examples**  
```none
| formatDate(1730925217838) as K
| formatDate(1730925217838, "YYYY-MM-dd") as K
```

### getCIDRPrefix
Retrieves the network prefix from an IPv4 address.

**Syntax**  
```none
| getCIDRPrefix(<ipv4String>) as <alias>
```

**Example**  
```none
| getCIDRPrefix("10.10.1.35") as K
```

### hash
Hashes data into a string value using the specified hash algorithm.

**Syntax**  
```none
| hash(<field>) as <alias>
| hash(<field>, <hashAlgorithm>) as <alias>
```

**Examples**  
```none
| hash("hello world") as K
| hash("hello world", "sha1") as K
```

### hexToAscii
Converts hexadecimal string to ASCII value.

**Syntax**  
```none
| hexToAscii(<hexString>) as <alias>
```

**Example**  
```none
| hexToAscii("48656c6c6f20476f7068657221") as K
```

### hexToDec
Converts hexadecimal string to a long value.

**Syntax**  
```none
| hexToDec(<hexString>) as <alias>
```

**Example**  
```none
| hexToDec("0000000000001337") as K
```

### in
Checks if a field is in a set of string values or number values.

**Syntax**  
```none
| where <field> in (<value_1>[, <value_2>, <value_3>, ...])
| if(<field> in (<value_1>[, <value_2>, <value_3>, ...]), <value_if_true>, <value_if_false>) as <field>
```

### ipv4ToNumber
Converts an Internet Protocol version 4 (IPv4) IP address from the octet dot-decimal format to a decimal format.

**Syntax**  
```none
| ipv4ToNumber(<ipv4String>) as <alias>
```

**Example**  
```none
| ipv4ToNumber("10.163.3.0") as K
```

### isBlank
Checks if a string value is null, empty, or contains only empty characters.

**Syntax**  
```none
| isBlank(<string>) as <alias>
```

**Example**  
```none
| isBlank("  ") as K
```

### isEmpty
Checks if a string value is an empty string that contains no characters or whitespace.

**Syntax**  
```none
| isEmpty(<string>) as <alias>
```

**Example**  
```none
| isEmpty(" a ") as K
```

### isNumeric
Checks if a string value can be parsed as a number.

**Syntax**  
```none
| isNumeric(<string>) as <alias>
```

**Example**  
```none
| isNumeric("1.234") as K
```

### isPrivateIP
Checks if an IPv4 address is private.

**Syntax**  
```none
| isPrivateIP(<ipv4String>) as <alias>
```

**Example**  
```none
| isPrivateIP("192.168.0.1") as K
```

### isPublicIP
Checks if an IPv4 address is public.

**Syntax**  
```none
| isPublicIP(<ipv4String>) as <alias>
```

**Example**  
```none
| isPrivateIP("192.168.0.1") as K
```

### isValidIP
Checks if an IPv4 or IPv6 address is valid.

**Syntax**  
```none
| isValidIP(<ipString>) as <alias>
```

**Examples**  
```none
| isValidIP("192.168.0.1") as K
| isValidIP("192.168.400.1") as K
```

### len
Returns the length of a string.

**Syntax**  
```none
| len(<string>) as <alias>
```

**Example**  
```none
| len("1234") as K
```

### luhn
Validates credit card numbers in a string value using Luhn’s algorithm.

**Syntax**  
```none
| luhn(<string>) as <alias>
```

**Examples**  
```none
| luhn("6666-7777-6666-8888") as K
| luhn("00000000000000031") as K
```

### maskFromCIDR
Returns the subnet mask given a prefix length for IPv4 addresses.

**Syntax**  
```none
| maskFromCIDR(<prefixLength>) as <alias> where <prefixLength> is 0-32
```

**Examples**  
```none
| maskFromCIDR(32) as K
| maskFromCIDR(31) as K
```

### matches
Matches strings using the RE2-compliant regex format.

**Syntax**  
```none
| where <string expression> matches "<regex>"
| if(<field> matches <regex_pattern>, <value_if_true>, <value_if_false>) as <field>
```

### now
Returns the current epoch time in milliseconds.

**Syntax**  
```none
| now() as <alias>
```

**Examples**  
```none
| now() as K
| formatDate(now(), "YYYY-MM-dd") as today
```

### queryEndTime
Returns the end time of the search in milliseconds.

**Syntax**  
```none
| queryEndTime() as <alias>
```

**Examples**  
```none
| queryEndTime() as K
```

### queryStartTime
Returns the start time of the search in milliseconds.

**Syntax**  
```none
| queryStartTime() as <alias>
```

**Examples**  
```none
| queryStartTime() as K
```

### queryTimeRange
Returns the time range for the query being executed, in milliseconds.

**Syntax**  
```none
| queryTimeRange() as <alias>
```

**Examples**  
```none
| queryTimeRange() as K
```

### replace
Replaces all occurrences of a specified string with another string.

**Syntax**  
```none
| replace(<sourceString>, <searchString>, <replaceString>) as <alias>
| replace(<sourceString>, <regexString>, <replaceString>) as <alias>
```

**Examples**  
```none
| replace("hello world", "world", "gopher") as K
| replace("http://kloudfuse.com/products/12345678/logs", "[0-9]{5,}", "") as K
```

### substring
Extracts a part of a given string and start/end offsets.

**Syntax**  
```none
| substring(<sourceString>, <startOffsert>) as <alias>
| substring(<sourceString>, <startOffsert>, <endOffset>) as <alias>
```

**Example**  
```none
| substring("hello world", 0, 5) as K
```

### toBytes
Parses a string representation of bytes (KB, MB, GB, and so on) into the number of bytes it represents.

**Syntax**  
```none
| toBytes(<storageSize>) as <alias>
```

**Examples**  
```none
| toBytes("1.5KB") as K
```

### toDuration
Parses a string representation of time (ns, µs, ms, s, m, h) to milliseconds.

**Syntax**  
```none
| toDuration(<timeString>) as <alias>
```

**Example**  
```none
| toDuration("2ms") as K
```

### toFloat
Parses a string representation of a number or a number to a float.

**Syntax**  
```none
| toFloat(<number>) as <alias>
| toFloat(<numberString>) as <alias>
```

**Examples**  
```none
| toFloat("1.25") as K
```

### toInt
Parses a string representation of a number, or a number to an `int`.

**Syntax**  
```none
| toInt(<number>) as <alias>
| toInt(<numberString>) as <alias>
```

**Example**  
```none
| toInt("1.5") as K
```

### toLowerCase
Converts all letters of a string to lowercase.

**Syntax**  
```none
| toLowerCase(<string>) as <alias>
```

**Example**  
```none
| toLowerCase("HELLO WORLD") as K
```

### toUpperCase
Converts all letters of a string to uppercase.

**Syntax**  
```none
| toUpperCase(<string>) as <alias>
```

**Example**  
```none
| toUpperCase("hello world") as K
```

### transpose
Converts aggregate query results from a long format into a wide, tabular format by pivoting row values into column headers.

**Syntax**  
```none
| transpose row <row_field1>[, <row_field2>, ...] column <column_field1>[, <column_field2>, ...]
```

**Examples**  
```none
| source="pinot-server" | timeslice 5m | count by _timeslice, status_code
| source="pinot-server" | timeslice 5m | count by _timeslice, status_code | transpose row _timeslice column status_code
| source="pinot-server" | timeslice 5m | count as request_count, avg(response_time) as avg_response by _timeslice, status_code | transpose row _timeslice column status_code
```

### trim
Removes starting and trailing empty spaces in a string.

**Syntax**  
```none
| trim(<string>) as <alias>
```

**Example**  
```none
| trim("  hello world   ") as K
```

### urlDecode
Returns an unescaped URL string.

**Syntax**  
```none
| urlDecode(<urlString>) as <alias>
```

**Example**  
```none
| urlDecode("http%3A%2F%2Fexample-server123.org%2Fapi%2Fv1%2Fdata.php%3Fauth%3DAbCdEfGhIjKlMnOpQrStUvWxYz123456%26") as K
```

### urlEncode
Encodes a URL into the ASCII character set.

**Syntax**  
```none
| urlEncode(<urlString>) as <alias>
```

**Example**  
```none
| urlEncode("http://example-server123.org/api/v1/data.php?auth=AbCdEfGhIjKlMnOpQrStUvWxYz123456&") as K
```