Functions: Quick Reference

Conditional functions let you perform an action or calculation using an IF statement. For parameters and examples, go to the Conditional Functions article.

IF c THEN t ELSE f ENDIF

IF c THEN t ELSE f ENDIF: Returns t if the condition c is true, else returns f.

IF c THEN t ELSEIF c2 THEN t2 ELSE f ENDIF

IF c THEN t ELSEIF c2 THEN t2 ELSE f ENDIF: Returns t if the first condition c is true, else returns t2 if the second condition c2 is true, else returns f.

IIF

IIF(bool, x, y): Returns x if bool is true, else returns y.

Switch

Switch(Value,Default,Case1,Result1,...,CaseN,ResultN): Compares a value against a list of cases and returns the corresponding result.

Conversion functions convert numbers to strings or strings to numbers. For parameters and examples, go to the Conversion Functions article.

BinToInt

BinToInt(s): Converts the binary string s to an integer (Limited to 53 bits).

CharFromInt

CharFromInt(x): Returns the Unicode® character that matches the input number x.

CharToInt

CharToInt(s): Returns the number that matches the input Unicode® character s.

ConvertFromCodePage

ConvertFromCodePage(s, codePage): Translates text from a code page to Unicode. To learn more about code pages, go to the Code Pages documentation.

ConvertToCodePage

ConvertToCodePage(s, codePage): Translates text from Unicode® encoding to a specific code page. To learn more about code pages, go to the Code Pages documentation.

HexToNumber

HexToNumber(x): Converts a HEX string to a number (Limited to 53 bits).

IntToBin

IntToBin(x): Converts x to a binary string.

IntToHex

IntToHex(x): Converts x to a hexadecimal string.

ToDegrees

ToDegrees(x): Converts a numeric radian value (x) to degrees via the (x)rad × 180/π calculation. Please note that x must be a numeric value and can’t include any radian symbol (rad).

ToNumber

ToNumber(x, [bIgnoreErrors], [keepNulls], [decimalSeparator]): Converts a string (x), to a number.

ToRadians

ToRadians(x): Converts a numeric degree value (x) to radians via the (x)° × π/180 calculation. Please note that x must be a numeric value and can’t include the degree symbol (°).

ToString

ToString(x, numDec, [addThousandsSeparator], [decimalSeparator]): Converts a numeric parameter (x) to a string using numDec decimal places. Default selection uses a period as the decimal separator.

DateTime functions let you perform an action or calculation on a date and time value. For parameters and examples, go to the DateTime Functions article.

DateTimeAdd

DateTimeAdd(dt,i,u): Adds a specific interval to a date-time value.

DateTimeDay

DateTimeDay(dt): Returns the numeric value for the day of the month in a date-time value.

DateTimeDiff

DateTimeDiff(dt1,dt2,u): Subtracts the second argument from the first and returns it as an integer difference. The duration is returned as a number, not a string, in the specified time units.

DateTimeFirstOfMonth

DateTimeFirstOfMonth(): Returns the first day of the month, at midnight.

DateTimeFormat

DateTimeFormat(dt,f,l): Converts date-time data from ISO format to another specified format (f), in a specified language (l), for use by another application. Output to a string data type. If the specified format carries less precision, the output is truncated.

DateTimeHour

DateTimeHour(dt): Returns the hour portion of the time in a date-time value.

DateTimeLastOfMonth

DateTimeLastOfMonth(): Returns the last day of the current month, with the clock set to one second before the end of the day (23:59:59).

DateTimeMinutes

DateTimeMinutes(dt): Returns the minutes portion of the time in a date-time value.

DateTimeMonth

DateTimeMonth(dt): Returns the numeric value for the month in a date-time value.

DateTimeNow

DateTimeNow(): Returns the current system date and time to seconds.

DateTimeNowPrecise

DateTimeNowPrecise(): Returns the current system date and time with fractions of a second (up to 18 digits of precision). Note that if you request precision beyond what your operating system provides, the function fills in the remainder with zeros.

DateTimeParse

DateTimeParse(dt,f,l): Converts a date string with the specified format (f), in a specified language (l), to the standard ISO format (yyyy-mm-dd HH:MM:SS with optional date-time precision if applicable). If the specified format carries less precision, the output is truncated.

DateTimeQuarter

DateTimeQuarter(dt, [Q1 Start]): Returns the numeric value for the quarter of the year in which a date-time (YYYY-MM-DD) value falls. Use the optional numeric parameter [Q1 Start] to indicate the start month for Q1 (1-12).

DateTimeSeconds

DateTimeSeconds(dt): Returns the seconds portion of the time in a date-time value, including any sub-second precision if applicable.

DateTimeStart

DateTimeStart(): Returns the date and time when the current workflow started running.

DateTimeToday

DateTimeToday(): Returns today’s date.

DateTimeToLocal

DateTimeToLocal(dt): Converts a UTC date-time to the local system time zone. The DateTimeToLocal function currently doesn't support date-time precision in the dt field.

DateTimeToUTC

DateTimeToUTC(dt): Converts a date-time (in the local system time zone) to UTC. The DateTimeToUTC function currently doesn't support date-time precision in the dt field.

DateTimeTrim

DateTimeTrim(dt,t): Removes unwanted portions of a date-time and returns the modified date-time.

DateTimeYear

DateTimeYear(dt): Returns the numeric value for the year in a date-time value.

ToDate

ToDate(x): Converts a string, number, or date-time to a date.

ToDateTime

ToDateTime(x): Converts a string, number, or date to a date-time. ToDateTime function does not support DateTime precision. Excel value should be limited to seconds representation (5 digits, up to 99999).

A file function builds file paths, checks to see if a file exists, or extracts a part of a file path. For parameters and examples, go to the File Functions article.

FileAddPaths

FileAddPaths(Path1, Path2): Adds two file path parts, making sure there is exactly one \ (backslash) between the two paths.

FileExists

FileExists(Path): Returns True if the file exists and False if it doesn't.

FileGetDir

FileGetDir(Path): Returns the directory portion of the path.

FileGetExt

FileGetExt(Path): Returns the extension of the path, including the . (period).

FileGetFileName

FileGetFileName(Path): Returns the name portion of the path, without the extension.

A finance function applies financial algorithms or mathematical calculations. Learn more in the Finance Functions article.

FinanceCAGR

FinanceCAGR(BeginningValue, EndingValue, NumYears): Calculates Compound Annual Growth Rate: The geometric mean growth rate on an annualized basis.

FinanceEffectiveRate

FinanceEffectiveRate(NominalRate, PaymentsPerYear): Calculates Effective Annual Interest Rate: The interest rate on a loan or financial product restated from the nominal interest rate as an interest rate with an annual compound interest payable in arrears.

FinanceFV

FinanceFV(Rate, NumPayments, PaymentAmount, PresentValue, PayAtPeriodBegin): Calculates Future Value of an Investment: The value of an asset at a specified time in the future assuming a certain interest rate or rate of return.

FinanceFVSchedule

FinanceFVSchedule(Principle, Year1Rate, Year2Rate): Calculates Future Value Schedule: The future value of an initial principal after applying a series of interest rates to an investment.

FinanceIRR

FinanceIRR(Value1, Value2): Calculates Internal Rate of Return: The interest rate at which the costs of the investment lead to the benefits of the investment. This means that all gains from the investment are inherent to the time value of money and that the investment has a zero net present value at this interest rate.

FinanceMIRR

FinanceMIRR(FinanceRate, ReinvestRate, Value1, Value2): Calculates Modified Internal Rate of Return: A modification of the internal rate of return that aims to resolve some problems with the IRR. The MIRR is a financial measure of an investment's attractiveness.

FinanceMXIRR

FinanceMXIRR(FinanceRate, ReinvestRate, Value1, Date1, Value2, Date2): Calculates Modified Internal Rate of Return of an investment with dates.

FinanceNominalRate

FinanceNominalRate(EffectiveRate, PaymentsPerYear): Calculates Nominal Annual Interest Rate: An interest rate is called nominal if the frequency of compounding (such as a month) is not identical to the basic time unit (normally a year).

FinanceNPER

FinanceNPER(Rate, PaymentAmount, PresentValue, FutureValue, PayAtPeriodBegin): Calculates the Number of periods for an investment or loan.

FinanceNPV

FinanceNPV(Rate, Value1, Value2): Calculates Net Present Value of an investment: Measures the excess or shortfall of cash flows, in present value terms, once financing charges are met.

FinancePMT

FinancePMT(Rate, NumPayments, PresentValue, FutureValue, PayAtPeriodBegin): Calculates payments on a loan.

FinancePV

FinancePV(Rate, NumPayments, PaymentAmount, FutureValue, PayAtPeriodBegin): Calculates Present Value of an investment: The value on a given date of a future payment, or series of future payments, discounted to reflect the time value of money and other factors such as investment risk.

FinanceRate

FinanceRate(NumPayments, PaymentAmount, PresentValue, FutureValue, PayAtPeriodBegin): Calculates the interest rate (per period).

FinanceXIRR

FinanceXIRR(Value1, Date1, Value2, Date2): Calculates the Internal Rate of Return of an investment with dates.

FinanceXNPV

FinanceXNPV(Rate, Value1, Date1, Value2, Date2): Calculates the Net Present Value of an investment with dates.

A math function performs mathematical calculations. For parameters and examples, go to the Math Functions article.

ABS

ABS(x): Returns the absolute value of (x). It is the distance between 0 and a number. The value is always positive.

ACOS

ACOS(x): Returns the arccosine, or inverse cosine, of (x).

ASIN

ASIN(x): Returns the arcsine, or inverse sine, of (x).

ATAN

ATAN(x): Returns the arctangent, or inverse tangent, of (x).

ATAN2

ATAN2(y, x): Returns the arctangent of y and x (Arc - Tangent of (y/x)).

Average

Average(n1, ...): Returns the average of a list of numbers.

AverageNonNull

AverageNonNull(n1, ...): Returns the average of a list of numbers. Null values are excluded from the calculation. The calculation only works with Numeric data types.

CEIL

CEIL(x, [mult]): Returns a numeric value (x) raised to the lowest multiple of (mult) that is greater than or equal to x. The (mult) parameter must be a number greater than 0. If (mult) is not specified, the function returns the smallest integer greater than or equal to (x).

COS

COS(x): Returns the cosine of (x).

COSH

COSH(x): Returns the hyperbolic cosine of (x).

DISTANCE

DISTANCE(from_Lat,from_Lon, to_Lat, to_Lon): Returns the distance from (lat1,lon1) to (lat2,lon2).

EXP

EXP(x): Returns e raised to the x power (e^x).

FACTORIAL

FACTORIAL(x): Returns the factorial of a numeric value (x) via the (n! = n x (n-1) x … x 1) calculation. For example, the factorial of 4 is calculated as such: 4! = 4 x 3 x 2 x 1 = 24.

FLOOR

FLOOR(x, [mult]): Returns a numeric value (x) lowered to the highest multiple of (mult) that is less than or equal to x. The (mult) parameter must be a number greater than 0. If (mult) is not specified, the function returns the largest integer less than or equal to (x), as shown in the first example below.

LOG

LOG(x): Returns the natural logarithm of (x).

LOG10

LOG10(x): Returns the base-10 logarithm of (x).

Median

Median(...): Calculates the median out of one or more (possibly unsorted) values.

Mod

Mod(n,d): Modulo of (n) divided by (d) (integer operation).

PI

PI(): Returns the value of the constant PI to 15 digits of accuracy.

POW

POW(x, e): Returns (x) raised to the (e) power.

RAND

RAND(): Returns a random number greater than or equal to 0 and less than 1.

RandInt

RandInt(n): Returns a random integer between 0 and the specified parameter.

Round

Round(x, mult): Returns (x) rounded to the nearest multiple of (mult).

SIN

SIN(x): Returns the sine of (x).

SINH

SINH(x): Returns the hyperbolic Sine of (x).

SmartRound

SmartRound(x): Returns (x) rounded to the nearest multiple of a value determined dynamically based on the size of (x).

SQRT

SQRT(x): Returns the square root of (x).

TAN

TAN(x): Returns the tangent of (x).

TANH

TANH(x): Returns the hyperbolic tangent of (x).

A bitwise function operates on one or more bit patterns or binary numerals at the level of their individual bits. Use a bitwise function to manipulate values for comparisons and calculations. For parameters and examples, go to the Math: Bitwise Functions article.

BinaryAnd

BinaryAnd(n,m): Returns a binary of (n) and (m). The result is 1 if both n and m are 1, and 0 otherwise. If 0 is equated with false, and 1 with true the BinaryAnd operation works like a logical And.

BinaryNot

BinaryNot(n): Returns a Binary Not of (n).

BinaryOr

BinaryOr(n,m): Returns a Binary Or of (n) and (m).

BinaryXOR

BinaryXOr(n,m): Returns a Binary XOr of (n) and (m).

ShiftLeft

ShiftLeft(n,b): Left shifts (n) (as integer) by (b) bits.

ShiftRight

ShiftRight(n,b): Right shifts (n) (as integer) by (b) bits.

A minimum or maximum function finds the smallest and largest value of a set of values. For parameters and examples, go to the Min/Max Functions article.

Between

BETWEEN(x, min, max): Tests if a value (x) is between 2 other values (min and max), inclusive of min and max. Returns TRUE if x is between min and max. Returns FALSE if x is not between min and max.

Bound

Bound(x, min, max): If (x < min) returns min, else if (x > max) returns max, else returns (x).

Max

Max(v0, v1, ..., vn): Returns the maximum value from the list.

MaxIDX

MaxIDX(v0, v1,..., vn): Returns the 0-based index of the maximum value from the list.

Min

Min(v0, v1,..., vn): Returns the minimum value from the list.

MinIDX

MinIDX(v0, v1,..., vn): Returns the 0-based index of the minimum value from the list.

An operator is a character that represents an action. Use an arithmetic operator to perform mathematical calculations or a Boolean operator to work with true/false values. Learn more in the Operators article.

Block Comment

/*Comment*/: Use a block comment operator to add a comment block to an expression editor (within the expression line) without interfering with the expression.

Single Line Comment

// Comment: Use the single-line comment operator to add a single-line comment to an expression editor without interfering with the expression.

Boolean AND &&

Boolean AND &&: (Boolean AND) Use this operator to combine 2 Boolean values. The result is also a Boolean value. The result is True if both of the combined values are true, and the result is False if either of the combined values is false.

Boolean AND - Keyword

Boolean AND - Keyword: (Boolean AND) Use this operator to combine 2 Boolean values. The result is also a Boolean value. The result is True if both of the combined values are true, and the result is False if either of the combined values is false.

Boolean NOT !

Boolean NOT !: (Boolean NOT) Accepts one input. If that input is true, it returns False. If that input is false, it returns True.

Boolean NOT - Keyword

Boolean NOT - Keyword: (Boolean NOT) Accepts one input. If that input is true, it returns False. If that input is false, it returns True.

Boolean OR - Keyword

Boolean OR - Keyword: (Boolean OR) If either (or both) of the 2 values are true, it returns True.

Boolean OR ||

Boolean OR ||: (Boolean OR) If either (or both) of the 2 values are true, it returns True.

Close Parenthesis

Close Parenthesis ): Close parenthesis.

Division

Division /: Use the division operator to divide a numeric value by another numeric value.

Equal To

Equal To =: Equal to.

Greater Than

Greater Than >: Greater than.

Greater Than or Equal

Greater Than Or Equal >=: Greater than or equal.

Less Than

Less Than <: Less than.

Less Than or Equal

Less Than Or Equal <=: Less than or equal.

Multiplication

Multiplication *: Use the multiplication operator to multiply multiple numeric values together.

Not Equal To

Not Equal To !=: Not equal to.

Open Parenthesis

Open Parenthesis (: Open parenthesis.

Subtraction

Subtraction -: Use the subtraction operator to subtract a numeric value from another.

Value IN - Keyword

value IN (...) - Keyword: (Test Value in List) Determines if a given value matches any value in a subquery or a list.

Value NOT IN - Keyword

value NOT IN (...) - Keyword: (Test Value Not in List) Determines if a given value matches any value not in a subquery or a list.

A spatial function builds spatial objects, analyzes spatial data, and returns metrics from spatial fields. For parameters and examples, go to the Spatial Functions article.

ST_Area

ST_Area(object, units): Returns the area of the spatial object in the specified units (numeric data value.)

ST_Boundary

ST_Boundary(object): Returns the boundary of the spatial object (polyline spatial object indicating the boundary of the input polygon.)

ST_BoundingRectangle

ST_BoundingRectangle(object, ...): Returns the bounding rectangle of the spatial object (polygon spatial object.)

ST_Centroid

ST_Centroid(object): Returns the centroid of the spatial object (point spatial object.)

ST_CentroidX

ST_CentroidX(object): Returns the longitude of the centroid of the spatial object (numeric data value.)

ST_CentroidY

ST_CentroidY(object): Returns the latitude of the centroid of the spatial object (numeric data value.)

ST_Combine

ST_Combine(object1, object2,...): Combines the spatial objects (spatial object.)

ST_Contains

ST_Contains(object1,object2): Returns True if object1 contains object2 (Boolean value.)

ST_ConvexHull

ST_ConvexHull(object1,...): Returns the convex hull of the spatial objects (spatial object.)

ST_CreateLine

ST_CreateLine(point1, point2...): Creates a line by connecting the specified points and lines in a sequence (spatial object.)

ST_CreatePoint

ST_CreatePoint(x,y): Returns a spatial object containing the specified longitude and latitude coordinates (spatial object.)

ST_CreatePolygon

ST_CreatePolygon(obj1, obj2...): Creates a polygon by connecting the specified points and lines in a sequence (spatial object.)

ST_Cut

ST_Cut(object1,object2): Returns the result of cutting object1 from object2 (spatial object.)

ST_Dimension

ST_Dimension(object): Returns the dimension of the spatial object. The spatial dimension is the minimum number of coordinates needed to specify every point that makes up the spatial object (numeric data value.)

ST_Distance

ST_Distance(object1, object2, units): Returns the distance from object1 to object2 in the specified units. Supported units are Miles (Mi), Kilometers (KM), Meters, and Feet (numeric data value.)

ST_EndPoint

ST_EndPoint(object): Returns the last point of the spatial object (point spatial object.)

ST_Intersection

ST_Intersection(object1, object2, ...): Returns the intersection of the specified spatial objects (spatial object.)

ST_Intersects

ST_Intersects(object1, object2...): Returns True if the spatial objects intersect (Boolean value.)

ST_InverseIntersection

ST_InverseIntersection(object1, object2...): Returns the inverse intersection of the specified spatial objects (spatial object.)

ST_Length

ST_Length(object, units): Returns the linear length of the spatial object in the specified units (numeric data value.)

ST_MD5

ST_MD5(object): Calculates the MD5 hash of the spatial object.

ST_MaxX

ST_MaxX(object): Returns the maximum longitude of the spatial object (numeric data value.)

ST_MaxY

ST_MaxY(object): Returns the maximum latitude of the spatial object (numeric data value.)

ST_MinX

ST_MinX(object): Returns the minimum longitude of the spatial object (numeric data value.)

ST_MinY

ST_MinY(object): Returns the minimum latitude of the spatial object (numeric data value.)

ST_NumParts

ST_NumParts(object): Returns the number of parts in the spatial object (numeric data value.)

ST_NumPoints

ST_NumPoints(object): Returns the number of points in the spatial object (numeric data value.)

ST_ObjectType

ST_ObjectType(object): Returns the spatial object type as a string value (string data value.)

ST_PointN

ST_PointN(object, n): Returns the Nth point in the spatial object (point spatial object.)

ST_RandomPoint

ST_RandomPoint(object): Returns a random point within the spatial object (point spatial object.)

ST_Relate

ST_Relate(object1,object2,relation): Returns True if the objects satisfy the provided DE-9IM relation (Boolean value.)

ST_StartPoint

ST_StartPoint(object): Returns the first point in the spatial object (spatial object.)

ST_Touches

ST_Touches(object1, object2): Returns True if object1 touches object2 (Boolean value.)

ST_TouchesOrIntersects

ST_TouchesOrIntersects(object1, object2): Returns True if object1 touches or intersects object2 (Boolean value.)

ST_Within

ST_Within(object1, object2): Returns True if object1 is contained by object2 (Boolean value.)

These functions perform a variety of specialized actions and can be used with all data types. For parameters and examples, go to the Specialized Functions article.

Coalesce

Coalesce(v1,v2,v3,…,vn): Returns the first non-null value.

EscapeXMLMetacharacters

EscapeXMLMetacharacters(String): Replaces XML metacharacters with their escaped versions. There are 5 characters that are escaped.

GetVal

GetVal(index, v0,...vn): Returns the value (v0, ..., vn) specified by the 0-based index.

GetEnvironmentVariable

GetEnvironmentVariable(Name): Returns the environment variable specified in Name. To get a list of environment variables, on your machine go to Control Panel > System and Security > System > Advanced System Settings > Environment Variables. A list of system variables appears. You can use any value from this list.

Message

Message(messageType, message, returnValue): Use with a conditional statement to output a message to the message log and update column data with a specified value when a condition is met.

NULL

NULL(): Returns a Null value.

RangeMedian

RangeMedian(...): Calculates the median from a series of aggregated ranges. Visit Range Median for more information.

ReadRegistry

ReadRegistryString(Key, ValueName, DefaultValue="): Returns a value from the registry.

Soundex

Soundex(String): Returns the Soundex of String. Soundex creates a code based on the first character in the string plus three characters based on these items:

Soundex_Digits

Soundex_Digits(String): Returns the first 4 digits or the Soundex if none.

TOPNIDX

TOPNIDX(N, v0, v1, ..., vn): Returns the 0-based index of the Nth from the maximum value from the list. With N==0, it is the same as MaxIdx(...).

UrlEncode

UrlEncode(String): Encodes UTF-16 String as a web-legal URL for both the original engine and AMP.

A string function performs operations on text data. Use a string function to cleanse data, convert data to a different format or case, compute metrics about the data, or perform other manipulations. For parameters and examples, go to the String Functions article.

Contains

Contains(String, Target, CaseInsensitive=1): Searches for the occurrence of a particular string within a string. Returns True if (String) contains (Target), else returns False.

CountWords

CountWords(string): Returns the count of words in the specified string. Words are defined by characters separated by a space.

DecomposeUnicodeForMatch

DecomposeUnicodeForMatch(String): Removes accents and expands compound characters while converting to narrow. This function takes a Unicode string and translates it to a lowercase, narrow character string. All accents and other decorations are removed.

EndsWith

EndsWith(String, Target, CaseInsensitive=1): Checks if a string ends with a particular string. Returns True if (String) ends with (Target), else returns False. It defaults to case insensitive.

FindNth

FindNth(Initial String, Target, Instance): Finds the Instance (nth occurrence) of a target string in the initial string and returns the 0-indexed position of the instance. The function is case-sensitive and doesn’t accept negative parameters.

FindString

FindString(String,Target): Searches for the occurrence of a particular string (Target) within another string (String) and returns the numeric position of its occurrence in the string. Returns the 0-based index of the first occurrence of (Target) in (String). Returns -1 if no occurrence.

GetLeft

GetLeft(String, Delimiter): Returns the left part of the provided (String) until the first instance of the specified 1 or more (Delimiter).

GetPart

GetPart(String, Delimiter, Index): Uses the specified 1 or more (Delimiter) to divide (String) into substrings, and returns the substring in the position that is specified in (Index). For example, if Index is 2, the function returns the second substring.

GetRight

GetRight(String, Delimiter): Returns the right part of the provided (String) after the first instance of the specified 1 or more (Delimiter).

GetWord

GetWord(string, n): Returns the Nth (0-based) word in the string. Words are defined as a collection of characters separated by a space. 0-based index means the first word is at the 0 position.

Left

Left(String, len): Returns the first (len) characters of the string (String). If len is less than 0 or greater than the length of String, String remains unchanged.

Length

Length(String) : Returns the length of the string (String).

LowerCase

LowerCase(String): Converts a string to lowercase.

MD5_ASCII

MD5_ASCII(String): Calculates the MD5 hash of the string. The string is expected to be only narrow characters. Wide characters are converted to ‘?’ before computing the hash. Use for String types, not recommended for WString types. You should avoid using this function on strings that may contain wide characters.

MD5_UNICODE

MD5_UNICODE(String): For both the original engine and AMP, calculates the MD5 hash of the string stored as UTF-16.

MD5_UTF8

MD5_UTF8(String): Calculates the MD5 hash of the string stored as UTF-8.

PadLeft

PadLeft (String, len, char): Pads the string to the left with the specified character to the specified length. If the padding (char) is more than one character long, only the first character is used.

PadRight

PadRight (String, len, char): Pads the string to the right with the specified character to the specified length. If the padding (char) is more than one character long, only the first character is used.

REGEX_CountMatches

REGEX_CountMatches(String,pattern,icase): Returns the count of matches within the string to the pattern.

REGEX_Match

REGEX_Match(String,pattern,icase): Searches a string for an occurrence of a regular expression. Tells if the string matches the pattern from the first character to the end.

REGEX_Replace

REGEX_Replace(String, pattern, replace, icase): Allows replacement of text using regular expressions and returns the string resulting from the RegEx find (pattern) and replace (string). All occurrences of the match are replaced, not just the first.

Replace

Replace(String, Target, Replacement): Returns the string (String) after replacing each occurrence of the String (Target) with the String (Replacement).

ReplaceChar

ReplaceChar(String, y, z): Returns the string (String) after replacing each occurrence of the character (y) with the character (z). If the replacement character (z) is a string with more than one character, only the first character is used. If (z) is empty, each character (String) that matches any character in (y) is simply removed.

ReplaceFirst

ReplaceFirst(String, Target, Replacement): Returns the string (String) after replacing the first occurrence of the string (Target) with the string (Replacement).

ReverseString

ReverseString(String): Reverses all the characters in the string.

Right

Right(String, len): Returns the last (len) characters of the string. If len is less than 0 or greater than the length of String, the string remains unchanged.

StartsWith

StartsWith(String, Target, CaseInsensitive=1): Checks if a string starts with a particular string. Returns True if String starts with a particular string Target, else returns False.

STRCSPN

STRCSPN(String, y): Returns the length of the initial segment of the string (String) consisting of characters not in the string (y).

StripQuotes

StripQuotes(String): Removes a matched set of quotation marks or apostrophes from the ends of the string.

STRSPN

STRSPN(String, y): Returns the length of the initial segment of the string (String) consisting of characters in the string (y).

Substring

Substring(String, start, length): Returns the substring of (String) starting at (start), stopping after (length), if provided.

TitleCase

TitleCase(String) : Converts a string to title case.

Trim

Trim(String, y): Removes the characters in the string (y) from the ends of the string (String). Y is optional and defaults to trimming white space. Notice in the TRIM function examples the specified characters are trimmed. It doesn't matter what order the characters are in.

TrimLeft

TrimLeft(String, y): Removes character in the string (y) from the beginning of the string (String). Y is optional and defaults to trimming white space.

TrimRight

TrimRight(String, y): Removes character in the string (y) from the end of the string (String). Y is optional and defaults to trimming white space.

Uppercase

Uppercase(String): Converts a string to upper case.

Uuid

UuidCreate(): Creates a unique identifier.

A test function performs data comparisons. Use a test function to identify the data type of a value, or determine if a value exists. For parameters and examples, go to the Test Functions article.

CompareDictionary

CompareDictionary(a,b): Compares 2 strings ignoring case differences, and compares text that appears to consist of numbers, in numerical order. The function returns -1 if a < b, 0 if a==b, and 1 if a > b.

CompareDigits

CompareDigits(a, b, nNumDigits): Compares 2 numbers and determines if they are the same to the given number of digits (nNumDigits). The comparison finds the difference between the numbers and reports A and B the same when the leading digit of the difference is NumDigits or more places to the right of the digits of the larger of A and B.

CompareEpsilon

CompareEpsilon(a, b, epsilon): Compares 2 floating-point numbers and returns True if they are within epsilon.

EqualStrings

EqualStrings(a,b): Compares string (a) to string (b) to determine if they are an exact match. The function returns True if all characters in both a and b are identical and False if 1 or more characters are not identical.

IsEmpty

IsEmpty(v): Tests if v is NULL or equal to "".

IsInteger

IsInteger(v) : Tests if (v) contains a value that can be converted to an integer. If so, it returns True.

IsLowerCase

IsLowerCase(String): Returns True if all (one or more) alphabetic characters in a string are lowercase and False if one or more alphabetic characters are uppercase. The function ignores non-alphabetic characters. Note that this function only applies to characters with a case distinction and might not apply to characters in all languages.

IsNull

IsNull(v) : Tests if (v) is NULL. Returns True if v is NULL, otherwise returns False.

IsNumber

IsNumber(v) : Tests if the field type for (v) is a number or not.

IsSpatialObj

IsSpatialObj(v): Tests if the field type for (v) is a spatial object or not.

IsString

IsString(v): Tests if the field type for (v) is a string or not.

IsUpperCase

IsUpperCase(String): Returns True if all (one or more) alphabetic characters in a string are uppercase and False if one or more alphabetic characters are lowercase. The function ignores non-alphabetic characters. Note that this function only applies to characters with a case distinction and might not apply to characters in all languages.