Deleted post
The "in" operator can test whether a substring is present in a string:
"upon" in "stars upon thars" 1
To use this on a column, we could define a helper function. While we're at it, this can also lowercase the column we're searching for a case-insensitive match:
on like needle haystack do each v in (list "%l")format haystack needle in v end end
Allowing us to write a query like so:
ex:insert Date Entry with 20210412 "Once Upon A Time" 20220709 "There once were" 20230101 "stars upon thars" end select where like["upon" Entry] from ex # +----------+--------------------+ # | Date | Entry | # +----------+--------------------+ # | 20210412 | "Once Upon A Time" | # | 20230101 | "stars upon thars" | # +----------+--------------------+
How's that?
The absence of a direct equivalent to SQL's "like" operator has plagued me for some time, so in Lil v1.40 I decided to introduce a first-class equivalent. Using this operator, we can now perform the same type of case-insensitive search (as well as quite a few handy variations) without needing a helper function:
ex:insert Date Entry with 20210412 "Once Upon A Time" 20220709 "There once were" 20230101 "stars upon thars" end select where ((list "%l")format Entry) like "*upon*" from ex # +----------+--------------------+ # | Date | Entry | # +----------+--------------------+ # | 20210412 | "Once Upon A Time" | # | 20230101 | "stars upon thars" | # +----------+--------------------+
If you prefer the previous way, it will be necessary to rename the "like[]" helper function, since "like" is now a reserved word. Sorry for any inconvenience!