In this post I will be talking about the usage of LIKE operator in Microsoft Access Database queries. If you have to perform searches in the text strings in any particular field in your Access database then you can use the LIKE operator in its several forms. LIKE operator can be used with the wildcards to make changes in the way it searches the given search string.
Given below are simple steps for creating a search query in Microsoft Access using the LIKE operator:
If you simply use the LIKE operator then you need to provide the complete search string with it and only then the search will happen. Example if you write LIKE “Apple” for field FirstName then it will only search for records that have “Apple” as the FirstName.
If you want to find all the Names that begin with “A” then you need to modify your LIKE operator by usage of the STAR (*) wildcard. In this case your search string will look like this LIKE “A*”. This will ensure that only those records will be added to the results which have their First Names starting with letter A.
To run a more precise query you can have more letters added to it. For example if you run LIKE “AP*” then only those records will be displayed which have their First Names starting with “AP” and none other.
We can further play with the insertion of wildcards and changing the position of the wildcard will alter the results altogether. For example if we write LIKE “*E” then it will result only those records which have their First Names ending with letter E.
Till now we have discussed the usage of wildcards for finding records which have a specific letter(s) in the beginning or end of the text. Now we will see the wildcard usage for finding records which have some specific set of letter(s) in the beginning and in the end, and also we will see how to fond records which have a set of letter(s) somewhere in the middle.
To find records which have letter A in the beginning and letter E at the end we need to write the following query Like “A*E”. This will search for all the records which have anything in the middle but A in the starting and E at the end.
Another important usage is to find records which have a text patter in the middle somewhere. For example if we wish to find the records which have letter Z in them then we need to use the wildcards this way LIKE “*Z*”. This will ensure that only those records are displayed as the results which have the letter Z in them.
If we extend the step7 to more than one letter then we can search for more results. Like if we wish to search for records which have “PPL” somewhere in them then use the LIKE as follows LIKE “*PPL*”.