VisionCore
Home Search Support Forum Knowledge Base Print

Search Conditions

In the Conditions combo box on a search form you can select a certain condition to apply when searching for records.  The default will always be "Like", which should be used for all normal searching.  The different conditions allow you to customize what you are searching for in a more specific manor.  Below are the descriptions and examples of all the different search conditions available.

 

Keep in mind no matter what none of the conditions will return a null or empty searched field.  In other words if you set the condition for a Last Name field to <> (Not Equal) and enter 'SMITH', the search form will return every Last Name that is not SMITH but will not return any records where the Last Name is empty, blank or Null.

 

Example of Conditions

Condition

 Example

 Description

Like

Last Name Like Johnson
 
Last Name Like John[*]n
could return Johnson, Johnston, Jonathan

Searches for data based on a pattern search you enter to find data in the selected fields of the search form.  See the Examples using the Like Condition below for more on this powerful searching condition.

Not Like

Last Name Not Like Johnson

Same as Like except returns the opposite of what was found.

=

Last Name equals Johnson

Searches for data Equal to the entered data.

<>

Last Name does not equal Johnson

Searches for data Not Equal To the entered data.

<

Last Names Less Than Johnson

Searches for data Less Than the entered data.

<=

Last Names Less Than Or Equal To  Johnson

Searches for data Less Than Or Equal To the entered data.

>

Last Names Greater Than Johnson

Searches for data Greater Than the entered data.

>=

Last Names Greater To or Equal To Johnson

Searches for data Greater Than or Equal To the entered data.

 

Examples using the Like Condition (Pattern Matching)

Kind of match

Pattern

Matches Found

Matches Not Found

Multiple characters

a*a

aa, aBa, aBBBa

aBC


*ab*

abc, AABB, Xab

aZb, bac

Special character

a[*]a

a*a

aaa

Multiple characters

ab*

abcdefg, abc

cab, aab

Single character

a?a

aaa, a3a, aBa        

aBBBa

Single digit

a#a

a0a, a1a, a2a

aaa, a10a

Range of characters

[a-z]

f, p, j

, &

Outside a range

[!a-z]

, &, %

b, a

Not a digit

[!0-9]

A, a, &, ~

, 1, 9

Combined

a[!b-m]#

An9, az0, a99

abc, aj0

 

Pattern Matching

Built-in pattern matching provides a versatile tool for making string comparisons. The following table shows the wildcard characters you can use with the Like operator and the number of digits or strings they match.

 

Character(s)

in pattern

Matches in expression

?

Any single character

*

Zero or more characters

#

Any single digit (0 - 9)

[charlist]

Any single character in charlist

[!charlist]

Any single character not in charlist

         

You can use a group of one or more characters (charlist) enclosed in brackets ([ ]) to match any single character in expression, and charlist can include almost any characters in the ANSI character set, including digits. In fact, you can use the special characters opening bracket ([ ), question mark (?), number sign (#), and asterisk (*) to match themselves directly only if enclosed in brackets. You can't use the closing bracket ( ]) within a group to match itself, but you can use it outside a group as an individual character.

 

In addition to a simple list of characters enclosed in brackets, charlist can specify a range of characters by using a hyphen (-) to separate the upper and lower bounds of the range. For example, using [A-Z] in pattern results in a match if the corresponding character position in expression contains any of the uppercase letters in the range A through Z. You can include multiple ranges within the brackets without delimiting the ranges. For example, [a-zA-Z0-9] matches any alphanumeric character.

 

Other important rules for pattern matching:

Bullet PointAn exclamation mark (!) at the beginning of charlist means that a match is made if any character except those in charlist are found in expression. When used outside brackets, the exclamation mark matches itself.

Bullet PointYou can use the hyphen (-) either at the beginning (after an exclamation mark if one is used) or at the end of charlist to match itself. In any other location, the hyphen identifies a range of ANSI characters.

Bullet PointWhen you specify a range of characters, the characters must appear in ascending sort order (A-Z or 0-100). [A-Z] is a valid pattern, but [Z-A] isn't.

Bullet PointThe character sequence [ ] is ignored; it's considered to be a zero-length string ("").