Negative Search Criteria - a discussion

Microsoft offers a special approach for analysing strings that do not contain specified characters. The format used in string searches takes the form

[!any char]

where "any char" is either a single character or a series of alternative single characters that must not be present in the string. The usual wild cards may be used outside the square brackets.

We should emphasise immediately that the value "any char" does not represent a search string but a random group of individual characters. So if we used the search analysis *[!any char], this would mean that the string was not permitted to end in any of the following values a, c, h, n, r or a space. The fact that there are two instances of the value a within the string any char has no impact on the analysis.

Below we show an example of the use of positive and negative search criteria.

Columns B and C both employ positive search criteria.

The interpretation of the square brackets in this context is that the characters contained within them represent alternative single values and not a string pattern.

Columns D, E and F each employ negative search criteria. The criteria look the same as the positive ones above but have strikingly different interpretations.

We need to look more carefully at the meaning of the negative search criteria. Let us take a simpler case. We will look for the absence of a specified value at the end of a string.

The above diagram looks much easier to interpret. If we search for the positive of W, n or d at the end of the string we get the 1 (true) or 0 (false) values that we expect. The exact opposite (boolean reversal) of the results occur if we search for the absence of those values. Zero (0) always replaces one (1) in the table and vice versa.

When searching for for values in the middle of a string the results are more difficult to interpret. The table below shows the results of simple searches in the middle of a string.

In this case the positive and negative results do not represent the boolean opposite of one another, but it is possible to interpret this simpler version of the case.

The paragraph above highlights how difficult it is to explain in good English the mathematical implications of a double negative. The table below shows the limitations of getting a false negative result.

The answer to negative searches appears quirky but conforms to boolean logic.


  Last Updated: 24/05/2009 | © ACBA (UK) LTD, 2009