Post by Jag Talon in Lil Programming Questions

Viewing post in Lil Programming Questions

Hi! I’m a bit stuck trying to figure out how to search for a substring inside a table. I’m thinking of a journal application where I have Date and Entry columns, and I was hoping to add a search function that returns rows that match a certain substring. Reading the docs, it looks like I can match the first part of the string by doing this:

select Date Entry where ("hi%m" parse Entry) from journal_list.value

But I can’t quite figure out how to match a string that doesn’t start at the beginning. 😅

Internet Janitor279 days ago (1 edit)

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?

Jag Talon279 days ago(+1)

Amazing thank you so much for the help! I’m still getting used to Lil you can do so much with so little. I’m just now seeing this: “The each loop collects together the results of each iteration of the loop and returns them” 🤯

Internet Janitor271 days ago

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!

itch.io

Viewing post in Lil Programming Questions