Transform Templates - Numeric

This topic discusses application of the Transform pane with Tables on a numeric field in a table. See the Transform pane and Transform Templates topics for use and for links to lists of templates for other data types for tables and use of the Transform pane with other components, such as Drawings and Images.

Tables, Numeric Types

Absolute Value

Take the absolute value of the number in the source field and put it into the target field.  Absolute value leaves positive numbers unchanged and converts negative numbers into their positive equivalents.  

 

 

Preview: Put in Temp the Absolute Value of Numbers.

Add

Add the two source fields and put the result in the target field.

 

 

Preview: Add the Numbers and Fractions fields and put in Temp.

Arc Cosine

Take the arc cosine (inverse cosine) of the source field and put it into the target field.

 

Preview: Put in Temp the Arc Cosine of Small.

Arc Sine

Take the arc sine (inverse sine) of the source field and put it into the target field.

 

Preview: Put in Temp the Arc Sine of Small.

Arc Tangent

Take the arc tangent (inverse tangent) of the source field and put it into the target field.

 

Preview: Put in Temp the Arc Tangent of Numbers.

Arc Tangent of Ratio

Take the arc tangent (inverse tangent) of the ratio between the Delta Y field and the Delta X field and put into the target field.

 

 

Preview: Put in Temp the Arc Tangent of the Ratio between Numbers as the Delta Y field and Fractions as the Delta X field.

Area

Given a geom that contains an area object, place the area in square units of measure of that area object into the target field.

 

 

Preview: Put in SqMeters the Area of Geom.

Geoms which contain lines or points have no area so the result for those objects is NULL.

 

Base 10 Logarithm

Put the base 10 logarithm (also called the common logarithm) of the source field into the target field.

 

Preview: Put in Temp the Base 10 Logarithm of Numbers.

Base 2 Logarithm

Put the base 2 logarithm (also called the binary logarithm) of the source field into the target field.

 

Preview: Put in Temp the Base 2 Logarithm of Numbers.

Bessel J0

A Bessel function of the first kind: Put the result of the Bessel function J0(x) using the source field as x into the target.

 

Preview: Put in Temp the Bessel J0 function of Numbers.

Bessel J1

A Bessel function of the first kind: Put the result of the Bessel function J1(x) using the source field as x into the target.

 

Preview: Put in Temp the Bessel J1 function of Numbers.

Bessel Jn

The nth Bessel function of the first kind: Put the result of the Bessel function Jn(x) for Order n using the source field as x into the target.  Specifying 2 for Order uses a J2(x) Bessel function.  Using 0 or 1 for the Order is equivalent to using J0(x) or J1(x), respectively.

 

Preview: Put in Temp the  Bessel Jn function for Order 2 of Numbers.

Bessel Y0

A Bessel function of the second kind: Put the result of the Bessel Function Y0(x) using the source field as x into the target.

 

Preview: Put in Temp the Bessel Y0 function of Numbers.

Bessel Y1

A Bessel function of the second kind: Put the result of the Bessel Function Y1(x) using the source field as x into the target.

 

Preview: Put in Temp the Bessel Y1 function of Numbers.

Bessel Yn

The nth Bessel function of the second kind: Put the result of the Bessel Function Yn(x) for Order n using the source field as x into the target.  Specifying 2 for Order uses a Y2(x) Bessel function.  Using 0 or 1 for the Order is equivalent to using Y0(x) or Y1(x), respectively.

 

Preview: Put in Temp the  Bessel Yn function for Order 2 of Numbers.

Binary And

Perform bitwise AND operation on the source fields and puts the result into the target field.   Bitwise operators make no sense if we do not know the numeric type of the field, in these examples all unsigned 8 bit integers of uint8 type, and how those are represented as binary numbers.

 

 

Preview: Put in Temp_Uint the Binary And result using Uint_A and Uint_B.

Because the binary function operates on each bit in the uint8 types in the table, only when all eight bits of the value match does the AND provide a non-zero result for each bit.  In this case that happens only in the second record.

Binary Not

Performs bitwise NOT operation on the source fields and puts the result into the target field. This is a binary inversion: all 0’s become 1’s and all 1’s become 0’s.

 

Preview: Put in Temp_Uint the Binary Not result using Uint_A.

Inverting all binary bits for the uint8 value 0 would result in 255.  Inverting each representation as a binary in effect counts down by 1 in the result as Uint_A counts up by 1.

Binary Or

Performs bitwise OR operation on the source fields and puts the result into the target field.

 

Preview: Put in Temp_Uint the Binary Or result using Uint_A and Uint_B.

The binary representation of 2 in a uint8 match exactly for all bits so the result is 2 as well.  For all cases where the value of Uint_B is 0 the result is simply whatever is in Uint_A.  In the one case where the two fields are different, the fourth record, the OR operation results in a bitwise addition, adding the single binary "on" bit for the value of 1 to the binary pattern for 4 to get a result of 5.

Binary Xor

Performs bitwise XOR operation on the source fields and puts the result into the target field.

 

Preview: Put in Temp_Uint the Binary Xor result using Uint_A and Uint_B.

The Boolean XOR function generates a FALSE when both inputs are TRUE.   The result in a bitwise comparison is a FALSE, or 0 when all bits in both source fields are identical.  That only happens in our example in the second record where all of the bits are the same when the two values are both the same, 2, thus creating a result of 0.  In all of the other cases where the two values are different the result is similar to that of the Binary Or operator.

Ceiling

Put the rounded up integer value of the source field into the target field.

 

Preview: Put in Temp the Ceiling value of Fractions.

Ceiling up to Decimals

Put the value of the source field rounded up to the specified number of Decimal positions into the target field.

 

Preview: Put in Temp the Ceiling up to Decimals value of Fractions with Decimals set to 2.

Compare Order

Compare the source field to the comparison field and put a 1, 0, or -1 into the target field, with 1 if the source is greater than the comparison, 0 if the  same and -1 if the source is less than the comparison.

 

Preview: Compare Order with Numbers the source and Fractions the comparison field.  Some values are greater, some less.

Preview: Compare Order with Numbers the source and Numbers the comparison field. All values compare the same since it is the same field.

Complementary Error Function

Take the complementary error function (also called erfc(z) ) of the source field and put it into the target field.

 

Preview: Put in Temp the Complementary Error Function of Numbers.

Copy

Copy the source field into the target field.  Choose Expression in the pull down menu for the Value to enter a literal value or to use an SQL expression.

 

 

Preview: Copy Numbers into Temp.

 

To populate a field with the same, literal numeric value, we choose Expression from the pull down menu and then enter the number.

 

 

Preview: Copy 3.141592 into Temp.  We choose Expression from the pull down menu and then enter the desired number, no quotes around the number necessary.

Better rounding:  3.141593 would be better rounding if we wanted pi.    

 

 

Manifold even has a constant called PI we can use in SQL expressions, as seen above.

 

 

Much better!

 

Cosine

Take the cosine of the source field and put it into the target field.

 

Preview: Put in Temp the Cosine of Numbers.

The value of the source field is taken to be in radians as units of measure.

Cube Root

Take the cube root of the source field and put it into the target field.

 

Preview: Put in Temp the Cube Root of Numbers.

Date Day

Copy the day part of a datetime source field as a number into the target field.

 

 

Preview: Put in Temp the Date Day part of Calendar.

Date Hour

Copy the hour part of a datetime source field as a number into the target field.

 

 

Preview: Put in Temp the Date Hour part of Calendar.

Date Millisecond

Copy the millisecond part of a datetime source field as a number into the target field.

 

Preview: Put in Temp the Date Millisecond part of Calendar.

Date Minute

Copy the minute part of a datetime source field as a number into the target field.

 

Preview: Put in Temp the Date Minute part of Calendar.

Date Month

Copy the month part of a datetime source field as a number into the target field.

 

Preview: Put in Temp the Date Month part of Calendar.

Date Second

Copy the seconds part of a datetime source field as a number into the target field.

 

Preview: Put in Temp the Date Second part of Calendar.

Date Year

Copy the year part of a datetime source field as a number into the target field.

 

Preview: Put in Temp the Date Year part of Calendar.

Distance

Given two geom fields, geom 1 and geom 2, return the closest distance in drawing units of measure between the objects in geom 1 and the object in geom 2.     For example, if in a table that provides a list of cities and geom 1 contains a point at the center of the city while geom 2 contains a point at the center of the airport which serves that city, distance would for each record report the distance between the city and its airport.  Distance computes Cartesian distance.

 

 

Consider a table that has two geom fields as seen above, one named Geom and the other named Tgeom.  The fourth record has been selected so the record appears in red selection color.

 

 

We can see objects in the window above, where Objects Drawing layer takes geometry from the Geom field and the Temp Objects Drawing layer takes geometry from the Tgeom field.  The Geom field contains geometry for our sample drawing that has three areas, two lines and two points.   The Tgeom field contains geometry for points which in each record are positioned below and to the left of the objects in the Geom field.   The window shows the two selected objects that correspond to the selected record in the table.  Points in the Temp Objects Drawing layer are symbolized as small green triangles, rotated by 30 degrees so they point at the object which is in the Geom field of the same record.

 

 

We apply the Distance template to compute, for each row, the distance from the Geom object to the Tgeom object, putting the result in the Dist field.

 

The preview shows the closest distance between the pairs of objects in each record.  For example, the closest distance between the two selected objects is 17.18... meters.  We know the unit of measure is in meters because the projection used for the sample drawing is Pseudo Mercator, which uses meters as a unit of measure.

 

Looking at the map window and reading the Dist value from the table we can see that the closest distance between the two objects for the selected record is just slightly over 17 meters.

 

Divide

Divide the source field by the divide field and put the result into the target field.

 

 

Preview: Divide the Numbers field by Series and put the result in Temp.

Divide and Truncate

Divide the source field by the divide field and put the integer part of the result into the target field.

 

Preview: Divide and Truncate the Numbers field by Series and put the result in Temp.

Error Function

Take the error function (also called erf(z) ) of the source field and put it into the target field.

 

Preview: Put in Temp the Error Function of Numbers.

Exponent

The exponential function: Take e to the power of the source field and place result into the target field. For anything but small numbers in the source field such exponentiation very rapidly results in numbers larger than can be represented in most types and thus stores a NULL in the target.  

 

Preview: Put in Temp the Exponent of Series.

Floor

Put the rounded down integer value of the source field into the target field.

 

Preview: Put in Temp the Floor value of Fractions.

Floor down to Decimals

Put the value of the source field rounded up to the specified number of Decimal positions into the target field.

 

Preview: Put in Temp the Floor down to Decimals value of Fractions with Decimals set to 2.

Fractional Part

Take only the decimal fraction portion of the source and put into the target field.

 

Preview: Put in Temp the Fractional Part of Fractions.

Fractional Part from Decimals

Take only the decimal fraction portion of the source beyond the specified number of Decimal positions and put into the target field.

 

Preview: Put in Temp the Fractional Part from Decimals of Fractions with Decimals set to 2.

Gamma Function

Put the gamma function for the source field into the target field.

 

Preview: Put in Temp the Gamma Function of Series.

Hyperbolic Arc Cosine

Put the hyperbolic arc cosine of the source field into the target field.

 

Preview: Put in Temp the Hyperbolic Arc Cosine of Series.

Hyperbolic Arc Sine

Put the hyperbolic arc sine of the source field into the target field.

 

Preview: Put in Temp the Hyperbolic Arc Sine of Series.

Hyperbolic Arc Tangent

Put the hyperbolic arc tangent of the source field into the target field.

 

Preview: Put in Temp the Hyperbolic Arc Tangent of Small.

Hyperbolic Cosine

Put the hyperbolic cosine of the source field into the target field.

 

Preview: Put in Temp the Hyperbolic Cosine of Series.

Hyperbolic Sine

Put the hyperbolic sine of the source field into the target field.

 

Preview: Put in Temp the Hyperbolic Sine of Series.

Hyperbolic Tangent

Put the hyperbolic tangent of the source field into the target field.

 

Preview: Put in Temp the Hyperbolic Tangent of Series.

Hypotenuse

The great and ancient Pythagorean theorem: The square root of the sum of the squares of the two sides of a right triangle.  Save into the target the value of the hypotenuse taking two given source fields as Delta X and Delta Y.

 

 

 

Preview: Put in Temp the Hypotenuse taking Numbers as Delta X and Fractions as Delta Y.

 

To use a numeric literal as a parameter, we first choose Value from the pull down list and then enter the number.

 

 

Preview: Put in Temp the Hypotenuse taking the number 3 as Delta X and 4 as Delta Y.

 

Yes, a right angle triangle with sides of 3 and 4 has a hypotenuse of length 5.   3, 4, 5 is easy to remember as a Pythagorean triple, an example of the Pythagorean Theorem.

 

JSON Number

A means to extract numbers that are embedded into JSON strings as the numeric value for a named JSON entity.  Scan the source field to find the specified name and if it exists and has a numeric value put the number into the target field.

 

 

Preview: Find the JSON Number for the name FalseEasting within the field CoordSys and put it into Temp.  No quotes are used around the FalseEasting string used as the Name.

 

We can extract eccentricity or other numbers as well:

 

 

Preview: Find the JSON Number for the name Eccentricity within the field CoordSys and put it into Temp.  No quotes are used around the Eccentricity string used as the Name.

The example table above has a field called CoordSys that contains JSON strings which specify various coordinate systems.   The JSON strings are long, the one for the first record above being:

 

{ "Name": "Argentina Campo Inchauspe Faja 3", "System": "Transverse Mercator", "CenterLat": -90, "CenterLon": -66, "FalseEasting": 3500000, "Axes": "XYH", "Base": "Campo Inchauspe (Argentina)", "MajorAxis": 6378388, "Eccentricity": 0.08199188997902977, "Transform": "Molodensky-Badekas", "CenterX": -148, "CenterY": 136, "CenterZ": 90, "Unit": "Meter", "UnitScale": 1, "UnitShort": "m" }

 

Some of the named entities have numeric values and some have string values.  Using the JSON Number template we can extract numeric values for any of the named entities.   In the first example there is no FalseEasting name in the JSON string for the third record so the result is NULL.  In the second example all of the records have Eccentricity names in their JSON strings.

Length

Given a geom that contains a line object or area object, place the length of that line object into the target field.  The length of an area object is the length of its boundary, that is, the perimeter of the area.  The length reported for a branched object is the sum of the lengths of the branches.

 

We open the table to our sample drawing, which has three areas, two lines and two points.

 

 

Preview: Put in Length the Length of Geom.

The boundary of the first area is not quite 316 meters in length.  We know the units of measure are meters because the drawing is in Pseudo Mercator projection, which is meter-based.    The two points have NULL lengths since points are dimensionless.   The use of the name Length for the field could be whatever we like and is not required to be the same as the template's name.   In this case it is a useful reminder of what values the field contains.

 

Limit

Limit the result at both the low end and also the high end, to no less than the limiting value in the At least field and also to no more than the limiting value in the At most field.  Save the Value field into the result,  unless that is below the At least field, in which case the At least field value is saved into the result, or unless that exceeds the At most field, in which case the At most field value is saved into the result.  Another way of looking at this is to take the Value field if it falls between the At least and At most fields, inclusive.   Yet another way is to consider simultaneously the At least field as the floor and the At most field the ceiling for what is taken from the Value field.

 

 

Preview: Put in Temp the Numbers value, so long as it is at least the Fractions value and at most the value 10000.  If it is greater, use 10000, and if it is lesser, use the Fractions value.

 

  See the Know Your Limits video.

Limit High

Limit the result at the high end, to no more than the limiting value in the At most field.  Save the Value field into the result, unless that exceeds the At most field, in which case the At most field value is saved into the result.  Another way of looking at this is to take the minimum of the Value field or the At most field.  Yet another way is to consider the At most field the ceiling for what is taken from the Value field.

 

 

Preview: Put in Temp the Numbers value, so long as it is at most the Fractions value.  If it is greater, use the Fractions value.

  See the Know Your Limits video.

Limit Low

Limit the result at the low end, to no less than the limiting value in the At least field.  Save the Value field into the result, unless that is below the At least field, in which case the At least field value is saved into the result.  Another way of looking at this is to take the maximum of the Value field or the At least field.  Yet another way is to consider the At lease field the floor for what is taken from the Value field.

 

 

Preview: Put in Temp the Numbers value, so long as it is at least the Fractions value.  If it is lesser, use the Fractions value.

  See the Know Your Limits video.

Log-gamma Function

The log-gamma function takes the natural logarithm of the gamma function for the source field and stores the result into the target field.

 

Preview: Put in Temp the Log gamma Function of Small.

Logarithm

Put the base e logarithm (also called the natural logarithm) of the source field into the target field.

 

Preview: Put in Temp the Logarithm of Numbers.

Modulo

The remainder (of a congruence) function: Take what is left over by the integral division of the source field by the given Divide by value and place into the target field.

 

 

Preview: Put in Temp the Modulo of Numbers using 10 as the Divide by value.

In the above we can see that 1474 can be integrally divided by 10, that is evenly divided, 147 times, leaving a remainder of 4.

Multiply

Multiply the two source fields and place the result into the target field.

 

 

Preview: Multiply the Numbers field with Fractions and put the result in Temp.

 

To Multiply by a literal number, choose Value in the pull down menu and then enter the desired number.

 

 

Preview: Multiply the Numbers field with 2 and put the result in Temp.  No quotes are used around the number 2.

Negate

Multiply the source field by -1 and place the result into the target field.

 

Preview: Negate the Numbers field and put the result in Temp.

Number of Branches

Given a geom field report the number of branches for the object the geom contains.

 

Preview: Count the Number of Branches in Geom and put the result in Temp.

The drawing shows what visually appear to be three areas and three lines, but the table contains only two records.  The first record is for a multibranched area that has three branches, and the second record is for a multibranched line that also has three branches.   The template thus reports 3 as the number of branches for both records.

Number of Characters

Count the number of characters in a source text field and put the result into the (numeric) target field.

 

Preview: Count the Number of Characters in Comment and put the result in Temp.

Number of Coordinates

Given a geom field report the number of coordinates which define the object the geom contains.  

Preview: Count the Number of Coordinates in Geom and put the result in Temp.

A rectangular area object contains 5 coordinates because the last coordinate is the same as the first coordinate, to close the area.

Number of Regexp Matches

Given a text source field, count the number of times the given regular expression Pattern matches within the source field and put that number into the numeric target field.  A value of 0 indicates no matches.

 

The regular expression Pattern of '.' matches any character.  Given a string value of 'Jason' the pattern will match 5 times within that string, matching each of the five characters that occur in that string.  The number 5 will be put into the target field.   Given a string value of 'Mississippi' and a regular expression Pattern of 'i' the pattern will match 4 times within that string and the number 4 will be put into the target field.

 

 

Preview: Put into Temp the Number of Regexp Matches using Country and a regexp pattern of e. No quotes are used around the e regexp string.

The example counts the number of occurrences of the letter e, which occurs just once in Switzerland, France and Germany but twice in Netherlands and zero times in Italy.

Parse Base64 String

Convert a number encoded as a base64 string into a number and write that number into the target field.  

Parse Hex String

Convert a number encoded as a hexadecimal string into a number and write that number into the target field.  

Power

Compute the source field to the power of the specified Power value and put the result into the target field. A Power of 2 is the source field squared while a Power of 3 is the source field cubed.

 

 

Preview: Take the Power of Numbers using a Power of 3 and put the result in Temp.

Power of 10

Compute 10 to the power of the source field and put the result into the target field.  If the source field has values 1, 2, 3,... the result of Power of 10 will be 10, 100, 1000,....   For anything but small numbers in the source field such exponentiation very rapidly results in numbers larger than can be represented in most types and thus stores a NULL in the target.  

 

 

Preview: Put in Temp the Power of 10 of Series.

Power of 2

Compute 2 to the power of the source field and put the result into the target field.  If the source field has values 1, 2, 3,... the result of Power of 2 will be 2, 4, 8,....   For anything but small numbers in the source field such exponentiation very rapidly results in numbers larger than can be represented in most types and thus stores a NULL in the target.  

 

 

Preview: Put in Temp the Power of 2 of Series.

Reciprocal Cube Root

Also known as the inverse cube root: Take 1 divided by the cube root of the source field and put the result into the target field.

 

 

Preview: Put in Temp the Reciprocal Cube Root of Numbers.

Reciprocal Square Root

Also known as the inverse square root: Take 1 divided by the square root of the source field and put the result into the target field.

 

Preview: Put in Temp the Reciprocal Square Root of Numbers.

Regexp Match Position

Given a text source field, determine the first character position within the text that the given regular expression Pattern matches put the number into the numeric target field. A value of -1 indicates no match.

 

The regular expression Pattern '.' matches any character.  Given a string value of 'Jason' the pattern will match the very first character, that is, the 0 position (we are using a counting system that begins with zero, for 0, 1, 2,...).  The number 0 will be put into the target field.  Using a regular expression Pattern of 'a' will match the second character, an a character, that is in the 1 position so the number 1 will be put into the target field.  

 

 

Preview: Put into Temp the Regexp Match Position using Country and a regexp pattern of e. No quotes are used around the e regexp string.

The regexp pattern e matches any occurrence of the letter e, which occurs just once in Switzerland, France and Germany but twice in Netherlands.  In the first record the first e occurs in position 1, the second character position when counting 0, 1, 2, ...  but in the second record the first e occurs in position 5, that is, the sixth character position.  The letter e does not occur in Italy so the template reports a -1 value.

Regexp Match Position, Nth

Given a text source field and a regular expression Pattern and an Occurrence number, n, (zero based counting, with the first Occurrence being 0, the second being 1 and so on), report the number of the character position in the text where the Pattern matches the nth time. A value of -1 indicates no match or no match for the given Occurence position.

 

Given a string value of 'Mississippi' and a regular expression Pattern of 'i' the pattern will match 4 times within that string.  If the Occurrence number is 2, meaning the third match, that third match occurs on character position 7 (also zero based counting, so that is the eighth character in the string).  The number 7 will be put into the target field.

 

 

Preview: Put into Temp the Regexp Match Position, Nth using Country, a regexp pattern of e and an Occurrence of 1. No quotes are used around the e regexp string.

The regexp pattern of e matches any occurrence of the letter e.  An Occurrence of 1 means the second occurrence when counting 0, 1, 2, ...   The parameters ask at which character position is located the second occurrence of the letter e.  For the countries of Switzerland, France, Italy and Germany there is no second occurrence of the letter e so the result -1 is put into Temp.   In the case of Netherlands the second occurrence of the letter e is located at the fifth character position, so (counting from 0) the result 4 is put into Temp.

Round

Round a source field with a fractional part up or down to the nearest integer and put the result into the target field.

 

Preview: Round the Fractions field and put the result into Temp.

Round to Decimals

Round a source field with a fractional part up or down to the number of decimals specified by  Decimals and put the result into the target field.

 

Preview: Round to Decimals the Fractions field using a Decimals value of 2 and put the result into Temp.

Sign

For a source field that is positive or negative put a 1 or -1 respectively into the target field.

 

Preview: Determine Sign of the Numbers field and put the result into Temp.

Sine

Take the sine of the source field and put it into the target field.

 

Preview: Put in Temp the Sine of Numbers.

Size in Bytes

Take the size in bytes of the value in the source field and put into the target field.  This will be the same number for numeric source fields (for example, 8 for float64 types) and will be a varying number for fields such as varchar fields.

 

Preview: Count the Size in Bytes of Comment and put the result in Temp.

Square Root

Take the square root of the source field and put it into the target field.

 

Preview: Put in Temp the Square Root of Numbers.

Subtract

Subtract one source field from the other and put the result into the target field.

 

Preview: Subtract the Fractions field value from Numbers and put in Temp.

Tangent

Take the tangent of the source field and put it into the target field.

 

Preview: Put in Temp the Tangent of Numbers.

Text Compare Order, Intl

Compare text sort order of source field and comparison field. If the source field comes in a bigger sort order number than, that is, it appears after, the comparison put 1 in the target field.  If it sorts to a lesser sort order position, that is appears before, put -1 in the target.  If identical put a 0 in the target field.   

 

The Language choice shows the collation to be used for that language.  The default is neutral, using the default language set by Windows.   

 

Clicking the collation picker button allows us to change the collation, to choose from a list of Favorites, and to add and edit Favorite collations.  

 

See the Collations topic for details and examples.

 

 

Clicking the collation picker button and choosing More... we see the en-US collation has been selected with the Ignore accent option.

 

 

Preview: Put in Temp the Text Compare Order, Intl result of comparing Comment to Description, using US English.

In the first record a text value beginning with Dol... sorts after a text value beginning with Cet... so the result 1 is put into Temp.  In the second record a text value beginning with Dol... sorts before a text value beginning with Dou... so the result -1 is put into Temp. In the third record both fields are identical so the sort order is the same and the result 0 is put into Temp.  

 

The example shows a choice of en-US for language even though the data is obviously in French, an approximation close enough for a user manual example.  For production work we would specify fr-FR for French as used in France.  Accents are ignored in the example.

Text Position

Find the specified string in the text source field and count the character position number (beginning at 0 for the first character position) at which it is found and put that number into the target field.  If not found put -1 into the target field.

 

 

Preview: Put in Temp the Text Position number searching for village in the Description field.  No quotes are used around the village string in the Search for box.

Only one match occurs above, in the third record where village matches the contents of the Description beginning with the ninth character position, resulting in the number 8 (we are counting 0, 1, 2,...) to be put into Temp.  The other records have -1 in Temp to indicate no match.

Text Position, Intl

Same as Text Position but with optional specification of the language/collation used.

Text Position, Nth

Given a text source field, a Search for string and an Occurrence number, n, (zero based counting, with the first Occurrence being 0, the second being 1 and so on), report the number of the character position in the text where the Search for string matches the nth time.   If not found at the specified nth position put -1 into the target field.

 

Given a string value of 'Mississippi' and a Search for string of 'i' the string will match 4 times within that string.  If the Occurrence number is 2, meaning the third match, that third match occurs on character position 7 (also zero based counting, so that is the eighth character in the string).  The number 7 will be put into the target field.

 

 

Preview: Put in Temp the Text Position, Nth number searching for ou in the Description field with an Occurrence number of 0 (meaning the first match).   No quotes are used around the ou string in the Search for box.

 

 

The string ou matches text in the Description field in the first record at the 14th character position so the number put into Temp is 13.  The string ou also matches text in Description in the second and fifth records at the second character position so the number put into Temp for those records is 1.

 

It may seem odd that French text could go all the way to 55 characters, as in the third record, before encountering the sequence ou, but in this case it does, with the full text of the Description field for that record being:

 

Près du village de Lenay, au sommet d'un coteau connu sous le nom de Champ des Pierres Folles, il y avait une allée couverte et un cromlech détruits en 1825. La Sainte Vierge venait pendant la messe de minuit se placer sur la table la plus élevée, tandis que les pierres de l'enceinte dansaient et tournaient autour d'elle.

Text Position, Nth, Intl

Same as Text Position, Nth but with optional specification of the language/collation used.

Truncate

Take the integer part of the source field, discarding any decimal fraction part, and put the result into the target field.

 

Preview: Truncate the Fractions field and put the result into Temp.

Truncate differs from Round in that with Truncate fractional parts are simply discarded while with Round the fractional part influences the result.  For example, in the second record above with Truncate the result is 4464 while with Round the result is 4465, since the presence of a fractional part greater than .5 results in a rounding to the next higher integer.

Truncate to Decimals

Take the integer part of the source field as well as any decimal fraction up to the specified number of Decimal places, discarding any decimal fraction part beyond, and put the result into the target field.

 

Preview: Truncate to Decimals the Fractions field using a Decimals value of 2 and put the result into Temp.

URL Port

Appears when the target field is a numeric data type.    Extracts that portion of a URL string that is the port number.

 

 

Consider a table with an nvarchar field called URLs that contains URL strings.  The table also has two nvarchar text fields called Scheme and Password, and an int32 numeric field called Port.

 

 

We choose Port as the target field. The URL Port transform appears in the templates list.    We choose the URL Port transform, using the URLs field as the source URL.  

 

 

The port number is extracted from the URLs and copied into the Port field. This transform uses the StringUrlPort  SQL function.

 

See related URL Scheme, URL Host, URL User, URL Password, URL Path, and URL Extra text transforms.

Vector x2 Value

Copy the specified part of a two-component vector numeric Value source field into the target field, with an Index number specifying the part to extract. The parts of a two-component vector numeric value are counted starting with zero: 0, or 1 for the first or second part respectively.   See the Vector x3 Value template for examples.

Vector x3 Value

Copy the specified part of a three-component vector numeric Value source field into the target field, with an Index number specifying the part to extract. The parts of a three-component vector numeric value are counted starting with zero: 0, 1, or 2 for the first, second, and third part respectively.

 

The examples below use a source field containing values that are triples of uint8 numbers, that is, vector numeric values with three numbers in the vector, each number of which is an unsigned, eight-bit integer.

 

 

Preview: Put in Temp the first part (Index of 0) of the field uInt8x3.  

 

 

Preview: Put in Temp the second part (Index of 1) of the field uInt8x3.  

 

 

Preview: Put in Temp the third part (Index of 2) of the field uInt8x3.

Vector x4 Value

Copy the specified part of a four-component vector numeric Value source field into the target field, with an Index number specifying the part to extract. The parts of a four-component vector numeric value are counted starting with zero:  0, 1, 2 or 3 for the first, second, third and fourth part respectively.   See the Vector x3 Value template for examples.

 

Notes

Everything Math - For a handy reference to anything in mathematics, see the Wolfram MathWorld site.   Thank you, Wolfram!

 

A guy walks into a bar and asks for 1.014 root beers.  The bartender says, “I’ll have to charge you extra, that’s a root beer float”.  So the guy says, “In that case, better make it a double.”

 

Why do programmers confuse Halloween with Christmas?  Because OCT 31 = DEC 25.

See Also

Editing Drawings

 

Transform

 

Transform Pane

 

Transform Options

 

Transform Templates

 

Transform Templates - Drawings

 

Transform Templates - Images

 

Transform Templates - Binary

 

Transform Templates - Boolean

 

Transform Templates - Datetime

 

Transform Templates - Geom

 

Transform Templates - Text

 

Transform Templates - Tile

 

Transform: Center and Centroids

 

Transform: Escape Templates