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!