Pitney Bowes
MapInfo Pro™ Monthly Journal
Be the Query Ninja: String Manipulation with MapInfo Pro SQL - Part 1

The information in this article pertains to both the 32 and 64 bit versions of MapInfo Pro.

From time to time, you may get some data from other parties that might not be as clean as you could want it to be. You might have to erase certain parts of a string, you might have to split one string into several or maybe you need to replace certain words with different words.

Luckily, the MapBasic language comes with a number of functions that can be used for searching thru strings and extracting parts from strings.

These MapBasic functions can also be used when querying tables and when updating table.

Here is a list of some of the MapBasic string functions:
  • Find a sub-string: InStr( )
  • Extract part of a string: Left$( ), Right$( ), Mid$( )
  • Upper/Lower case: UCase$( ), LCase$( ), Proper$( )
  • Trim blanks from a string: LTrim$( ), RTrim$( )
  • Determine string length: Len( )
  • Repeat a string sequence: Space$( ), String$( )
Query statements

Let have a look at some practical query statement to see how you can use these functions in an SQL statement.

In many cases, you might want to find the records that meat a certain condition. Maybe the addresses of your data have been entered with abbreviations and you want to change these to the full words, for example changing "12 High St" to "12 High Street", "234 Main Rd" to "234 Main Road".

First you need to locate the records that are using the abbreviation. We can use a condition like this:

Right$(RTrim$(Address), 2) = "Rd"

Right 2 chars from Address

The Right$( ) function extracts a number of characters from the right of the text. In the example here, we extract the two characters to the right.

If you are uncertain about how clean the data is, you can use RTrim$() to remove potential trailing spaces from the address:

Right$(RTrim$(Address), 2) = "Rd"

Right 2 chars from RTrimmed Address

The RTrim$( ) function removes any potential spaces at the end of the string. There is a similar function for removing spaces at the beginning of the string - it is called LTrim$( ).

Here is another technique:

SQL Removing Rd from address

Using one the two methods above, we can find the addresses that ends with the text "Rd".

Now let us remove the "Rd" from the string and compare that with the original address to validate it looks fine. To do so, we will add some expressions to the Select Columns field:

Address,
Left$(RTrim$(Address), Len(RTrim$(Address)) - 2) "Trim"


The Left$( ) function works similar to the Right$( ) function. It just reads from the beginning of the string.

The Len( ) function is used to "measure" the length of the string - the number of characters in the string.

I have also included the RTrim$( ) function in the expression to remove potential trailing spaces from address.

In the browser window below, you can see the original addresses on the left and the trimmed versions on the right.

Comparing original to trimmed

We can now also add "Road" to the string and see if that looks fine, too. We just have to add another expression to the Select Columns field:

Address,
Left$(RTrim$(Address), Len(RTrim$(Address)) - 2) "Trim",
Left$(RTrim$(Address), Len(RTrim$(Address)) - 2) + "Road" "New"


Adding Road to the trimmed address

Here we add - or concatenate - the string "Road" onto the trimmed version of the address. We keep the other two columns or expressions as well. This makes it easier to visually check if the result looks correct.

Comparing original to trimmed to new

When you have verified that the New address looks fine, you can update the original address column with the value from the new address column using Update Column.

Update address with new address

Be careful, that you select the latest query and not the base table when updating.

You can also use the expression that trims the address and adds "Road" to the address directly in the Update Column dialog. This can be used directly on the first or second query where we select the records that needs to be updated.

Where do I find more information?

You can find more details about these and other string functions in the MapBasic Reference Guide:
http://www.pbinsight.com/support/product-documentation/details/mapinfo-mapbasic

Article by Peter Horsbøll Møller, EMEA Partner Channel Enablement Specialist
When not writing articles for "The MapInfo Professional" journal, Peter helps Pitney Bowes Software customers to get the most from their software and is a prolific contributor to the mapinfo-l Google Group. When not working he is an aficionado of fine whiskey and good cigars.