Query Ninja: String Manipulation with MapInfo Pro SQL - Part 1

User-added image

Be the Query Ninja: String Manipulation with MapInfo Pro SQL - Part 1
User-added imageThe 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"

User-added image

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"

User-added image

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:

User-added image

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:
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. 

User-added image

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: 
Left$(RTrim$(Address), Len(RTrim$(Address)) - 2) "Trim", 
Left$(RTrim$(Address), Len(RTrim$(Address)) - 2) + "Road" "New"

User-added image 

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. 

User-added image

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. 

User-added image

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.

User-added image
Article by Peter Horsbøll Møller, EMEA Partner Channel Enablement Specialist
When not writing articles for "The MapInfo Pro" journal, Peter helps Pitney Bowes Software customers to get the most from their software and is a prolific contributor to the LI360 Community. When not working he is an aficionado of fine whiskey and good cigars.
UPDATED:  June 28, 2017