In between operator in Dataiku
How do we implement following SQL Where clause using Dataiku Prepare recipe (Filter rows or cell with formula):
(table1.col1) Between "0*" And "9*" Or (table1.col1) Between "A*" And "Z*")
Can't find anything related to Between operator in dataiku.
Thanks!!!
Answers

Sarina Dataiker, Dataiku DSS Core Designer, Dataiku DSS Adv Designer, Registered Posts: 317 Dataiker
Hi @pnaik1
,From your example, my understanding is that you are looking for a match of:
col1 starts with 09 or col1 starts with AZ. Is that understanding correct?
If so, I would suggest using a "Extract with regular expression" step and selecting "Create a special found column" with the regular expression:
(^[AZ].*)(^[09].*)
Here's an example of the step:
This will provide you with an output column with the values true/false, where the true values meet the condition: starts with AZ or starts with 09. You can then filter on your found column to filter for "true" matches.
To answer your original question regarding a formula step, you can use the < and > operators within a formula to represent "in between". So if I was looking for the column 'col1' and wanted all values in between 0000 and 9999 for example, you could have a formula step that is: col1 > 0000 && col1 <= 10000. For the use case you outlined here, it seems like a regular expression would be the easiest way to evaluate though.
Let me know if you have any questions about this approach.
Thank you,
Sarina