# Functions: Quick Reference

These function types are available for transforming your data. The type of data determines the functions you can use. Go to Data Types for more information.

Use this guide to quickly reference functions that you can use in the Expression Editor in Designer. If you need more information and examples, go to the category-specific pages linked in each section.

**Custom Formula Functions**

If you use custom formula functions and your custom function has the same name as an existing Alteryx function (or a newly-introduced Alteryx function), you might encounter unexpected results and conflicts.

To resolve this, rename your custom function (or add a prefix/suffix) to differentiate it from base-installed Designer functions.

## Conditional

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

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

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

## File

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.

## Finance

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.

## Math

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

## Math: Bitwise

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.

## Min/Max

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.

## Operators

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.

## Spatial

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

Point objects return a value of 0.

Line objects return a value of 1.

Polygon objects return a value of 2

###### 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.)

## Specialized

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.

Character | Escaped Version |
---|---|

" | " |

' | ' |

< | < |

> | > |

& | & |

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

**Server Support**

Alteryx doesn't support the `GetEnvironmentVariable`

in apps saved to Server.

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

Character | Soundex |
---|---|

Non-Alpha Characters (numbers and punctuation) | -1 |

a, e, i, o, u, y, h, and w | Ignored unless it's the first character in the string. |

b, f, p, and v | 1 |

c, g, j, k, q, s, x, and z | 2 |

d and t | 3 |

l | 4 |

m and n | 5 |

r | 6 |

###### Soundex_Digits

`Soundex_Digits(String)`

: Returns the first 4 digits *or* the Soundex if none.

If there are digits (numbers) in the String, the first 4 digits are returned.

If there are no digits, the Soundex code is returned.

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

## String

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.

## Test

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.