Use exact filtering on fields with string data type

When you search on fields with a String data type. The filtering engine is designed to return exact results. This includes any extra characters such as the space character or carriage returns.

Therefore, it is important to carefully design your search filters to get intended results. You get return hits only for the field data that exactly satisfies the search filter.

Example: Exact filtering

For example, a search filter of blue rock produces hits only on field contents that contain exactly blue-space character-rock. Any other fields that contain either blue or rock are not included in the filter results.

In the following data example, each field contains the words blue and rock. However, only rows 3 and 6 return results because these are the only exact matches.

Special operators

There are some special characters and operators that you can use to help define exact filters.

Sample data set

Let's set up a sample data set and examine the results we might get from different search filters that we can perform on a data set. Here is our sample data set.

Using the ? character 

First, let's look at the Shift Column and how to use the ? character.

Filter string = Shift

Let's see what happens if we put in the search string Shift. Although each field contains the word Shift, no rows are returned because no field contains this exact string. Therefore, there is no exact hit.

Filter string: Shift

Result: Rows 0

Filter string = Shift?

The ? wild character can be substituted for any single character. If we use a ? directly after the word Shift, such as Shift? there are still no results because there is no exact hit.

Filter string: Shift?

Result: Rows 0

Filter string = Shift ?

If we put a space character in between the word Shift and the ? character, however, we get a full set of results because this matches the field data exactly. We have the word shift and then a space character and then any single character. This gives us a collection of exact hits.

Filter string: Shift ?

Result: Rows 1, 2, 3, 4, 5

Using the * character

Let's look at the column, String Plain Text 1 row. As the name suggests, the field is String data type with StringType property set to Plain Type and the VisibleRow property set to 1.

Filter string = yellow

Three of these fields in this column contain the word yellow. However, if we enter the filter string yellow the only row returned is row 5, because row 5 is the only row that contains exactly the string "yellow".

Filter string: yellow

Result: Row 5

Filter string =yellow*

Let's use our * wild character. The * wild character returns any number of characters.

If we enter the filter string yellow*, rows 2 and 5 are returned. These rows are the only rows that have the string yellow followed by any other characters. Note that row 1 is not returned because row 1 has characters before the string yellow, but not after.

Filter string: yellow*

Result: Rows 2 and 5

Filter string =*yellow*

So, how can we return the rows that contain the string yellow, regardless of whether other data appears before or after? If we enter the filter string *yellow* we get each row that has yellow in it with any number of characters before or after the word yellow.

Filter string: *yellow*

Result: Rows 1, 2, and 5

Multi-line results, AND, OR operators

Let's look at the last column, String Plain Text 3 rows. Although this field is also in Plain Text, it is multi-line which means the data may contain carriage returns. A carriage return is regarded differently than a space character.

Notice how in rows 1 and 5, the word is followed by a trailing ellipses. The field contains data that is on different lines. This data is not visible because it is not in the first line on the field. If we open up one of these fields we can see that row 1, the field String Plain Text 3 rows contains red apple on two separate rows. Row 5 contains car red on two separate rows.

Filter string = red apple

If we put in the basic filter red apple, we might expect to return a result of row 1. However this filter returns no results because red and apple are on different lines. There is a carriage return character between the two words, instead of a space character.

Filter string: red apple

Result: 0

Filter string = *red*car*

Let's say we want to return every item with red and car. We could devise a filter string that includes anything before red, red, anything after red, car, and anything after car. However, the result is that we get red and car only in this order. If they appear in reverse order, it is not included in the results.

Filter string: *red*car*

Result: Row 2

Filter string = *red* and *car*

If we want to return the items with red and car in any order, a good way to do that is to put wildcard stars around each word and use the AND operator.

Filter string: *red* and *car*

Result: Row 2 and 5

Filter string = *red* or *car*

This time we want items with either red or car, but we do not require both. Put wildcard stars can be put around each word and use the OR operator.

Filter string: *red* or *car*

Result: Rows 1, 2 and 5

Related topics

• Create a data query with Filter

• Filtering rules for String values