Using Functions in Expressions - MapInfo_Pro - 2023

Functions take data values and perform some operation on them to produce a new value. Functions have the following form:

SomeFunction(parameters)

Most of MapInfo Pro's functions take one or two parameters. A parameter can be a column or another expression. MapInfo Pro uses the keyword "obj" or "object" with the geographic functions: Area, CentroidX, CentroidY, ObjectLen, and Perimeter. This keyword tells MapInfo Pro that it has to get values based on graphical objects in the table rather than tabular data.

Abs

Syntax: Abs(num_expr)

Action: Returns the absolute value of a numerical expression. When the expression has a value greater than zero, Abs returns that value. When the expression has a value less than zero, Abs returns a value equal to the value of the expression multiplied by negative one.

Example: Consider the following expression:

1. Abs(numA-numB)

   (returns the absolute difference between numA and numB, regardless of which is larger)

The following illustrates how Abs works:

	Abs(5) = 5
	Abs(-3) = 3
	Abs(-0.02) = 0.02

Area

Syntax: Area(obj, units)

Action: Returns the area of the object. Returns 0 if the object has no area or there is no object for the record. Arcs, text, points, lines, and polylines do not have areas. When you choose Area from a menu, the "obj" keyword is placed between the parentheses with the current unit in double-quotes. In most cases the current unit is square miles. To specify another unit, type the unit between quotes.

Examples: To select all rows for objects that have an area greater than 59 square miles:

1. Area(obj,"sq mi")>59

To calculate the population density for an object:

1. Population/Area(obj,"sq mi")

To select all rows for objects with a population density less than 250 people per unit area:

1. Population/Area(obj,"sq mi")<250

When you want the area in hectares:

1. Area(obj,"hectare")

When you want the area in square kilometers:

1. Area(obj,"sq km")

CentroidX

Syntax: CentroidX(object)

Action: Returns the x coordinate of the centroid of the object, which is the longitude value for earth maps. The centroid is usually the center of the object's minimum bounding rectangle (MBR).

The value is in decimal degrees if the coordinates are latitudes and longitudes. The value is in whatever units were specified for the table if its coordinates are not latitudes and longitudes.

Examples: To select all objects west of New York City:

1. CentroidX(obj)<-73.997890

To select all objects east of New York City:

1. CentroidX(obj)>-73.997890

CentroidY

Syntax: CentroidY(object)

Action: Returns the y centroid of the object, which is the latitude value for earth maps.

The value is in decimal degrees if the coordinates are latitudes and longitudes. The value is in whatever units were specified for the table if its coordinates are not latitudes and longitudes.

Examples: To select all objects north of New York City:

1. CentroidY(obj)>40.750450

To select all objects south of New York City:

1. CentroidY(obj)<40.750450

To select all objects northeast of New York City:

1. CentroidX(obj)>-73.997890 and CentroidY(obj)>40.750450

The first clause in the expression selects objects east of New York City while the second clause in the expression selects objects north of New York City. Because the two clauses are joined by "and" an object must satisfy both clauses to be selected.

To select all objects that are either north or east of New York City:

1. CentroidX(obj)>-73.997890 or CentroidY(obj)>40.750450

The first clause in the expression selects objects east of New York City while the second clause in the expression selects objects north of New York City. Because the two clauses are joined by "or" an object needs to satisfy only one of them in order to be selected.

Chr$

Syntax: Chr$(num_expr)

Action: Chr$ interprets the value of num_expr as a character value. It returns the character corresponding to that value. Accordingly, num_expr should be an integer between 0 and 255.

Example: You might want to have labels with information on two lines. You can use Chr$ to insert a carriage return into a label expression. The ANSI value for a carriage return is 13. Assume that you want the first line of a label to be a country name and the second line of a label to be the county population.

The following expression produces that result:

1. Proper$(County)+Chr$(13)+Population

Cos

Syntax: Cos(num_expr)

Action: The Cos function returns the cosine of the value of a numeric expression, where that expression represents an angle expressed in radians.

CurDate

Syntax: CurDate( )

Action: Returns a date value representing the current date.

Examples: To enter the current date into a column:

1. CurDate()

You might use this in conjunction with Update Column where you want to enter the current date into some column.

To select all received 30 or more days ago:

1. Received<=CurDate()-30

Day

Syntax: Day(datefield)

Action: Returns the day of the month from the date. The day is represented as an integer from one (1) to thirty-one (31).

Examples: To select all rows where the date is the first of the month:

1. Day(date)=1

To select all rows where the day is Friday and the date is not the thirteenth:

1. Weekday(date)=6 and Day(date)<>13

This expression has two clauses. The first clause uses the Weekday function to select records where the day of the week is a Friday. The second clauses uses the Day function to select records where the day of the month does not equal (<>) 13. Since the two clauses are connected by "and" both clauses must be true for a row to be selected.

Distance

Syntax: Distance(x1, y1, x2, y2, units)

x1 and x2 are East-West (Longitude) coordinates for earth maps

y1 and y2 are North-South (Latitude) coordinates for earth maps

Units is the current distance unit

Action: Calculates the distance between two points.

When you choose Distance from a menu the "obj" keyword is placed between the parentheses along with the current unit in double-quotes. In most cases the current unit is miles.

When you want distance to be in some other unit, you should type the unit in, between quotes. The units available are:

Examples: To calculate the distance between some objects and New York City, located at -73.997890 longitude and 40.750450 latitude:

1. Distance(-73.997890, 40.750450, CentroidX(obj), CentroidY(obj),"mi")

To select all objects west of New York and not more than 20 miles from it:

1. CentroidX(obj)<-73.997890 and Distance(-73.997890, 40.750450, CentroidX(obj), CentroidY(obj),"mi")<=20

This expression has two clauses. The first clause specifies that an object must be west of New York to be selected. The second clause specifies that an object must be no more than twenty miles from New York in order to be selected. Since the two clauses are connected by "and" both must be true if an object is to be selected.

Format$

Syntax: Format$ ( value, pattern)

value is a numeric expression

pattern is a string that specifies how to format the results

Action: Produces a string representation of the numeric value num_expr.

Examples: The Format$( ) function produces a specially-formatted character string that incorporates a numeric value (specified by the value parameter). The Format$( ) function can embed a variety of formatting characters (for example currency symbols such as $, %, periods, and commas) to make the result string more readable or more aesthetically pleasing. Given a numeric value such as 12345.67, your program can use the Format$( ) function to produce formatted results, such as $12,345.67.

The Format$( ) function's value parameter represents the numeric value that you want to format. The pattern parameter is a string of code characters, carefully chosen (by you) to produce a particular type of formatted result. The pattern string should include one or more special format characters, such as #, 0, %, the comma character, the period, or the semicolon character; these characters control how the results looks. The pattern string can also include one or more cosmetic characters, such as $, -, ( , or ), to make the results more attractive.

The next table summarizes the format characters.

In the next table, the left column shows sample strings that you could use as the Format$( ) function's pattern parameter. The middle column shows sample numbers, such as you might use as the value parameter. The right column shows the results that Format$( ) would return, given the parameters from the left columns.

InStr

Syntax: InStr(position, string, substring)

Action: InStr tests whether of not some string has a specific substring. MapInfo Pro searches string starting at the character position specified by position. When position is one, MapInfo Pro starts searching at the beginning of string. When it has the value 6, MapInfo Pro starts searching at the sixth character in string.

When string contains substring, InStr returns the character position where substring starts.

Example: You are preparing to geocode a file and you want to identify all the entries with Post Office boxes for addresses. That means you want all entries that have the string "box" in their address column. They may also have "P.O.", but you cannot be sure of that. Nor can you be sure of how "box" is capitalized in the entries. Here is your expression:

1. InStr(1,UCase$(ADDRESS),"BOX")>0

This expression directs MapInfo Pro to search the Address column for the string "BOX". All entries containing this string are selected.

For example, if your data looks like this:

"This is a test of the insert function."

You want to find the position of "test." The INSTR function will look like this:

INSTR(1,"This is a test of the insert function", "test")

When this statement is executed, the value 11 will be returned.

Int

Syntax: Int(num_expr)

Action: Returns the nearest integer that is less than or equal to the specified value (num_expr).

Examples: The following table shows how Int treats various values:

When you apply Int to a Logical variable type, it will produce a 1 if the Logical value is TRUE, or 0 if the Logical value is FALSE.

It is also possible to evaluate logical expressions with Int. For example:

1. Int(STATE_NAME="Maine")

returns an integer value of 1 if the State_Name column or variable contains the string "Maine", or an integer value of 0 if State_Name contains any other string value.

LCase$

Syntax: LCase$(string)

Action: Returns the lower case of the string.

Examples: Consider the following expression:

1. LCase$(CITY)

The following table shows how LCase$ converts an input string (from a column in your table) into a returned string:

Left$

Syntax: Left$(string, number)

Action: Returns a string that consists of the leftmost number of characters in string.

Examples: Consider the following expression:

1. Left$(CITY,5)

The following table shows how that expression converts an input string into a returned string:

Now consider the following expression, where Phone is a column containing telephone numbers prefixed by area codes:

1. Left$(PHONE,3)="404"

This expression selects all rows where the first three digits of the phone number are "404". Note that the Phone column contains character data and therefore the comparison value, 404, has to be in quotes so MapInfo Pro knows to treat it as a character string.

see also: Right$, Mid$

Len

Syntax: Len(string)

Action: Returns the number of characters in a string.

LTrim$

Syntax: LTrim$(string)

Action: LTrim$ removes any non-printing characters (for example spaces, TABs) from the beginning of string.

see also: RTrim$

Maximum

Syntax: Maximum(num_expr, num_expr)

Action: Maximum returns the larger of two numbers.

Example: To find the highest address number for a street segment in a StreetPro file:

1. Maximum(TOLEFT,TORIGHT)

ToLeft is the highest address number on the left side of a segment and ToRight is the highest address number of the right side of a segment.

Mid$

Syntax: Mid$(string, position, length)

string is a character expression

position is an integer indicating a starting position in string

length is an integer indicating the number of characters to extract

Action: Mid$ returns a string of a set length, starting at given position, in a specified string.

Examples: Consider the following expression:

1. Mid$(CITY,5,4)

The following table shows how that expression converts an input string into a returned string:

Minimum

Syntax: Minimum(num_expr, num_expr)

Action: Minimum returns the smaller of two numbers.

Examples: To find the lowest address number for a street segment in a StreetPro file:

1. Minimum(FROMLEFT,FROMRIGHT)

FromLeft is the highest address number on the left side of a segment and FromRight is the highest address number of the right side of a segment.

Month

Syntax: Month(datefield)

Action: Returns the month of the date as a number, 1 through 12.

Examples: To select all received in August:

1. Month(received)=8

To select all received before August:

1. Month(received)<8

To select all received in August 1990:

1. Month(received)=8 and Year(received)=1990

This expression has two clauses. The first specifies that the month must be August and the second that the year must be 1990. The clauses are connected by the AND operator and so both must be True for a row to be selected.

To select all received in August or September:

1. Month(received)=8 or Month(received)=9

This expression has two clauses. The first selects rows where the month is August and the second selects rows where the month is September. Since the clauses are connected by "or" only one must be True for a row to be selected.

To select all received in August or September:

1. Month(received)=any(8,9)

This expression achieves the same result as 92, but is formulated using the keyword "any".

To select all received in August or September of 1990:

1. Month(received)=any(8,9) and Year(received)=1990

This expression has two clauses. The first selects rows where the month is August or September and the second selects rows where the year is 1990. The clauses are connected by the AND operator and so both must be True for a row to be selected.

ObjectLen

Syntax: ObjectLen(obj, unit)

Action: Returns the length of lines and polylines. Returns a value of 0 for other objects.

When you choose ObjectLen from a menu place the "obj" keyword between the parentheses along with the current unit in double-quotes. In most cases the current unit is miles. When you want length to be in some other unit, type the unit using double quotes. The units available are:

Examples: To calculate the length of an object:

1. ObjectLen(obj, "mi")

To have the length calculated in kilometers:

1. ObjectLen(obj, "km")

To have the length calculated in nautical miles:

1. ObjectLen(obj, "nmi")

When you wanted to convert from kilometers to miles you would use a conversion factor of .621.

To select all objects more than 10 miles long:

1. ObjectLen(obj, "mi")>10

Perimeter

Syntax: Perimeter(obj, unit)

Action: Returns the perimeter of the object for regions, ellipses, rectangles, and rounded rectangles. When you choose Perimeter from a menu the "obj" keyword is placed between the parentheses along with the current unit in double-quotes. In most cases the current unit is miles.

When you want perimeter to be in some other unit, you should type the unit using double quotes. See the table above for the available units.

Examples: To calculate the perimeter of an object:

1. Perimeter(obj, "mi")

To calculate the perimeter in kilometers:

1. Perimeter(obj, "km")

To calculate the Perimeter in meters"

1. Perimeter(obj, "m")

To select all objects with a perimeter greater than 35 miles long:

1. Perimeter(obj, "mi")>35

Proper$

Syntax: Proper$(string)

Action: Returns a string that has the first letter of each word capitalized and all other letters lowercase.

Examples: Consider the following expression:

1. Proper$(CITY)

This table shows how the Proper$ function reformats a column in your table:

Right$( )

Syntax: Right$ (string_expr, num_expr)

Action: Returns part or all of a string beginning at the right end of the string.

Example:

1. Right$(CITY, 4)

The following table shows how that expression converts an input string into a returned string:

see also: Left$, Mid$

Round

Syntax: Round(number1, number2)

Action: Returns the rounded number1. number2 specifies how to round it.

Examples: Consider the following expression:

1. Round(number1,number2)

The next table gives examples. The first column contains the function arguments (number1, number2) and the second column contains the rounded result.

To select all rows where the median age is 42, you might use the following expression:

1. Round(MED_AGE, 1)=42

Depending on your data, this expression may give better results than one that compares median age directly with a target value (MED_AGE=42). The median age column in MapInfo Pro's STATES table has the median age calculated to one decimal place. When we compare those values directly with 42 MapInfo Pro would not select records where the median age is, for example, 41.7, 42.1, or 42.4. Yet we probably want those records. By rounding the median age value to a whole number, we can get those values.

RTrim$

Syntax: RTrim$(string)

Action: RTrim$ removes any white-space characters (for example, spaces, and TABs) from the end of string.

see also: LTrim$

Sin

Syntax: Sin(num_expr)

Action: The Sin function returns the sine of the value of a numeric expression, where that expression represents an angle expressed in radians.

Str$

Syntax: Str$(expr)

Action: Str$ converts a numerical expression into a string that represents it. When the numerical expression is negative the first character in the string with be a negative sign (-). The first character returned for a positive value is the first number. If the expression is an object, Str$() returns a string describing the type of object (for example, "region"). If the expression is a Logical (true/false) value, Str$ returns "T" for true or "F" for false.

When applied to a Float variable type, Str$ will produce a text string representing the floating-point number rounded to:

• Seven digits of precision (total of 7 non-zero digits, including any number of leading or trailing zeroes depending on the location of the decimal point) if the number is less than 100,000.
• The nearest hundredth (.01) if the number is greater than or equal to 10,000.

To control the number of digits of accuracy displayed in a string, use the Format$() function.

UCase$

Syntax: UCase$(string)

Action: Returns the upper case of the string.

Examples: Consider the following expression:

1. UCase$(CITY)

This table shows how it converts an input string (from a column in your table) into a returned string:

see also: Lcase$, Proper$

Val

Syntax: Val(char_expr)

Action: The Val function extracts a numerical value from a character expression. It ignores any tabs, spaces, and line feeds at the start of a string and then tries to interpret the first sequence of numeric characters as a numerical value. It stops processing the string as soon as it finds a non-numeric character.

Examples: The following table illustrates how Val extracts a numerical value from an input string:

Weekday

Syntax: Weekday(datefield)

Action: Returns the day of the week from the data. The day is given as a number between 1 and 7. 1 is Sunday and 7 is Saturday.

Examples: To select all rows where the weekday is Wednesday:

1. Weekday(date)=4

To select all rows where the weekday is Wednesday, Thursday, or Friday:

1. Weekday(date)=any(4,5,6)

The "any" keyword directs MapInfo Pro to select any row where the week day is one of the specified days.

To select all rows where the day is Friday and the month is July:

1. Weekday(date)=6 and Month(date)=7

This expression has two clauses. The first clause uses the Weekday function to select records where the day of the week is a Friday. The second clause uses the Month function to select records where the month is July. Since the two clauses are connected by the AND operator, both clauses must be True for a row to be selected.

Year

Syntax: Year(datefield)

Action: Returns the year of the date.

Examples: To find all orders received in 1990:

1. Year(date)=1990

To find all orders received in 1990 or 1991:

1. Year(date)=any(1990,1991)

To find all orders received between 1985 and 1990, including those orders received in 1985 and 1990:

1. Year(date)>=1985 and Year(date)<=1990

This expression has two clauses. The first selects all rows where the date is 1985 or greater. The second clause selects all rows where the date is 1990 or less. Both clauses must be satisfied for a row to be selected.