Syntax for use with MapInfo Pro SQL Select for string parsing and manipulating text

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

From time to time, acquiring data from other parties might not be as clean as it could be. Parts of a string may need to be erased, one string may need to be split into several or maybe  certain words need to be replaced 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 to use these functions in an SQL statement.

There may be a need to find the records that meat a certain condition. Maybe the addresses of the data have been entered with abbreviations and need to change those to the full words, for example changing "12 High St" to "12 High Street", "234 Main Rd" to "234 Main Road".

First locate the records that are using the abbreviation. 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, extract the two characters to the right.

If uncertain about how clean the data is, 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, find the addresses that ends with the text "Rd".

Now remove the "Rd" from the string and compare that with the original address to validate it looks fine. To do so, 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.

Also included is the RTrim$( ) function in the expression to remove potential trailing spaces from address.

In the browser window below, the original addresses on the left are shown and the trimmed versions on the right.
User-added image

Now also add "Road" to the string and see if that looks fine, too. 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

Add - or concatenate - the string "Road" onto the trimmed version of the address. 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 verified that the New address looks fine, update the original address column with the value from the new address column using Update Column
User-added image
e careful, to select the latest query and not the base table when updating.

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 selecting the records that need to be updated.

Find more details about these and other string functions in the MapBasic Reference Guide:

Article by Peter Horsbøll Møller, EMEA Partner Channel Enablement Specialist
UPDATED:  May 23, 2019