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.
UnicodeNormalize
UnicodeNormalize(String, Form)
: Converts text data in the provided string into a standardized Unicode form. Use the required Form parameter (case-insensitive) to define the normalization form.
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],[tz])
: Converts date-time data from ISO format to another specified format (f), in a specified language (l), using the time zone name (tz) for use by another application. Output to String data type. If the specified format carries less precision than the incoming data, the output is truncated and not rounded.
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([tz])
: Returns the current date and time, including seconds. If you provide the optional time zone (tz) parameter, the returned time is in that time zone. If you don't provide a time zone, the returned time uses your system time zone.
DateTimeNowPrecise
DateTimeNowPrecise(digits,[tz])
: Returns the current system date and time with fractions of a second (up to 18 digits of precision). If you provide the optional time zone (tz) parameter, the returned time is in that time zone. If you don't provide a time zone, the returned time uses your system time zone.
DateTimeParse
DateTimeParse(string,f,[l],[tzName])
: Converts a date string (string) with the specified format (f), in a specified language (l), with a specified time zone (tzName) to the 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,[tz])
: Converts a UTC date-time to the optional tz argument, else to the local system time zone. The DateTimeToLocal function now supports date-time precision in the dt field.
DateTimeToUTC
DateTimeToUTC(dt,[tz])
: Converts a date-time (in the local system time zone or the provided time zone) to UTC. The input date-time is assumed to be in the given time zone, or else in the time zone of the system on which the workflow is running. The DateTimeToUTC function now supports 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 a UTF-16 string using non-standard, UTF-16-based percent-encoding for both the original engine and AMP. This method is no longer recommended. Please use the UrlEncodeUTF8 function instead.
UrlEncodeUTF8
UrlEncodeUTF8(String)
: Encodes a string using the RFC 3986-compliant percent-encoding.
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.