Westclintech

Getting started

XLeratorDB/String Functions Documentation

FUNCTION REFERENCE - String Functions FUNCTIONS

Formatting

CLEAN

Remove the first 32 nonprinting characters in the 7-bit ASCII code (values 0 through 31) from text.

CONCAT

Concatenate the contents of 2 columns.

CRLF

Insert a Carriage Return Char(13) and a Line Feed Char(10) into a string.

DEC2FRAC

Convert a number into its fractional representation.

DOLLAR

Convert a number to text using currency format, with the decimals rounded to the specified place.

FIXED

Format a number in decimal format using a period and commas, round the number to the specified number of decimals, and return the result as text.

FORMATDATE

Convert a datetime value to text and specify the display formatting by using special format strings.

FORMATNUM

Convert a numeric value to text and specify the display formatting by using special format strings.

FRAC2DEC

Convert a fraction, as text, to a numeric value.

INDENT

Place characters at the beginning of a string of text.

JOINSTR

Concatenate the text from multiple rows in a table.

JOINSTR_q

Concatenate the text from multiple rows in a table.

PADLEFT

Place characters at the beginning of a string of text, when the length of the text is less than the specified width.

PADRIGHT

Place characters at the end of a string of text, when the length of the text is less than the specified width.

PARSE

Find a word in a delimited string based on its position in the string.

PCT2FRAC

Convert a percentage into its fractional representation.

PROPER

Capitalize the first letter in each word of a string of text.

QUOTED

Add quotation marks, CHAR(34), to the beginning and end of a string of text.

QUOTES

Add quotation marks, CHAR(39), to the beginning and end of a string of text.

SPLIT

Generate a single column table containing the substrings between the specified delimiter passed to the function.

STRIP

Remove punctuation from the beginning and end of a string.

SUBSTITUTE

Substitute new_text for old_text in a text string.

TEXT

Convert a numeric value to text and specify the display formatting by using special format strings.

TRIM

Remove trailing and leading spaces from a string of text.

VALUE

Convert a text string that represents a number to a number.

Inspection

CONTAINSSTR

Determine if a string contains a particular value.

COUNTSTRING

Count the number of times a particular text occurs in another text.

ENDSWITH

Determine if a string ends with a particular value.

EXACT

Compare two text strings.

FIND

Locate one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string.

INSTR

Return an integer specifying the position of the first occurrence of one string within another.

INSTRREV

Return an integer specifying the position of an occurrence of one string within another, from the end of string.

ISALPHA

Determine if a value contains only alphabetic characters [a-z][A-Z].

ISNUMERIC

Determine if a value is numeric.

NUMWORDS

Calculate the number of words in a string.

STARTSWITH

Determine if a string starts with a particular value.

Logic

IIFSTR

Return one of two values, depending on the evaluation of an expression.

Calendar

NETWORKDAYS

Calculate the number of working days between two dates (inclusive)

NETWORKDAYS_q

Calculate the number of whole working days between a start date and an end date.

WORKDAY

Calculate the date that is the indicated number of working days before or after a date (the starting date).

WORKDAY_q

Calculate the date that is the indicated number of working days before or after a date (the starting date).