Advance Search: Add "Contains", "Begins With" and "Ends With" to the list of choices

Please add a “Contains” option to the list of searches in the Advance Search window. The “Like” and “Not Like” options do not seem to be specific enough.

Advance%20Search%20contains

It would make searches much easier to be able to select a specific string to look for within a field. For example: a Contains search of “XXXX” in the Product Code field in the Inventory database that would return all codes that Contain that string - 123XXXX321, XXXX321123, 123321XXXX and so on.

Hi,

Although I do see the merit of “contains”, this can be done already using widcards. So if you need to get this in a hurry, the below should help.

For example you are looking for a customer which starts with an “A” but then only have 3 additional characters (like ALDI) you could put: “A???”

You can get quite funky with these clauses such as “[a-c]*” which will return all customers starting with the letters A, B or C. There are quite a few combinations to be had. Although not as user user friendly as “contains”, it does the same job and are standard querying techniques. Here is a page with all sorts of wonderful query wildcards:
https://www.w3schools.com/sql/sql_wildcards.asp
@twhitson - your example can be done using wilcards either with the “#” (if a numeric field or “?” if its alphanumeric.

Hope this helps.
Jon

1 Like

In the list of options you have EQUALS which is looking for an exact match such as:

EQUALS ALDI (using Jon’s example) would only return the record where it matches exactly

However, you also have LIKE which is looking for something similar or containing:

LIKE A* would return anything where A was the first letter, so ALDI, ALDO, ASDA, AMERICAN GOLF etc. You can enter as much of the string as you like.

You can conversely use the * at the other end to find things that end with any number of letters.

By entering an * at both ends you’re looking for a string anywhere within a field.

You can ever filter for special characters if doing some data cleanse etc (I often do this) such as * & * to find records containing an & sign or the dredded * , * which is a system developers nightmare!