Functions Reference

The following tables describe the functions that may be used to create calculated fields in Data Prep Studio.

Date Functions

Date functions operate on or with dates.

Function & Syntax	Description
Age(startdate[,enddate [,interval]])	Returns the number of whole intervals between the given start date and end date.
CalendarWeek(date)	Returns the week number of the calendar week in which the given date falls. This is similar to the Week function, but it does week numbering according to the current settings under Options/Time Intervals.
Date([date])	Returns the date portion of a given date/time or today's date.
DateAdjust(date,years [,months[,days]])	Returns the given date value adjusted forward or backward by the given integral number of years, months, and days.
Day(date)	Returns the number of day (1-31) from a date.
DayOfYear(date)	Returns the day number (1-366) from a date/time.
Days360(startdate,enddate[,method])	Returns the number of days between the given start date and end date, based on a 360-day year. If method is 0 (or omitted) then the calculation is based on the US (NASD) method, otherwise it is based on the European method.
FirstDay(date interval_type)	Returns the date of the first day of the interval in which the given date falls, where interval_type specifies the type of time interval.
FiscalDayOfYear(date)	Returns the day number of the day within the fiscal year in which the given date falls.
FiscalHalfYear(date)	Returns the number (1 or 2) of the fiscal half-year in which the given date falls.
FiscalPeriod(date)	Returns the number (1-13) of the fiscal period in which the given date falls.
FiscalQtr(date)	Returns the number (1-4) of the fiscal quarter in which the given date falls.
FiscalWeek(date)	Returns the week number of the fiscal week in which the given date falls.
FiscalYear(date [at_start])	Returns the year number of the fiscal year in which the given date falls. If at_start is 0 or not given, then the fiscal year is numbered for the calendar year in which it ends. If at_start is non-zero, then the fiscal year is numbered for the calendar year in which it starts.
HalfYear(date)	Returns the number (1 or 2) of the calendar half-year in which the given date falls.
Hour(date)	Returns the hour (0-23) from a date/time.
IsoDayOfYear(date)	Returns the day number within the ISO8601 year on which the given date falls.
IsoWeek(date)	Returns the ISO8601 week number of the week in which the given date falls.
IsoWeekDay(date)	Returns the number of the day of the week for the given date, where 1=Monday, 2=Tuesday, …, 7=Sunday.
IsoYear(date)	Returns the year corresponding to the ISO8601 week in which the given date falls. For dates near the end of December or the start of January this may be different than the calendar year.
LastDay(date, interval_type)	Returns the date of the last day of the interval in which the given date falls, where interval_type specifies the type of time interval.
Max(expr1,expr2 [,expr3[,expr4]])	This function returns the larger of expr1, expr2 and optionally expr3 and expr4. Accepts numeric, date or character input.
Min(expr1,expr2 [,expr3[,expr4]])	This function returns the smaller of expr1, expr2 and optionally expr3 and expr4. Accepts numeric, date or character input.
Minute(date)	Returns the minute (0-59) from a date/time.
Month(date)	Returns the number of month (1-12) from a date.
Now()	Returns the current date and time.
Qtr(date)	Returns the quarter of the year within which a date falls: 1 = January - March, 2 = April - June, 3 = July - September and 4 = October - December.
Second(date)	Returns the second (0-59) from a date/time.
Time([date])	Returns the time portion of a date/time or the current time.
Today()	Returns today’s date (i.e., the current system date).
Week(date[,startday])	Returns the number of week (1-53) from a date. startday designates the day that begins each week.
Weekday(date [,startday])	Returns the number of the weekday (1-7) of a date. startday designates the day that begins each week.
Year(date)	Returns the number of year from a date. Valid range is 1601-2400.
YearFrac(startdate, enddate)	Returns the fractional number of years represented by the number of whole days between the given start date and end date, according to basis, which has the following meanings: 0 (or omitted)=US (NASD) 30/360; 1=actual/actual; 2=actual/360; 3=actual/365; 4=European 30/360.

String Functions

String functions operate on or with character strings.

Function & Syntax	Description
Extract(string,start string[,end string])	Extracts a substring between a starting string and an optional ending string.
Instr(search string, target string)	Returns the numeric position of search string within the field specified in target string. If the search string is not found, a value of zero is returned.
InTrim(string)	This function trims any sequence of consecutive spaces within a string to a single space, and also removes any leading or trailing spaces from it.
IsAlpha(character)	Returns 1 (true) if character is alphabetic, otherwise returns 0 (false).
IsBlank(string)	Returns 1 (true) if string is either empty or contains all blanks, otherwise returns 0 (false).
IsLower(character)	Returns 1 (true) if character is a lowercase alphabetic, otherwise returns 0 (false).
IsUpper(character)	Returns 1 (true) if character is an uppercase alphabetic, otherwise returns 0 (false).
Left(string,n)	Returns n number of characters from the beginning of string.
Len(string)	Returns the length of string as a number.
Lower(string)	Converts all uppercase letters in string to lowercase letters.
LSplit(string, maxparts,sep,n)	Starts on the left and splits string into the specified number of parts (maxparts) using sep as the separator. sep may be a single character or a substring. n specifies which part to return.
LTrim(string)	Removes leading spaces from string.
Max(expr1,expr2 [,expr3[,expr4]])	This function returns the larger of expr1, expr2 and optionally expr3 and expr4. Accepts numeric, date or character input.
Min(expr1,expr2 [,expr3[,expr4]])	This function returns the smaller of expr1, expr2 and optionally expr3 and expr4. Accepts numeric, date or character input.
NSplit(string,n)	Reads string as a person’s name, splits it into five substrings - prefix, first name, middle initial or middle name, last name, and suffix - then returns the specified substring (n).
Proper(string)	Forces to uppercase the first letter of each word in string.
PSplit(string,n)	Reads string as a postal code, splits it into 3 substrings - city, state, and postal code - then returns the specified substring (n).
RegexExtract(string,pattern[,N])	Scans the given string for matches to the given regular expression pattern. Returns the text of the Nth match found. If N is not given, this function returns the text of the first match found, if any.
RegexIsMatch(string,pattern)	Scans the given string for a match to the given regular expression pattern. Returns 1 if a match was found, 0 otherwise.
RegexReplace(string,pattern, replacement)	Scans the given string for matches to the given regular expression pattern. Each match found is replaced according to the substitutions in the replacement string, and the overall resultant string is returned.
RegexSplit(string,pattern[,piece])	Splits the given string into pieces based on matches of the given regular expression pattern. Returns the requested piece, which is a 1-based number indicating the desired piece, counting from the left. Returns the first piece if piece is not specified.
Replace(string,old,new)	Replaces a substring in a string with a new substring.
Right(string,n)	Returns n number of characters from the end of string.
RSplit(string, maxparts,sep,n)	Starts on the right and splits string into the specified number of parts (maxparts) using sep as the separator. sep may be a single character or a substring. n specifies which part to return.
RTrim(string)	Trims trailing spaces from string.
Space(number)	Returns a string consisting of a specified number of spaces.
Strip(string,stripchars)	Strips the indicated characters (stripchars) from string.
Stuff(s,p,n,c)	Returns a string by replacing n number of characters in string s, starting at position p, using replacement string c.
Substr(string,starting position,length)	Extracts a substring of a specified length and starting position from string.
TextLine(string,n)	Splits a multi-line string at the line breaks and returns the specified line.
Trim(string)	Trims leading and trailing spaces from string.
Upper(string)	Converts all lowercase letters in a character string to uppercase letters.

Numeric Functions

Numeric functions operate on or with numbers.

Function & Syntax	Description
Abs(number)	Returns the absolute value of a number.
Ceiling(number [,number2])	Rounds a number up (to the next multiple of number2, if specified).
Exp(number)	Returns e raised to a number.
Floor(number [,number2])	Rounds a number down (to the previous multiple of number2, if specified).
GeoDistance(lat1,long1,lat2,long2[,use_metric])	Returns the distance between (lat1,long1) and (lat2,long2) where the latitudes and longitudes are specified in degrees. If use_metric is 0 or not given, the distance is returned in miles, otherwise the the distance is returned in kilometers.
Int(number)	Returns the integer portion of a number.
Log(number)	Returns the natural logarithm (base e) of a number.
Max(expr1,expr2 [,expr3[,expr4]])	This function returns the larger of expr1, expr2 and optionally expr3 and expr4. Accepts numeric, date or character input.
Min(expr1,expr2 [,expr3[,expr4]])	This function returns the smaller of expr1, expr2 and optionally expr3 and expr4. Accepts numeric, date or character input.
Mod(number1, number2)	Returns the remainder after dividing number1 by number2.
MRound(number, number2)	Rounds a number to the nearest multiple of number2.
Pi()	Returns the value of Pi
Round(number [,decimals])	Rounds number to a specified number of places to the right (or left) of the decimal point. Note: Negative values of decimals round the integer portion of the number rather than the decimal portion.
Sqrt(number)	Returns the square root of number.
ZeroIfNull(expression)	Returns 0.0 if expression is null, otherwise returns the value of the expression. Applies only to numeric expressions.

Conversion Functions

Conversion functions convert a value of one type to another type, such as a date to a string.

Function & Syntax	Description
Asc(string)	Returns the numeric value of a character.
Base64Decode(string)	Decodes a string from a Base64 format string.
Base64Encode(string)	Encodes a string to a Base64 format string.
CharToSeconds (timestring)	Converts a time string to a number representing seconds since midnight.
Chr(number)	Returns the character value of a number.
CtoD(string [,date format[,extraction format]])	Converts a string to date format. date format is an optional parameter that indicates the date format, "m/d/y", "d/m/y" or "y/m/d". extraction format is an optional parameter that indicates the date/time extraction pattern. "D" extracts a date, "DT" extracts a date followed by a time, "T" extracts a time and "TD" extracts a time followed by a date. More than one extraction pattern may be combined to form a list of extraction patterns. For example: "D,DT" means extract a date OR a date followed by a time.
DateToJulian(date [,length])	Converts a date to a Julian date string of the given length. Default length is 5.
DegMinSecToDecimal(degrees,minutes[,seconds])	Converts a number of degrees,minutes and seconds to decimal degrees.
DegreesToRadians(angle)	Converts an angle in degrees to an angle in radians
Dtoc(date)	Converts a date to character format.
JulianToDate(string)	Converts a Julian date string to a date.
KilometersToMiles(kilometers)	Converts a number of kilometers to miles
MilesToKilometers(miles)	Converts a number of miles to kilometers.
RadiansToDegrees(angle)	Converts an angle in radians to an angle in degrees
SecondsToChar (seconds)	Converts a number representing seconds since midnight to a time string.
Str(number[,length[,decimals[,fillchar]]])	Converts a number to a string. length is the total length of the string to return, decimals specifies the decimal position to round, and fillchar is a character used to fill up to the value specified in length.
Val(string)	Converts a string to a number. Note: The string must begin with a numeric character or a negation sign. If the string contains any non-numeric characters (apart from a decimal delimiter character) this function will convert the numeric portion of the string up to the first non-numeric character it encounters.

Report Functions

Report functions return information relating to the source of a record.

Function & Syntax	Description
Recno()	Returns detail record number. Each record is assigned a record number when the record is generated from a detail line in the report (e.g., Recno=1 always corresponds to the first detail line extracted from the report). Record numbers do not change when a filter is applied to the table or when the table is sorted, but do change if the detail template is modified in such a way as to yield more or fewer records).
Rowno()	Returns the row number of a record. This function differs from the Recno function in that row numbers are reassigned each time you sort or filter the table while record numbers are not.

Redaction Functions

Monarch Data Prep Studio provides the following functions that will allow you to replace, or mask out field values, so that only a representation of the field value is displayed, and not the actual value.

Function & Syntax	Description
RedactStrikeout(text[,overstrike[,keepBlanks]])	Redacts the given text by striking out characters with a given overstrike character, optionally keeping any blanks.
RedactStrikeoutDigits(text[,overstrike[,keepLastN]])	Redacts the given text by striking out characters with a given overstrike character, optionally keeping any blanks.

Trigonometric Functions

Monarch Data Prep Studio provides the following special functions that allow you to carry out trigonometric calculations.

Function & Syntax	Description
Acos(number)	Returns the arccosine, also known as inverse cosine, value (in radians) of a given number.
Asin(number)	Returns the arcsine, also known as inverse sine, value (in radians) of a given number.
Atan(number)	Returns the arctangent, also known as inverse tangent, value (in radians) of a given number.
Atan2(y,x)	Returns the arctangent, also known as inverse tangent, value (in radians) of y/x
Cos(number)	Returns the cosine of an angle.
Cot(number)	Returns the cotangent of an angle.
Csc(number)	Returns the cosecant of an angle.
GeoDistance(lat1,long1,lat2,long2[,use_metric])	Returns the distance between two points on a sphere given their longitudes and latitudes. This function uses the haversine formula to compute for the distance. The calculation assumes the earth's radius to be 3958.7613 miles / 6371.0088 kilometers.
Sec(angle)	Returns the secant of an angle (specified in radians)
Sin(number)	Returns the sine of an angle.
Tan(number)	Returns the tangent of an angle.

Special Functions

Monarch Data Prep Studio provides the following special functions that can be used to perform comparisons, check the status of fields, or generate random numbers (for use in auditing or other applications).

Function & Syntax	Description
If(condition,true value, false value)	Returns true value or false value from a conditional expression. If the expression is true, the true value is returned, otherwise the false value is returned. The return value may be a date, string or number.
IsEmpty(field)	Returns 1 if field is empty, otherwise returns 0. Note: The field parameter must be the name of a field from the Table window.
IsNull(expression)	Returns 1 if expression is null, otherwise returns 0.
Rand()	Returns a record’s random number in the range 0 to 32767.
RandEx()	Return’s a record’s random number in the range 0 to 4294967295