Back

Wildcard Searches in ACT


Examples of wildcard and series searches.

Example: you make a lookup on company and you write as criteria b[a-e]
Query will return you the lookup of all contacts with a company starting by BA, BB, BC, BD or BE

One of my favorites on Lookups is turning the "Starts With" implied operation to a "Contains" just by preceding the value with a % sign. To follow Xavier's pattern...

Example : perform Lookup on Company using value "Software"
Query will return a Lookup of Contacts with a Company starting with the string "Software" such as "Software Hut", "Software, Inc."

Example : perform Lookup on Company using value "%Software"
Query will return a Lookup of Contacts with a Company containing the string "Software" such as "Redhat Software", "Office Softwares, NA" and "Software, Inc."

*Note that for fields with multi-select Dropdowns, "Contains" is the default behavior in Lookups.

Using the single character wildcard _ would be Lookup Company value: "p_n" would return: "pan", "pen", "pinning" and so on.

You can also mix wildcards up....example:
You want a Lookup of Contacts where the Company contains the string pattern "ch_mp", such as "champ", "chimp" and "chump". You would Lookup using value "%ch_mp".

I want Contacts where users have input Social Security Numbers in User1 field - Lookup value: [0-9][0-9][0-9][-][0-9][0-9][-][0-9][0-9][0-9][0-9]

I want zip codes starting with number 8 and ending in an even number, Lookup Zip Code value: [8][0-9][0-9][0-9][02468]


================
[...] represents 1 character in this place.

Example1: you make a lookup on company and you write as criteria [ae]
Query will return you the lookup of all contacts with a company starting by A or E

Example2: you make a lookup on company and you write as criteria b[ae]
Query will return you the lookup of all contacts with a company starting by BA or BE

[...-...] represents a range of characters

Example1: you make a lookup on company and you write as criteria [a-e]
Query will return you the lookup of all contacts with a company starting by A, B, C, D or E

Example2: you make a lookup on company and you write as criteria b[ae]
Query will return you the lookup of all contacts with a company starting by BA, BB, BC, BD or BE

[^...] represents the list of characters you want to exclude

Example1: you make a lookup on company and you write as criteria [^a-e]
Query will return you the lookup of all contacts with a company that not starting by A, B, C, D or E

Example2: you make a lookup on company and you write as criteria [^ae]
Query will return you the lookup of all contacts with a company that not starting by A or E

...

In SQL, all that is called "Regular expressions". All doesn't work with Act, but you'll find a complete list of regular expressions at this url: http://technet.microsoft.com/en-us/library/ms174214.aspx
========================================================
Examples from posts by Bill Blakey (Sage) and Xavier Nyssen (ACC]



Back