Skip to main content
The !in~ operator in APL filters records based on whether a value doesn’t match any element in a specified set using case-insensitive comparison. Use this operator to exclude records where a field value equals one of several values regardless of letter case, which is more concise and efficient than chaining multiple inequality checks with and. The !in~ operator works with any scalar type, including strings, numbers, booleans, datetime values, and dynamic arrays. Use the !in~ operator when you need to exclude specific values with case-insensitive matching, such as filtering out known HTTP methods or status codes regardless of their capitalization in the data.

​
For users of other query languages

If you come from other query languages, this section explains how to adjust your existing queries to achieve the same results in APL.
In Splunk SPL, string comparisons are case-insensitive by default. APL requires the explicit !in~ operator for case-insensitive exclusion. Use !in~ when you want to exclude values regardless of case.
index=web_logs | where NOT method IN ("options", "head")
In ANSI SQL, the NOT IN operator’s case sensitivity depends on the database collation. APL’s !in~ operator explicitly performs case-insensitive exclusion, similar to SQL databases with case-insensitive collation.
SELECT * FROM sample_http_logs WHERE LOWER(method) NOT IN ('options', 'head')

​
Usage

​
Syntax

Expression !in~ (Value1, Value2, ...)

​
Parameters

NameTypeRequiredDescription
ExpressionscalarYesThe value to check against the exclusion set, ignoring letter case.
Valuescalar or tabularYesThe values to exclude. Specify individual scalar values, a dynamic array, or a subquery. When using a subquery with multiple columns, APL uses the first column. The operator supports up to 1,000,000 unique values in the set.

​
Returns

Returns true if the expression value does not match any value in the specified set (case-insensitive). Returns false otherwise.

​
Use case examples

Filter HTTP logs to exclude certain methods regardless of case.Query
['sample-http-logs']
| where method !in~ ('options', 'head')
| project _time, method, uri, status
Run in PlaygroundOutput
_timemethoduristatus
2024-10-17 10:15:00GET/api/users200
2024-10-17 10:16:30POST/api/data201
2024-10-17 10:17:45DELETE/api/item204
This query filters the HTTP logs to exclude OPTIONS and HEAD requests regardless of case, helping you focus on substantive requests.

​
Performance considerations

When two operators perform the same task, use the case-sensitive one (!in) for better performance. Use !in~ only when case-insensitive exclusion is necessary.

​
Use with dynamic arrays

When you pass a dynamic array with nested arrays, APL flattens them into a single list. For instance, x !in~ (dynamic(['a', ['b', 'c']])) is equivalent to x !in~ ('a', 'b', 'c'). 
let excluded_methods = dynamic(['options', 'head', 'trace']);
['sample-http-logs']
| where method !in~ (excluded_methods)
  • in: Use for case-sensitive matching to include values.
  • !in: Use for case-sensitive exclusion. Better performance than !in~.
  • in~: Use for case-insensitive matching to include values.
  • where: Use to filter rows based on conditions. The !in~ operator is commonly used within where clauses.
  • !~: Use for single value case-insensitive inequality checks. Use !in~ when checking against multiple values.