how compactly use multiple or's within a select

user_9878036 0
Viewed 104

Say I have a table

tbl:([]a:`XXY`XXZ`XZZ`XYZ)

q)tbl
a  
---
XXY
XXZ
XZZ
XYZ

And I want to select the rows like "XX" or "ZZ"

Is there a more compact way than writing 

select from tbl where (a like "*XX*")|(a like "*ZZ*")

Ideally I'd like to store all my or options in a variable and then pass that to the select.

I've tried using functional selects, but can only get it to work for a single or and only in the below form. 

q)c:(like;`a;enlist "*XX*")
::
q)?[tbl;enlist c;0b;()]
a  
---
XXY
XXZ

Instead of setting enlist "XX" within c, could I create a table or list and then pass that into c?

Thank you

kdb
2 Answers

The most common approach would be

q)select from tbl where any a like/:("*XX*";"*ZZ*")
a
---
XXY
XXZ
XZZ
user_38956970

Extend on @terrylynch answer you can also pass the variable to the like statement. As mentioned by Terry, you can always use an adverb to extend the pattern matching or using a lambda and iterate through the list variable using adverb. In this case, using /: the straightforward way

q)tbl:([]a:`XXY`XXZ`XZZ`XYZ)
q)tbl
a
---
XXY
XXZ
XZZ
XYZ
q)pattern:("*XX*";"*ZZ*")
q)select from tbl where any a like/:pattern
a
---
XXY
XXZ
XZZ
q)select from tbl where any {x like y}[a;] each pattern
a
---
XXY
XXZ
XZZ
user_92564480
Related