WHERE filtering options?

[seven-of-eleven]

I read through some of the docs and worked through the tutorial. I noted that you can filter:

sq '.actor | .first_name, .last_name | where(.first_name == "TOM”)’

which successfully shows the records with TOM as first_name. This seems to require an exact match.

As example how would I search for anything that starts with “TO” or perhaps contains “TO”, to show record for TOM, TORRY, TONY, TOBIAS… I know SQL generally has a LIKE operator with % and _ options. I tried using * and % but neither worked.

So I guess my questions is if there are options to search for partial match of a string?

[seven-of-eleven]

I solved this somewhat by removing the where filter in sq and piping the sq results into jq. Then I used jq to filter the results.

sq -j '.actor | .first_name, .last_name' | jq '.[] | select(.first_name | startswith("MA”))’
sq -j '.actor | .first_name, .last_name' | jq '.[] | select(.first_name | contains("MA”))’
sq -j '.actor | .first_name, .last_name' | jq '.[] | select(.first_name | endswith("T”))’

This works, but let me know if there's a way to do this within sq.

Thanks.

[neilotoole]

Hi @seven-of-eleven,

sq doesn't have the equivalent of LIKE, or the jq string testing operators yet. I'll put it on the todo-list!

[seven-of-eleven]

Just to follow up on this when I used sql it also did work:

sq sql "SELECT "First Name","Last Name",City,Email FROM data WHERE ("Last Name" LIKE 'B%' AND "First Name" LIKE 'S%') ORDER BY "Last Name"" -C

Incase someone else runs into this issue.
Best