ascii | Returns the ASCII value for the first character in str_value. Value type: float Syntax: ascii_var = ascii(str_value) str_value = date or text literal, column, variable, or expression ascii_var = decimal, float, or integer variable
Example: let #fascii = ascii($filename) |
asciic | Returns the numeric value for the first character (rather than byte) of the specified string. Syntax: ascii_var = asciic(str_value) str_value = date or text literal, column, variable, or expression ascii_var = decimal, float, or integer variable
Example: let #fascii = asciic($filename) |
chr | Returns a string composed of a character with the ASCII value of num_value. Syntax: dst_var = chr(num_value) num_value = decimal, float, or integer literal, column, variable, or expression. The value is always converted to float. dst_var = text variable
Example: let $svar = chr(#num) |
edit | Formats source_value according to edit_mask and returns a string containing the result. Syntax: dst_var = edit(source_value, edit_mask) source_value = Any literal, column, variable, or expression edit_mask = text literal, column, variable, or expression dst_var = text variable
Example: let $phone = edit(&phone, '(xxx) xxx-xxxxx') let $price = edit(#price, '999.99') let $today = edit($date, 'DD/MM/YYYY') |
fromhex | Accepts a TEXT variable that contains a string of hexadecimal characters (case insensitive) and returns a BINARY variable. Each byte of BINARY data consists of two hexadecimal characters. Syntax: dst_var = fromhex(source_value) source_value = text literal, column, variable, or expression dst_var = binary variable
Example: let $image = fromhex($hexchars) |
instr | Returns the numeric position of sub_value in source_value or zero (0) if not found. The search begins at offset offset_value. Value type: float Syntax: dst_var = instr(source_value, sub_value, offset_value) source_value = date or text literal, column, variable, or expression sub_value = text literal, column, variable, or expression offset_value = decimal, float, or integer literal, column, variable, or expression. The value is always converted to integer. dst_var = decimal, float, or integer variable
Example: let #offset = instr(&description, 'auto', 10) |
instrb | Performs the same functionality as the instr function except that the starting point and returned value are expressed in bytes rather than characters. Syntax: dst_var = instrb(source_value, sub_value, offset_value) source_value = date or text literal, column, variable, or expression sub_value = text literal, column, variable, or expression offset_value = decimal, float, or integer literal, column, variable, or expression. The value is always converted to integer. dst_var = decimal, float, or integer variable
Example: let #offset = instrb(&description, 'auto', 10) Note: instrb does not allow you to specify the target encoding. If you are using Unicode internally, specify the target encoding with lengthp, lengtht, substrp, substrt, or transform. |
isblank | Returns a value of one (1) if source_val is an empty string, null string, or composed entirely of whitespace characters; otherwise, returns a value of zero (0). Syntax: dst_var = isblank(source_value) source_value = date or text literal, column, variable, or expression (character data type columns only, no numeric data type columns) dst_var = decimal, float, or integer variable
Example: let #blank = isblank(&description) Note: isblank can only be used for character data type columns. |
length | Returns the number of characters in source_value. Syntax: dst_var = length(source_value) source_value = date or text literal, column, variable, or expression dst_var = decimal, float, or integer variable
Example: let #length = length(&description) |
lengthb | Same functionality as length except that the return value is expressed in bytes, rather than characters. Syntax: dst_var = lengthb(source_value) source_value = date or text literal, column, variable, or expression dst_var = decimal, float, or integer variable
Example: let #length = lengthb(&description) Note: lengthb does not allow you to specify the target encoding. If you are using Unicode internally, specify the target encoding with lengthp, lengtht, substrp, substrt, or transform. |
lower | Converts the contents of source_value to lowercase and returns the result. Syntax: dst_var = lower(source_value) Example: let $lower = lower(&description) |
lpad | Pads the source_value on the left to a length of length_value using pad_value and returns the result. Syntax: dst_var = lpad(source_value, length_value, pad_value) source_value = date or text literal, column, variable, or expression length_value = decimal, float, or integer literal, column, variable, or expression. The value is always converted to integer pad_value = text literal, column, variable, or expression dst_var = text variable
Example: let $lpad = lpad($notice, 25, '.') |
ltrim | Trims characters in source_value from the left until a character is not in set_value and returns the result. Syntax: dst_var = ltrim(source_value, set_value) source_value = date or text literal, column, variable, or expression set_value = text literal, column, variable, or expression dst_var = text variable
Example: let $ltrim = ltrim(&description, '.') |
replace | Inspects the contents of source_value and replaces all occurrences of from_string with to_string and returns the modified string. Syntax: dst_var = replace(source_value, from_string, to_string) source_value = date or text literal, column, variable, or expression from_string = text literal, column, variable, or expression to_string = text literal, column, variable, or expression dst_var = text variable
Example: let $replaced = replace($paragraph, ‘good’, ‘excellent’) |
rpad | Pads the source_value on the right to a length of length_value using pad_value and returns the result. Syntax: dst_var = rpad(source_value, length_value, pad_value) source_value = date or text literal, column, variable, or expression length_value = decimal, float, or integer literal, column, variable, or expression. The value is always converted to integer. pad_value = text literal, column, variable, or expression dst_var = text variable
Example: let $rpad = rpad($notice, 25, '.') |
rtrim | Trims characters in source_value from the right until a character is not in set_value and returns the result. Syntax: dst_var = rtrim(source_value, set_value) source_value = date, or text literal, column, variable, or expression set_value = text literal, column, variable, or expression dst_var = text variable
Example: let $rtrim = rtrim(&description, '.') |
substr | Extracts the specified portion source_value. The extraction begins at offset_value (origin is 1) for a length of length_value characters. Syntax: dst_var = substr(source_value, offset_value, length_value) source_value = date or text literal, column, variable, or expression offset_value = decimal, float, or integer literal, column, variable, or expression. The value is always converted to integer. length_value = decimal, float, or integer literal, column, variable, or expression. The value is always converted to integer. dst_var = text variable
Example: let $piece = substr(&record, 10, #len) |
substrb | Has the same functionality as substr except that the starting point and length are expressed in bytes, rather than in characters. Syntax: dst_var = substrb(source_value, offset_value, length_value) source_value = date or text literal, column, variable, or expression offset_value = decimal, float, or integer literal, column, variable, or expression. The value is always converted to integer. length_value = decimal, float, or integer literal, column, variable, or expression. The value is always converted to integer. dst_var = text variable
Example: let $piece = substrb(&record, 10, #len) Note: substrb does not allow you to specify the target encoding. If you are using Unicode internally, specify the target encoding with lengthp, lengtht, substrp, substrt, or transform. |
to_char | Converts source_value to a string, using maximum precision. Syntax: dst_var = to_char(source_value) source_value = decimal, float, or integer literal, column, variable, or expression dst_var = text variable
Example: let $string = to_char(#number) |
tohex | Accepts a BINARY variable and returns a string composed of uppercase hexadecimal characters that represents the data. Each byte of BINARY data consists of two hexadecimal characters. Syntax: dst_var = tohex(source_value) Example: let $hexchars = tohex($vargraphic) |
to_multi_byte | Converts the specified string as follows: any occurrence of a single-byte character that also has a multi-byte representation (numerals, punctuation, roman characters, and katakana) is converted. Syntax: dst_var = to_multi_byte (source_value) Example: let $multi = to_multi_byte (&text) |
to_number | Converts source_value to a number. Value type: float Syntax: dst_var = to_number(source_value) source_value = decimal, float, or integer literal, column, variable, or expression dst_var = decimal, float, or integer variable
Example: let #value = to_number($number) |
to_single_byte | Converts the specified string as follows: any occurrence of a multi-byte character that also has a single-byte representation (numerals, punctuation, roman characters, and katakana) is converted. This function also converts a sequence of kana characters followed by certain grammatical marks into a single-byte character that combines the two elements. For all other encodings, the string is not modified. Note: If you are running Production Reporting without the use of Unicode (UseUnicodeInternal=FALSE in SQR.INI), this conversion only occurs when the database encoding (ENCODING-DATABASE setting in SQR.INI) is set to SJIS, EBCDIK290, and EBCDIK1027. Syntax: dst_var = to_single_byte (source_value) Example: let $single = to_single_byte (&text) |
translate | Inspects the contents of source_value and converts characters that match those in from_set to the corresponding character in to_set and returns the translated string. If to_set does not contain a matching translation character in the corresponding from_set, then the original is left unchanged with regard to that character. If the translation string in to_set is empty, then all characters specified in the from_set string are removed. Syntax: dst_var = translate(source_value, from_set, to_set) source_value = date or text literal, column, variable, or expression from_set = text literal, column, variable, or expression to_set = text literal, column, variable, or expression dst_var = text variable
Example: let $translated = translate(edit(&price, '999,999.99'), ',.', '.,') |
upper | Converts the contents of source_value to uppercase and returns the result. Syntax: dst_var = upper(source_value) Example: let $upper = upper(&description) |