The LIKE Operator*pop_dbSQLWiz_05 is one of the ways in which you can select records from a Database*pop_defDatabase using the SQL Wizardpop_dbSQLWiz_01. This operator allows you to enter a word or a phrase that must be found in the field of a record to be included in the query. However, the LIKE operator also allows you to match any single character or a range of characters from a list of characters that you supply. For example, you could find all records with the surname ‘Smith’, Smyth’ or ‘Smythe’ by using the LIKE operator. There are two special characters that you can use to indicate a single character or a range of characters in your comparison:
_ Use the underscore character (i.e. the _ symbol) to indicate a single character.
% Use the percent character (i.e. the % symbol) to indicate a range of characters
In the examples below, the Pattern column contains the text you would enter in the Value or Variable boxes in the Compare To panel*pop_dbSQLWiz_06, which is to be used in the comparison page of the SQL Wizard:
| Pattern | Meaning |
d%
The letter d followed by 0 or more characters e.g. drip, drown, dog, date.
_n
Any single letter followed by n e.g. an, in, on.
Sm_th%
Will find Smith, Smyth and Smythe as well as Smiths, Smithson, and so on.
You can also find a range of characters:
[ ] Use the square brackets (i.e. the [ ] symbols to surround the characters in the range.:
In the examples below, the Pattern column contains the text you would enter in the Value or Variable boxes in the Compare To panel which is to be used in the comparison page of the SQL Wizard:
Pattern
Meaning
[A – F]
Will find all characters from the range A to F.
[ASDF]
Will match any of the characters A, S, D or F.
Note:
You can also combine the square brackets with the underscore and percent characters.
One other thing you should be aware of when setting up your query in the SQL Wizard is finding records by date or time – there are standard formats for extracting records from date and time fields.
For Timestamps use the following pattern – e.g. 2001-12-31 12:00:00.
For Date use the following pattern – e.g. 2001-12-31.
For Time use the following pattern – 12:00:00.
For Timestamps, Date and Time, a database will most likely store the data in this sequence, so if you want to use these fields you should make sure the value you would enter in the Value or Variable boxes in the Compare To panel which is to be used in the comparison page of the SQL Wizard follows the pattern described.
Note:
To see what pattern your database uses to store date and time, we suggest you do a simply query in the Database Query action first, to test what works.
%Databases:_Creating_a_Query Creating a Query - the SQL WizardDatabases:_Creating_a_Query