Advanced Database – Use wildcards in a query
We have already used the asterisk wildcard * in queries to display all fields.
The other most common wildcard is ‘?’ which was covered in module 1 and replaces exactly one character.
For instance if we run a query to find Car Reg No with the Criterion LIKE ‘RS?’ it will return any car reg number that begins with RS and has one more character after it.
The wildcard * can be useful when to find any data that contains a specific character.
Create a query and add the car and owner details table. We are going to show all that have a first name with the letter ‘h’ in it.
In the first column select all fields using the * wildcard, in the second column select first name field and type LIKE ‘*h*’ in the criterion box.
Press F5 to run the query.
The query displays the two records that have the letter ‘h’ in the middle of the name, but ‘Hans’ doesn’t display as it is at the beginning.
To make sure that the query searches for the initial ‘H’ too, we’ll adapt the criterion.
Press F4 to close the query table.
In the OR box below the criterion, type LIKE ‘H*’
Now the query will return First Names that begin with H as well as contain h within them.
Press F5 to run the query and notice that Hans is now included in the query table.
Press F4 to close the table and switch the view into SQL mode.
For some reason, Base displays wildcards differently in SQL than in the design view.
The * wildcard displays as % and the ? Displays as _
The other wildcards [ ], !, -, # are only used in MS Access, and there are no equivalents in Libreoffice Base.
