Using Functions

Character Functions

ARGLEN

(length, 
source_string, 
output_format)

Measures the length of a character string within a field, excluding trailing blanks.

ASIS

(argument)

Distinguishes between a space and a zero.

Note: This function is only available for variables and appears only in the User Variables calculator.

CHKFMT

(numchar,  
source_string, 'mask',  
output_format)

Checks numeric and alphanumeric fields for invalid character types.

CTRAN

(length,  
source_string,  
decimal, decvalue,  
output_format)

Replaces characters in a string, taking decimal ASCII values as arguments for the target.

CTRFLD

(source_string,  
length, output_format)

Centers a character string within a field.

EDIT

(source_string)

Converts between alphanumeric and integer data.

EDIT

(source_string, 
'mask')

Using a mask, extracts characters from an alphanumeric string and inserts new characters.

GETTOK

(source_string,  
length, token_number, 
'delim', outlen,  
output_format)

Divides a string into tokens and returns the specified token.

LCWORD

(length,  
source_string,  
output_format)

Converts the letters in a given string to mixed-case: the first letter of each new word and the first letter after a single or double quotation mark are converted to uppercase. The rest are converted to lowercase.

LCWORD2

(length,  
source_string,  
output_format)

Converts a character string to mixed-case. The first letter of every word is converted to uppercase.

LCWORD3

(length,  
source_string,  
output_format)

Converts a character string to mixed-case; the first letter after every apostrophe remains capitalized and every other letter is converted to lowercase, unless it is the last letter of the word. If it is the last letter, it will be converted to lowercase.

LJUST

(length,  
source_string,  
output_format)

Left-justifies a character string within its field.

LOCASE

(length,  
source_string,  
output_format)

Returns the infield in lowercase.

OVRLAY

(source_string,  
length, substring,  
sublen, position,  
output_format)

Overlays a substring to a specified place on another character string.

PARAG

(length,  
source_string, 
'delim',  
max_token_size,  
output_format)

Divides lines of text into smaller lines by marking them off with a delimiter character.

PATTERN 

(length,  
source_string,  
output_format)

Converts text to its pattern.

PATTERNS

(string)

Returns a character string that represents the pattern of the input string. Letters appear as 'A' or 'a', digits appear as '9', and special characters appear as is. This function is available starting in release 7708.

POSIT

(source_string,  
length, substring,  
sublength,  
output_format)

Finds the starting position of a substring within a larger string.

REPLACE

(input_string, search_string, replacement)

Replaces all instances of a search string in an input string with the given replacement string.

RJUST

(length,  
source_string,  
output_format)

Right-justifies a character string within a field.

SOUNDEX

(length,  
source_string,  
output_format)

Converts character strings to 4-letter codes, enabling you to search for character strings phonetically without knowing how they are spelled.

SPELLNM

(outlength, number,  
output_format)

Takes a number with two decimal spaces and spells it out with dollars and cents.

SPLIT

(element, string)

Returns a specific type of element from a string. The output is returned as variable length alphanumeric.

SQUEEZ

(length,  
source_string,  
output_format)

Reduces multiple contiguous blank characters within an input string to a single blank character.

STRIP

(length,  
source_string, char,  
output_format)

Removes all occurrences of a specific character from an input string.

STRREP

(inlength, instring, 
searchlength, 
searchstring,
replength, repstring, 
outlength, 
output_format)

Replaces character strings.

SUBSTR

(length,  
source_string, start,  
end, sublength,  
output_format)

Extracts a substring of a given length and starting position from a larger string.

TRIM

(trim_where,  
source_string, length,  
pattern, sublength,  
output_format)

Removes leading and/or trailing occurrences of a pattern within a string.

TRUNCATE

(ARGUMENT)

Removes trailing blanks from Dialogue Manager amper variables, and adjusts the length accordingly.

Note: This function is only available for variables and appears only in the User Variables calculator.

UPCASE

(length,  
source_string,  
output_format)

Converts all characters in a string to uppercase.

Character Functions - DBCS Code Pages

DCTRAN

(length, source_string, decimal, decvalue, output_format)

Translates a single-byte or double-byte character within a character string to another character based on its decimal value.

DEDIT

(length, source_string, mask_length, 'mask', output_format)

Extracts characters from or adds characters to a string when server is configured to use a DBCS code page.

DPART

(date, 'component', output_format)

Retrieves a date component as a numeric value.

DSTRIP

(length, source_string, char, output_format)

Removes all occurrences of a specific single-byte or double-byte character from a string.

DSUBSTR

(length, source_string, start, end, sublength, output_format)

Extracts a substring based on its length and position in the parent string when server is configured to use a DBCS code page.

Character Functions - Variable Length

LENV

(source_string,  
output_format)

Returns the actual length of an AnV input field or the size of an An field.

LOCASV

(upper_limit,  
source_string,  
output_format)

Converts alphabetic characters to lowercase and is similar to LOCASE.

POSITV

(source_string,  
upper_limit,  
substring, sub_limit,  
output_format)

Finds the starting position of a substring within a larger string and is similar to POSIT.

SUBSTV

(upper_limit,  
source_string, start,  
sub_limit,  
output_format)

Extracts a substring from a string and is similar to SUBSTR.

TRIMV

(trim_where,  
source_string,  
upper_limit, 
pattern,  
pattern_limit,  
output_format)

Removes a pattern from a string and is similar to TRIM.

UPCASV

(upper_limit,  
source_string,  
output_format)

Converts alphabetic characters to uppercase and is similar to UPCASE.

Data Source and Decoding Functions

COALESCE

(expression1, expression2 [,...])

Evaluates the arguments in order, and returns the first argument that is not MISSING.

DB_LOOKUP

(lookupsynonym, 
lookupcolumn1, 
sourcecolumn1,
lookupcolumn2, 
sourcecolumn2 ...  
returncolumn)

Looks up values in a table and returns the value from another column of the row where the first value was found.

For more information on improving lookup performance, see Improving Lookup Performance.

DB_EXPR

(native_SQL_expression)

Inserts a native SQL expression exactly as entered into the native SQL generated for a FOCUS or SQL language request.

DB_INFILE

(target_file, s1, t1, ... sn, tn)

Compares one or more field values in a source file to values in a target file.

DECODE

fieldname(code1
'result1'... [ELSE
default])

Changes a variable to an associated name.

LAST 

fieldname

Retrieves the previous value for a field.

NULLIF

(expression1, expression2)

Returns MISSING if the value of the two arguments are equal. Otherwise it returns the first value.

Date Functions - Legacy

AYM

(indate, months,  
output_format)

Adds or subtracts months from dates stored in alphanumeric or integer format.

AYMD

(indate, days,  
output_format)

Adds or subtracts a given number of days to a date in [YY]YYMMDD format.

CHGDAT

('in_display_options',
'out_display_options',  
date_string,  
output_format) 

Rearranges the year, month, and day portions of an input character string representing a date. It may also convert the input string from long to short or short to long date representation.

DADMY

(indate,  
output_format)

Given the date in day-month-year format, calculates the number of days since 1/1/1900.

DADYM

(indate,  
output_format)

Calculates the number of days since 1/1/1900 given the date in day-year-month format.

DAMDY

(indate,  
output_format)

Calculates the number of days since 1/1/1900 given the date in month-day-year format.

DAMYD

(indate,  
output_format)

Calculates the number of days since 1/1/1900 given the date in month-year-day format.

DAYDM

(indate,  
output_format)

Calculates the number of days since 1/1/1900 given the date in year-day-month format.

DAYMD

(indate,  
output_format)

Calculates the number of days since 1/1/1900 given the date in year-month-day format.

DMY

(from_date, to_date)

Returns the difference between two dates in day-month-year order.

DOWK

(indate,  
output_format)

Returns the day of the week in A4 format.

DOWKL

(indate,  
output_format)

Returns the day of the week in A12 format.

DTDMY

(number,  
output_format)

Returns date in day-month-year format given the number of days since 1/1/1900.

DTDYM

(number,  
output_format)

Given the number of days since 1/1/1900, returns date in day-year-month format.

DTMDY

(number,  
output_format)

Returns date in month-day-year format given the number of days since 1/1/1900.

DTMYD

(number,  
output_format)

Returns date in month-year-day format given the number of days since 1/1/1900.

DTYDM

(number,  
output_format)

Returns date in year-day-month format given the number of days since 1/1/1900.

DTYMD

(number,  
output_format)

Returns date in year-month-day format given the number of days since 1/1/1900.

GREGDT

(indate,  
output_format)

Converts a Julian date to a Gregorian date.

JULDAT

(indate,  
output_format)

Converts a Gregorian date to a Julian date.

MDY

(from_date, to_date)

Returns the difference between two dates in month-day-year order.

TODAY

(output_format)

Returns the current date in alphanumeric format (MM/DD/YYYY).

YM

(from_date, to_date,  
output_format)

Returns the number of days between two dates.

YMD

(from_date, to_date)

Returns the difference between two dates in year-month-day order.

Date Functions - Standard

DATEADD

(date, component_code,  
increment)

Adds or subtracts years, months, or days to or from a date.

DATECVT

(date, in_format,  
out_format)

Converts dates from one date format to another.

DATEDIF

(from_date, to_date,  
component_code)

Calculates the difference between two dates, expressed as years, months, or days.

DATEMOV

(date, 'move-point')

Moves a date to a significant point on the calendar.

DATETRAN

(indate, '(intype)', 
'(formatops)', 'lang',  
outlen, output)

Formats dates in international formats.

Date-Time Functions

DTADD

(date, component, increment)

Adds or subtracts the specified number of calendar or time components to the input date or date-time fields, and returns the new date or date-time value.

DTDIFF

(end_date, start_date, component)

Given two dates in standard date or date-time formats, returns the number of a given component boundaries between the two dates.

HADD

(timestamp, component,  
increment, length,  
output_format)

Increments a date-time field by a given number of units.

HCNVRT

(timestamp,(format),  
length, output_format)

Converts a date-time field to alphanumeric format for use with operators, such as EDIT, CONTAINS, and LIKE.

HDATE

(timestamp,  
output_format)

Extracts the date portion of a date-time field and converts it to a date format.

HDIFF

(end_timestamp,  
start_timestamp,  
component,  
output_format)

Finds the number of boundaries of a given type crossed going from date 2 to date 1.

HDTTM

(date, length,  
output_format)

Converts a date field to a date-time field. The time portion is set to midnight.

HEXTR

(source, 
'componentstring', 
length, output_format)

Extracts components of a date-time value and sets remaining components to zero.

HGETC

(length,  
output_format)

Stores the current date and time in a date-time field.

HHMMSS

(output_format)

Returns the current time in alphanumeric format.

HINPUT

(source_length,  
source_string,  
timestamp_length,  
output_format)

Converts an alphanumeric string to a date-time value.

HMASK

(source, 
'componentstring', 
input, length, 
output_format)

Extracts components of a date-time value and preserves remaining components.

HMIDNT

(timestamp, length,  
output_format)

Changes the time portion of a date-time field to midnight (all zeroes).

HNAME

(timestamp, component,  
output_format)

Extracts a specified component from a date-time field and returns it in alphanumeric format.

HPART

(value, component,  
output_format)

Extracts a specified component from a date-time field and returns it in numeric format.

HSETPT

(timestamp, component,  
value, length,  
output_format)

Inserts the numeric value of a specified component into a date-time field.

HTIME

(length, timestamp,  
output_format)

Converts the time portion of a date-time field to a numeric number of milliseconds (if the first argument is 8) or microseconds (if the first argument is 10).

HTMTOTS

(time, length,  
output_format)

Converts a time to a timestamp.

Format Conversion Functions

EDIT

(fieldname)

Converts between alphanumeric and integer data. Used with a mask, extracts characters from an alphanumeric string and inserts new characters.

FTOA

(number, '(format)',  
output_format)

Converts numbers from decimal format to alphanumeric format.

ITONUM

(maxbytes, infield,  
output_format)

Converts large binary integers in non-FOCUS files to double-precision format.

ITOPACK

(maxbytes, infield,  
output_format)

Converts large binary integers in non-FOCUS files to packed-decimal format.

ITOZ

(length, in_value,  
output_format)

Converts numbers from numeric format to zoned format for extract files.

PTOA

(number, '(format)',  
output_format)

Converts a number from numeric format to alphanumeric format.

TO_INTEGER

(string)

Converts a character string that contains a valid number consisting of digits and an optional decimal point to an integer value.

TO_NUMBER

(string)

Converts a character string that contains a valid number consisting of digits and an optional decimal point to the numeric format most appropriate to the context.

TSTOPACK

(timestamp, 'P2')

Converts an MS SQL Server or Sybase column described as "timestamp", which contains an increasing counter that represents a unique value in the database in hex notation with a format of A16 into a packed decimal number with a format of P21.

Legacy Functions

ASIS

(argument)

Distinguishes between a space and a zero.

ATODBL

(source_string,  
length, output_format)

Converts a number in alphanumeric format to decimal format.

BAR

(barlength, infield, 
maxvalue, 'char', 
output_format)

Produces a bar chart.

BITSON

(bitnumber,  
source_string,  
output_format)

Evaluates an individual bit within a character string to determine whether it is on or off.

BITVAL

(source_string,  
startbit, number,  
output_format)

Evaluates a string of bits within character strings and returns its binary value.

BYTVAL

(character,  
output_format)

Translates a character to its corresponding ASCII code.

CHKPCK

(length, in_value,  
error, output_format)

Validates the data in a field described as packed format.

FIND

(fieldname [AS  
dbfield] IN file);

Verifies if a value exists in an indexed field in another file.

HEXBYT

(decimal_value,  
output)

Converts a numeric value to its corresponding ASCII character.

LOOKUP

field

Retrieves a data value from a cross-referenced FOCUS data source in a MODIFY request.

PCKOUT

(in_value, length,  
output_format)

Writes packed numbers of varying lengths (between one and 16 bytes) to extract files.

REVERSE

(length,  
source_string,  
output_format)

Reverses the input characters.

UFMT

(source_string,  
length, output_format)

Converts input field values to hexadecimal (hex) notation.

Numeric Functions

ABS

(in_value)

Returns the absolute value of its argument.

CEIL

(number)

Returns the smallest integer that is greater than or equal to the number.

DMOD

(dividend, divisor,  
output_format)

Returns the remainder from a division as a number in decimal format.

EXP

(power, output_format)

Raises the number e to a power you specify.

EXPN

(n.nn {E|D} {+|-} p)

Evaluates an argument expressed in scientific notation.

FLOOR

(number)

Returns the largest integer that is less than or equal to the number.

FMOD

(dividend, divisor,  
output_format)

Returns the remainder from a division as a number in floating-point format.

IMOD

(dividend, divisor,  
output_format)

Returns the remainder from a division as a number in integer format.

INT

(in_value)

Returns the integer part of its argument.

LOG

(in_value)

Returns the natural logarithm of its argument.

MAX

(value1, value2, ...)

Returns the maximum value from its list of arguments.

MIN

(value1, value2, ...)

Returns the minimum value from its list of arguments.

MOD

(dividend, divisor)

Calculates the remainder from a division as an integer.

NORMSDST

(value, output_format)

Calculates the cumulative normal standard distribution.

NORMSINV

(value, output_format)

Calculates the inverse cumulative normal standard distribution.

PRDNOR

(seed, output_format)

Generates reproducible random numbers.

PRDUNI

(seed, output_format)

Generates reproducible random numbers.

RDNORM

(output_format)

Generates random numbers.

RDUNIF

(output_format)

Generates random numbers.

SQRT

(in_value)

Returns the square root of its argument.

System Functions

APINPATH

(length, appname, ‘A1’)

Returns Y if application appname is in the path, N if not.

CLSDDREC

(output_format)

Closes all files opened by the PUTDDREC function.

FEXERR

(error, 'A72')

Returns the first line of the error message.

FGETENV

(length, varname,  
outlen, output_format)

Gets the value of an environmental variable and returns it as an alphanumeric string.

FPUTENV

(varname_length,  
varname, value_length,  
value, output_format)

Assigns a character string to an environmental variable.

GETUSER

(output_format)

Retrieves the user ID from the system.

PUTDDREC

(ddname, dd_len,  
record_string,  
record_len,  
output_format)

Writes a character string as a record in a flat file.