|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 been 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 type 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 there must be a character.)
The LIKE operator is typically used in the "where Condition" field 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 on 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 the Discussion are of the Query Ninja in the LI360 Community.
Article by Monica Di Martino, GIS/Location Intelligence Presales Engineer
When not writing articles for The MapInfo Pro, 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.
Check out these interesting articles from the series of Query Ninja on Li360.
Query Ninja: Sub-selecting Part 5: Using the Buffer() function in a sub-select
Query Ninja: Sub-selecting Part 6: Selecting objects that intersect another object
Query Ninja: Using a Sub-select to select all the objects within a region
Query Ninja: A sub-selecting technique for selecting records from one table that are not in another
If you still not a member, please register to Li360 with this link to stay updated with latest in Location Intelligence world.