Filter Rows Using Pattern Matching

Question

Given the following table:

t:([] sym:`mkt`abc`nex`xyz`mno`def`nva`ghi;
      qty:100 200 150 300 250 175 125 225)

Result:

sym qty
-------
mkt 100
abc 200
nex 150
xyz 300
mno 250
def 175
nva 125
ghi 225

Select all rows where the sym column begins with either "m" or "n".

Expected result:

sym qty
-------
mkt 100
nex 150
mno 250
nva 125

Solution

Method 1

select from t where any sym like/: ("m*";"n*")

Result:

sym qty
-------
mkt 100
nex 150
mno 250
nva 125

Method 2

select from t where sym like "[mn]*"

Method 3

select from t where (sym like "m*") or (sym like "n*")

Explanation

The like operator performs pattern matching on strings and symbols.

For example:

`mkt like "m*"

Result:

1b

while:

`abc like "m*"

Result:

0b

The wildcard:

*

matches any sequence of characters.

Therefore:

sym like "m*"

matches symbols beginning with "m".

Method 1: Multiple Patterns

The expression:

sym like/: ("m*";"n*")

tests each symbol against both patterns.

For example:

`mkt like/: ("m*";"n*")

Result:

10b

Applying:

any

returns true if either pattern matches:

any `mkt like/: ("m*";"n*")

Result:

1b

This allows multiple patterns to be combined without writing explicit OR conditions.

Method 2: Character Classes

Pattern matching supports character classes:

"[mn]*"

which means:

First character is either m or n
Followed by zero or more characters

Therefore:

sym like "[mn]*"

matches symbols beginning with either letter.

This is often the most concise solution.

Method 3: Explicit Conditions

The same logic can be written using separate conditions:

(sym like "m*") or (sym like "n*")

While easy to read, this becomes less convenient when many patterns need to be checked.

Summary

Three common approaches are:

select from t where any sym like/: ("m*";"n*")

select from t where sym like "[mn]*"

select from t where (sym like "m*") or (sym like "n*")

All produce the same result. The character-class form:

sym like "[mn]*"

is usually the most concise when matching a small set of starting characters.