|"Be the Query Ninja" - Using the "LIKE" operator in SQL Select |
| ||This is an article to describe how to use the "LIKE" operator in the SQL Select dialog box in MapInfo Pro. |
And I hope you'll click on "Like" for MapInfo Pro after reading this article!
The examples in this article have created using the Street Pro Italia dataset from Pitney Bowes. I hope you will come to visit us when you are in our beautiful Italy!
Introducing the "LIKE" operator
We can use the LIKE operator when we need to select records by searching sub- strings (portions of the text in a character column). It works with any form of MapInfo table (native or otherwise).
It is similar to using some of the string functions (such as Left$, Right$ or Mid$) for searching for a sub-string but using the Like Operator has advantages. It is possible to find records having strings that appear at the start, middle or end of a word or a sentence and you do not need to know the position. You are able to search using wildcard characters.
The two wildcard characters are the percent sign"%" and the underscore"_". The "%" is used when you want to select records matching to zero or more characters (once again, in any position in the string).
The "_" is used when you want to specify a wildcard of a single character. (And with this option you there must be a character.)
The LIKE operator is used inside the "where Condition" of the SQL Select dialog box. In the 32-bit versions of MapInfo Pro this is in the Query menu. In the 64-bit versions it is found in the Spatial, Table and Map tabs on the ribbon.
The LIKE operator appears in the Operator list on the right of the SQL Select dialog box.
Figure 1: A "Like" example in SQL Select. Selecting records (Italian Municipalities) having Name starting with "San"
Examples conditions using the "LIKE" operator
Here is a list of examples of using the LIKE operator. The Where_condition column is what would appear in the SQL Select dialog box. The column on the right shows examples that would be returned by that expression.
Figure 2: List of "LIKE" example of using.
Select Point of Interesting using the "LIKE" operator in the SQL Select where conditions.
An example just more complicated as I'd like to find the Bank named "Monte Paschi Siena" or something like this - because I don't remember exactly what is the name of the bank - into the Point Of Interests table - being the banks within the Province of Rome where I live.
Figure 3: in the POI table select Monte Paschi Siena Bank in Rome.
SQL Select in MapInfo Pro is not case sensitive so you can search for "Maria" and "MARIA" with the same results. Also, you can use "Like" or "LIKE" in the where condition dialog box.
For searching records having an "_" in the string value of a table's column, please, use "\_%".
It's useful to save the query using the Save Template to save the query then use Load Template to load the query saved.
Questions or suggestions?
If you have questions or suggestions for the Query Ninja, post them in our LinkedIn Group.
Article by Monica Di Martino, GIS/Location Intelligence Presales Engineer
When not writing articles for The MapInfo Professional, Monica is devoted to understand the current challenges of our partners and customers. Monica also reads a lot, likes to travel, to eat and can occasionally be found on Twitter.