simple scripting question for replicating SQL in()

[#1]

Hi all, just a quick quesiton, hoping someone can help, is there a way to search in expressions for multiple items as you would in SQL,

i.e when I load data I can say SELECT FIELD1,FIELD2 from SQL.db WHERE FIELD1 IN (1,2,3,4) however once you get into a document I only appear to be able to use if(Field1 =1 or Field1=2 or Field1=3 or Field1=4, Field1) is there not a way to clean this up?? I have tried using If (Field1=(1|2|3|4),FIELD1) but this also doesn't work. Any ideas?

Thanks

Ben

[#2]

You could try Index(). WHERE Index('1,2,3,4', FIELD1)>0.

Index returns the position of the second paramater in the first. So if Index()>0, then the second parameter is In the first.

Findoneof() and Substringcount() are a couple of others that work, but I usually stick with Index().

[#3]

This is good, closer than I have got before, but its not quite what I'm after, you can't use wildcards with index can you?

i.e. FIELD1 In('%1%') would pick up anything 1-22222 1-333 etc really after something with this functionality as well, unless there is a way to use wildcards with index?

[#4]

Then you may want to switch it around: Index(FIELD1, 1). That should hit on 1-222222, 1-333, 867-5309-1, etc.

If you have more than one, Index(FIELD1, 1)+Index(FIELD1, 2)>0. If 1 or 2 appears in FIELD1, then you return true.

Look into Wildmatch(). Wildmatch(FIELD1, '*1*', 'Hello*'). It returns the parameter number where it found the match or zero for no match.