Skip to main content

OCONV

Converts a value from internal format to external format.

Synopsis

OCONV(istring,code)

Arguments

istring An expression that resolves to a string or integer. It specifies a value represented in internal (storage) format.
code An expression that resolves to a conversion code string. This conversion code specifies the type of conversion to perform. Conversion is from internal format to external format. Conversion codes are not case-sensitive. For a complete list of conversion codes, refer to the Conversion Codes table in the MultiValue Basic Quick Reference.

Description

The OCONV function is a general-purpose conversion function used to convert from internal (storage) format to external (output) format. The type of conversion is specified by a code string that is specific to the type of data to be converted.

The following types of conversions are supported:

  • Character conversions: character-to-hexcode, hexcode-to-character.

  • Numeric conversions: decimal-to-hex, hex-to-decimal, masked decimal conversion, range extraction, zero and non-zero substitution.

  • String Conversions: case conversion, character type extraction, Soundex conversion, string length conversion, uniform string length adjustment, substring extraction by length, delimited substring extraction, pattern match extraction, extraction of strings of a specified length.

  • Time and Date Conversions: time internal-to-display format, date internal-to-display format, date display-to-internal format, date element extraction, day-of-week, day-of-year, and quarter calculation.

  • Arithmetic and Logical Operations: arithmetic, equality comparisons, collation sequence comparisons, date and time arithmetic.

  • String Concatenation: concatenation, concatenation with inserted single-character delimiters.

  • Dynamic Array Element Extraction: extracting an element from a dynamic array by position.

You can use the STATUS function to determine the success of an OCONV conversion.

The OCONV function converts from internal format to external format. The ICONV function converts from external format to internal format. Note that the MCDX/MCXD, MCAX/MCXA, and MCWX/MCXW code pairs have the opposite meanings in ICONV, reversing the OCONV operation.

You can use the OCONVS function to convert the elements of a dynamic array from internal format to external format.

Invalid Values

In most cases, if you specify an istring value that cannot be converted, OCONV returns the istring value unchanged.

  • If you supply a non-numeric value to a numeric operation: date, time, and masked decimal conversions return the istring value unchanged. However, "DI" returns -46384. "MCX" and the other decimal/hex conversion return 0 (unless the first character(s) of the non-numeric string are viewed as the hexadecimal digits A through F.)

  • If you supply a non-alphabetic value to an alphabetic operation: case conversions return the istring value unchanged. Soundex conversion returns 0000.

If you specify a code value that is valid but is not implemented, OCONV generates an [806] error. If you specify a nonexistent code value, OCONV generates an [850] error.

Character Conversion

The following are the conversion codes for character conversions:

Character-to-code conversion: all characters in the istring input string are converted to their corresponding hexadecimal integer codes. Use "MCAX", "MX", or “MX0C” to output 8-bit code values. Use "MCWX" to output 16-bit (wide) code values.

"MCAX", "MX", “MX0C

"MCWX"

Code-to-character conversion: one or more hexadecimal codes in the istring input string are converted to their corresponding characters. You can specify the hexadecimal letters A-F in uppercase or lowercase. Use "MCXA" or "MY" for two-byte code input values. Use "MCXW" for four-byte (wide) code input values.

"MCXA", "MY"

"MCXW"

Numeric Conversion

The following are the conversion codes for numeric conversions:

Decimal-to-hexadecimal conversion. "MCD" / "MCDX"
Hexadecimal-to-decimal conversion. "MCX" / "MCXD"
Masked decimal conversion: Shifts the decimal point of a numeric, sets the number of fractional digits, optionally limits the number of characters of the result to return, and optionally performs other number formatting, such as converting a minus sign or appending a currency symbol. The m integer value specifies the shift of the decimal point, with a positive value shifting the decimal point to the left, and with 0 indicating no shift; left side zero padding is added as needed. The n integer value specifies the number of fractional digits; rounding (by default) and zero padding are performed as needed. If m is omitted, it defaults to the same value as n. The k integer specifies how many characters of the result to return. The x modifiers are non-numeric character codes for formatting results; you can specify multiple x codes. For x code values, see below. OCONV masked decimal conversion is the reverse of the ICONV masked decimal conversion.

"MD[n[m[k]]][x]"

"ML[n[m[k]]][x]"

"MR[n[m[k]]][x]"

Range extraction: returns istring if it falls within the numeric range n and m (inclusive). Otherwise, returns an empty string. istring can be any numeric value; n and m must be positive integers. You can specify multiple range pairs, separating them with a semicolon (;) or slash (/). For further details, see below. "Rn,m[;n,m]"
Zero and non-zero substitution: If istring is a non-zero value (either numeric or string), returns the nval literal. If istring is a zero value (0 or the empty string), returns the zval literal. nval and zval must both be specified (separated by semicolons) as either a quoted string or as an asterisk. You can substitute an asterisk (*) for either nval or zval; when * is specified, the istring literal is returned for that condition, rather than a substitute value. "S;nval;zval"

Hexadecimal / Decimal Conversion

“MCD” and “MCDX” convert a positive decimal integer to a hexadecimal number. Hexadecimal numbers A through F are returned in upper case. A fractional number is truncated before conversion to hexadecimal. A mixed numeric string is parsed as an integer until the first non-numeric value is encountered, at which point it is truncated and the resulting integer converted to hexadecimal. A negative number is converted to a high-order hexadecimal value; for example -3 is converted to FFFFFFFFFFFFFFFD. A non-numeric string returns 0.

“MCX” and “MCXD” convert a hexadecimal number to a positive decimal integer. The hexadecimal letter A-F are not case-sensitive. A fractional number is truncated before conversion to a decimal integer. A mixed numeric string is parsed as a hexadecimal number until the first non-hexadecimal value is encountered, at which point it is truncated and the resulting hexadecimal string is converted to a decimal integer. A negative number returns 0. A non-numeric string returns 0.

Masked Decimal Conversion / Currency Conversion

“MD”, “ML”, and “MR” convert an integer by moving the decimal point, specifying the number of fractional digits, and (optionally) inserting a currency symbol or other numeric format characters. For example, to convert the stored integer 123456 to a displayed dollar currency value, you could use the conversion code "MD22$,", which would result in the value $1,234.56.

“MD” removes leading zeros. “ML” (left justification) and “MR” (right justification) retain leading zeros.

The istring is commonly an integer. If istring is a fractional number, it is rounded to an integer before applying masked decimal conversion. istring can contain the numbers 0–9, a leading minus sign, a decimal point, and numeric group separators (commas). All other characters are considered non-numeric, and cause OCONV to return istring unchanged. Note that a leading plus sign (+) or a dollar sign ($) is considered a non-numeric character and prevents conversion.

The istring value is rounded to an integer, and then the n and m integer arguments are applied:

  • n specifies the number of fractional digits in the result. A positive integer in the range 0 through 9 (inclusive) sets the number of fractional digits. The n argument is optional; the default is 0. If n is omitted, m is also omitted and defaults to 0.

  • m specifies how many places (power of ten) to the left to move the decimal point. If m is omitted, it is assigned the same value as n.

Zero padding is added where needed.

This use of n and m is shown in the following examples:

PRINT OCONV("0123.57","MD")     ! returns 124: n defaults to 0, m defaults to n
PRINT OCONV("0123.57","MD00")   ! returns 124: same as above
PRINT OCONV("0123.57","MD2")    ! returns 1.24: n=2 fractional digits, m defaults to n (2 places left)
PRINT OCONV("0123.57","MD22")   ! returns 1.24: same as above
PRINT OCONV("0123.57","MD21")   ! returns 12.36: n=2 fractional digits, m moves decimal 1 place left
PRINT OCONV("0123.57","MD02")   ! returns 1: n=no fractional digits, m moves decimal 2 places left

If istring is the null string (""), “MD” conversions always returns the null string. “ML” and “MR” conversions treats the null string as zero and apply the specified numeric conversions. This null string behavior is emulation-dependent:

  • Caché, IN2, INFORMATION, PICK, PIOpen, Reality, Universe: treat the null string ("") as zero for numeric conversions. Numeric conversions are applied.

  • D3, jBASE, MVBase, R83, POWER95, Ultimate, UniData: treat the null string ("") as null, and return the null string for numeric conversions.

Formatting Codes: x

The following optional x codes can be specified following the optional n and m codes:

  • T: truncate the results of applying the n and m codes. The default is to round the results. Thus by applying "MD23T" "123456" becomes "123.45"; by applying "MD23" "123456" becomes "123.46".

  • P: if istring already contains a decimal point, the decimal point is retained and the m value is not applied. Thus by applying "MD22P" "123456" becomes "1234.56", but "12.3456" becomes "12.35". If "P" is not specified, "MD22" causes "12.3456" to become "0.12".

  • - (minus sign) or M: moves the minus sign from leading to trailing. Thus by applying "MD22-" "-123456" becomes "1234.56-". Positive numbers are unaffected by this code character.

  • N: removes the minus sign from a negative number. Has no effect on a positive number.

  • C (credit) or D (debit): When x=C positive numbers are unaffected, negative numbers lose their minus sign and take a CR suffix. When x=D positive numbers take a DB suffix, negative numbers lose their minus sign. Thus by applying "MD22D" "123456" becomes "1234.56DB" and "-123456" becomes "1234.56". Specifying lowercase "c" or "d" results in a corresponding lowercase cr or db suffix.

  • < (left angle bracket) or E: delimits a negative value with angle brackets. Thus by applying "MD22<" "-123456" becomes "<1234.56>". Positive numbers are unaffected by this code character.

  • $ (dollar sign) : appends a dollar sign to the conversion result. Thus by applying "MD22$" "123456" becomes "$1234.56" and "-123456" becomes "$-1234.56". If supported by the locale, the F (franc), I (international), and Y (yen) currency symbols are applied; if not supported by the locale, these are synonyms for $.

  • , (comma): inserts numeric group separators. Thus by applying "MD22," "123456" becomes "1,234.56" and "-123456" becomes "-1,234.56".

    If istring already contains one or more commas, these conversion codes handle existing commas as follows: a comma in istring is treated as a digit when applying n and m codes. After applying n and m, the conversion removes all commas to the left of the decimal point. Then, if x is a comma, the conversion adds commas as numeric group separators where needed.

  • Z: zero converted to empty string. If istring has a zero value, the Z conversion code returns the empty string rather than zero. Thus by applying "MD22Z" the istring values "0", "0.00", or "-000" all become the empty string. However, note that Z is applied before n and m processing; thus applying "MD22Z" to "0.1" results in "0.00". Appending a “Z” character code causes “ML” and “MR” conversions to return an empty string when istring is the empty string.

The E, M, N, P, T, and Z letter codes are not case-sensitive.

Multiple x code suffixes can be combined in any order. For example, "MD22$-," appends a dollar sign to the decimal fraction, moves any existing minus sign to the trailing position, and inserts numeric group separators where needed. Thus OCONV(-123456,"MD22$-,") returns "$1,234.56-". The x codes are applied in left-to-right order. Therefore, if multiple x code suffixes conflict (for example, C, <, and –), the last (rightmost) x code is the one applied.

Numbers of Characters to Return: k

You can optionally specify how many characters of the masked decimal conversion result to return. For “MD” and “MR” these are the rightmost characters of the conversion result. For “ML” these are the leftmost characters of the conversion result.

The number of characters to return (k) can be specified in several ways:

  • As a third positive integer value following n and m. For example, “MD224” specifies three integers: n the number of fractional digits; m the number of places to move the decimal point to the left; and k the number of characters of the result to display. Unlike n and m, k cannot be 0, but can be an integer larger than 9. Thus by applying "MD22" "123456" becomes "1234.56" and by applying "MD224" "123456" becomes "4.56". Note that the decimal point is counted as a character.

    In this syntax, n, m, and k must be explicitly specified. x codes should not be specified.

    For “MD” and “MR”, the k characters are counted from right to left. For “ML” the k characters are counted from left to right. If k exceeds the number of characters in the result, the result is padded with blank spaces.

  • As a multi-character x code, the first character of which is a #, %, or * character, followed by the k integer value. This code can be specified with or without delimiting parentheses. Thus "MD224", "MD22#4", and "MD22(#4)" return the same results.

    This syntax does not require explicit n and/or m values. Thus "MD#4", "MD(#4)", and "MD004" return the same results.

    This syntax allows you to apply x codes before or after selecting the character subset. Code characters are applied in left-to-right order. Therefore, any x codes specified to the left of the k code are included in the k count. x codes to the right of the k code are applied after the k count. Thus by applying "MD22-#3" "-123456" returns the three characters "56–"; the trailing minus sign is applied before the k count. Applying "MD22#3-" to "–123456" returns the four characters ".56–"; the trailing minus sign is applied after the k count.

    You can add a string suffix to the returned numeric string. Use the following syntax, with or without the enclosing parentheses: MDnm (#kstring), where string is one or more non-numeric characters. For example, to add the string suffix “salary” separated from the numeric by a single space: "MD22$(#8 salary)" or "MD22$#8 salary".

    You can add a fill character suffix to the returned numeric string. Use the following syntax, with or without the enclosing parentheses: MDnm (#kcharr), where char is a single non-numeric character and r is an integer repetition count. For example, to add the fill character suffix “^^^^^” to the numeric: "MD22$(#8^5)" or "MD22$#8^5".

    You can add multiple string suffixes and fill character suffixes. For example, to add the suffix ^^^^URGENT^^^^, you would specify "MD22$(#8^4URGENT^4)".

Range Extraction

The "Rn,m" code extracts positive numeric values within the specified range (inclusive). The n and m values specify the bounds of the range. They must be positive integers; 0 is a permitted value. A range may be in ascending or descending order (ascending is preferable when specifying multiple ranges). The n and m values may be the same value. If a number is within the range (inclusive of the n and m values) it is returned. If a number is not within the range, the empty string is returned. Signed numbers are returned with their sign; however, the only negative number that can be returned is –0. Fractional numbers are evaluated as being larger than their integer (they are neither rounded nor truncated); thus 1.9 is not within the range 2,4 or in the range 0,1 but is within the range 1,2. An istring containing non-numeric characters is parsed as 0.

You can specify multiple ranges by separating each range pair with either a semicolon or a slash. Thus "R2,4;8,10" is a valid range which will return the integers 2, 3, 4, 8, 9, or 10. When specifying multiple ranges, each successive range must start at a number equal to or greater than the low value of the previous range. Thus "R2,4;6,10", "R2,4;3,6", or "R2,4;2,6" are valid range codes. "R2,4;1,6" is not a valid range code; only the first range is parsed as a range; the lower number of the second range is parsed, extending the first range downward. Thus "R2,4;2,6" returns 2, 3, 4, 5, or 6; "R2,4;1,6" returns 1, 2, 3, or 4.

Masked String Conversion

The following are the codes for string and numeric conversions:

Case conversion: converts the case of alphabetic characters in istring; has no effect on non-alphabetic characters. "MCL" converts uppercase letters to lowercase. "MCU" converts lowercase letters to uppercase.

"MCL"

"MCU"

Title case conversion: converts the initial letter of each word to uppercase, other letters converted to lowercase. The first letter following an apostrophe is also converted to uppercase, unless that letter is followed by a blank or other non-letter character (thus “O’Brian’s Account”, “Three O’Clock”). "MCT" has no effect on non-letter characters. "MCT"
Mask Character Alphabetic: converts istring by removing all non-alphabetic characters, returning only the alphabetic characters. The inverse is “MC/A” which removes all alphabetic characters, returning only the non-alphabetic characters. “MCA”
Mask Character Both Alphabetic and Numeric: converts istring by removing all punctuation characters, returning only the alphabetic and numeric characters. The inverse is “MC/B” which removes all alphabetic and numeric characters, returning only the punctuation characters. “MCB”
Mask Character Numeric: converts istring by removing all non-numeric characters, returning only the number characters 0 through 9. (Note that plus and minus signs and the decimal point are removed.) The inverse is “MC/N”, which removes all number characters (0 through 9), returning only non-numeric characters. “MCN”
Non-printable character conversion: converts istring by replacing each non-printable character with a period (.). It returns the resulting string of printable characters and periods. “MCP”
Soundex conversion: represents the istring alphabetic string with a four-character Soundex representation. For further details, refer to the SOUNDEX function. "S"

Length conversion: "L" or "L0": returns the number of characters in istring.

"Ln": returns the value of istring if n is exactly the number of characters in istring. Otherwise, returns the empty string. n must be a positive non-zero integer.

"Ln-m" or "Ln,m": returns the value of istring if the number of characters in istring is in the range n through m (inclusive). Otherwise, returns the empty string. n can be specified as zero in this syntax.

"L" or "L0"

"Ln"

"Ln-m" or "Ln,m"

Uniform string length adjustment: returns istring with trailing padding characters and/or text mark (@TM) insertions. n is an integer specifying the desired uniform string length and x is a single non-numeric padding character (for example, "7#" converts istring to one or more strings each string being 7 characters long). If n is larger than the length of istring, OCONV pads istring with x characters to a total length of n characters. If n is smaller than the length of istring, OCONV inserts @TM delimiters every n characters, optionally padding with x so that all delimited substrings are the same length. If n is the same as the length of istring, istring is returned unchanged. "nx"
Text substring extraction: a substring is extracted from istring based on a start position integer and a length integer. The start position integer is optional: if start is specified, length is counted left-to-right from that position; if start is omitted, length is counted right-to-left from the end of the string. If specified, start must be positive integer 1 or greater. length must be positive integer 0 or greater. If start exceeds the length of istring, the empty string is returned. If start is not specified and length equals or exceeds the length of istring the whole string is returned. If start is specified and length equals or exceeds the length of the istring counting from the start point, the substring from start to the end of the string is returned. "T[start,]length"
Group (delimited substring) extraction: a substring is extracted from istring, based on a specified delimiter character (d) found in istring that indicates the stopping point. The optional s integer specifies the number of delimiters to skip from the beginning of the string before starting the extract. The default is to start at the beginning of the string. The n integer specifies the number of delimiters to count in performing the extract. If n is larger than the number of d delimiters, the extract continues to the end of the string. "G[s]dn"

Pattern match extraction: returns an istring if it matches the pattern code; otherwise returns the empty string. A pattern code consists of a series of integer/letter pairs. The integer specifies the number of sequential characters to match with a specified character type; a 0 means to match any number (including 0) of characters. The letter specifies the character type: A=alphabetic, N=numeric, X=alphanumeric. These codes are not case-sensitive. You can also specify literal characters in the pattern code string. Some literals, including numbers and parentheses, must be specified enclosed in single quotes. (The MATCH operator provide similar pattern matching support.)

You can specify multiple patterns, separated by a semicolon (;) or a slash (/). Each pattern is enclosed in parentheses. OCONV returns the istring if it matches any of the specified patterns. For example, the following returns dates with either a one-digit or a two-digit month: OCONV('06/11/2010',"P(2N/2N/4N);(1N/2N/4N)")

"P(pattern)[;(pattern2)]"

Group (Delimited Substring) Extraction

You can extract a substring from istring based on a specified non-numeric delimiter character (d) found in istring. This delimiter is the stopping point for the extract operation. This delimiter cannot be a number or a dynamic array level delimiter character (@VM, @FM, etc.). The G conversion code extracts a substring until it encounters the specified delimiter character. The delimiter is not included in the extracted string. If the specified delimiter is not found in istring, the entire string is returned, unless you have specified a non-zero value for the optional s (skip) argument.

The optional s (skip) integer specifies the number of d delimiters to skip from the beginning of the string before starting the extract. The default is 0 (zero) which means to start extraction at the beginning of the string. If the specified delimiter is not found in istring and the optional s (skip) argument is specified as a non-zero integer value, the empty string is returned. If s is larger than the number of delimiters in istring the empty string is returned.

The n integer specifies the number of delimiters to count when extracting the substring. Substring extraction begins at the starting point established by s and continues until the specified number of delimiters is reached. If n is 1, the extract stops when the first delimiter is encountered. If n is 2, the extract stops when the second delimiter is encountered. Intermediate delimiters are included in the substring, but the final delimiter is not. If n is larger than the number of delimiters, the extract continues to the end of the string. If n is 0, the empty string is returned.

Time and Date Conversion

The following are the conversion codes for time and date conversion. If you specify optional code characters for date or time formatting, these characters must be specified in the order described below.

Internal dates are specified as the number of days elapsed since December 31, 1967. Dates prior to this are specified using a negative number of days. Internal times are specified as the number of seconds elapsed since midnight. Permitted values are 0 (00:00:00) through 86399 (23:59:59); higher numeric values result in an <ILLEGAL VALUE> error. OCONV accepts, but truncates, fractional seconds.

Time conversion from internal format to display format. "MT" returns the local time in 24–hour format hh:mm. You can append one or more of the following optional codes (in the following order): H=12 hour clock with AM or PM suffix. P=12 hour clock with AM or PM prefix. S=include seconds. Z=suppress leading zero from hour integer. A single character to be used as the time separator character, replacing the default colons. AM and PM letters are always displayed uppercase.
Date conversion from internal format to display format. "D" returns the local date in the format dd MMM yyyy, where MMM is the three-letter abbreviation for the month name. You can append one or more optional codes in the following sequence: The integers 0, 2, or 4 to specify the number of year digits (the default is 4). A character (such as /, –, or a space) to specify the date separator character; this cause the date to be displayed in an all-numeric format: mm/dd/yyyy. The Z=zero suppress code for leading zeros. The E=European format code for all-numeric format returns dates in European format dd/mm/yyyy, regardless of your locale (the default is to use the all-numeric date format for the locale). The letter L, to specify that abbreviated month names are displayed in mixed case (the default is all uppercase).
Date conversion from internal format to ODBC display format. "DS" returns the date in the format yyyy-mm-dd. "DMI" returns the date in the format yyyy mm dd. You can optionally specify the integers 2 or 4 to specify the number of year digits (the default is 4).
Date conversion from external format to internal format. "DI" converts a date in display format to internal format. The input date can be in any American all-numeric display format (with optional 2–digit or 4–digit years) or ODBC date format with 4–digit years. If you omit the year portion of the date, "DI" conversion assumes the current year. Refer to the ICONV function for further details on external to internal conversions.
Day extraction: The day of the month is extracted from a date specified in internal format. "DD" returns the day of the month as a two-digit integer (08). "DDM" returns the day of the month and the month as two integers, separated by a space (August 6 = 06 08). Appending a Z to the code ("DDZ" or "DDMZ") suppresses the leading zero for the day integer.
Month extraction: The month is extracted from a date specified in internal format. "DM" returns the month as a two-digit integer (08). "DMA" returns the full name of the month in uppercase letters (AUGUST). "DMB" returns the 3–letter abbreviation for the month in uppercase letters (AUG). Appending an L to the code ("DMAL" or "DMBL") returns the month name in mixed case letters. However, letter case of month names and abbreviations is emulation-dependent (see below). "DMR" returns the month as a Roman numeral. Appending a Z to a code that returns the month as an integer suppresses the leading zero for the month integer.
Day of week extraction: The day of the week is extracted from a date specified in internal format. "DW" returns the day of the week as an integer, counting Monday as day 1. "DWA" returns the full name of the day in uppercase letters (SUNDAY). "DWB" returns the 3–letter abbreviation for the day in uppercase letters (SUN). Appending an L to the code ("DWAL" or "DWBL") returns the day name in mixed case letters. However, letter case of day of week names and abbreviations is emulation-dependent (see below).
Year extraction: The year is extracted from a date specified in internal format. "DY" (or "DN") returns the year portion of the date (2004). "DY2" returns the year portion as a 2-digit date (04). "DYA" and "DYAL" return the Chinese zodiac animal corresponding to the year.
Days of year extraction: The number of elapsed day in the year is extracted from a date specified in internal format. "DJ" returns the elapsed days in the year as a 3-digit integer. "DJZ" suppresses the leading zero(s) for the elapsed days integer.
Quarter / Season extraction: The quarter of the year is extracted from a date specified in internal format. "DQ" returns the quarter of the year as an integer (Jan.-Mar. = 1). "DQA" returns the name of the season in upper case, with Q1 = WINTER. "DQAL" returns the name of the season in mixed case (Winter).

Date Extraction

Date extraction codes can be combined, with the date components displayed in the order specified, separated by a space, and the L or Z appended codes affecting the first specified component. For example, "DMD", "DMAD", "DMBD", "DMADL", and "DMBDL" return the month followed by the day integer. "DMY" returns the month and the year as two integers. "DMAY", "DMAYL", "DMBY", and "DMBYL" return the name of the month and the year. "DMJ" returns the month and the elapsed days in the year as two integers. "DMW" returns the month and the day of the week as two integers. "DWD" returns the day of the week and the day of the month. "DWJ" returns the day of the week and the elapsed days in the year as two integers.

Caché MVBasic displays month names, day of week names, and their abbreviations as either all uppercase ("DMA", "DWA") or lowercase with the first letter capitalized ("DMAL", "DWAL"). In D3, MVBase, R83, POWER95, Ultimate, and UniData emulations, these names and abbreviations are always displayed as lowercase with the first letter capitalized.

Date Conversion

You can display a date in any of the following formats:

  • Abbreviated month format, as shown in the following examples:

    OCONV('16000','D')   returns 21 OCT 2011
    OCONV('16000','D2')  returns 21 OCT 11
    OCONV('16000','DL')  returns 21 Oct 2011
    OCONV('16000','D2L') returns 21 Oct 11
    
  • Numeric format. The default day/month order is determined by the current Caché locale. The following examples use American format as the locale default:

    OCONV('16000','D/')   returns 10/21/2011
    OCONV('16000','D2/')  returns 10/21/11
    OCONV('16000','D/E')  returns 21/10/2011
    OCONV('16000','D2/E') returns 21/10/11
    
  • ODBC format, as shown in the following examples:

    OCONV('16000','DS')   returns 2011-10-21
    OCONV('16000','DS2')  returns 11-10-21
    OCONV('16000','DMI')  returns 2011 10 21
    OCONV('16000','DMI2') returns 11 10 21
    
Note:

You can specify the default date format using Caché NLS. Because of operational differences between MV and Caché NLS in the handling of month names, your NLS default date format must represent months as integers.

You can use the DATE function to supply the current date in internal format. The DATE and TIME functions return internal format values. The TIMEDATE function returns external format values.

An internal date is an integer count of days, with 0 representing December 31, 1967. If you specify a fractional number for an internal date, it is truncated to an integer. If you specify a non-numeric value for an internal date, is returned unchanged.

Dates earlier than December 31, 1967 can be represented using negative numbers. The largest permitted internal date is 2933628, which represents December 31, 9999. The smallest permitted internal date is -46385, which represents December 31, 1840.

The expansion of two-digit years to four digits is governed by the MultiValue CENTURY.PIVOT verb, described in Operational Differences Between MultiValue and Caché.

Arithmetic and Logical Operations

The 'A' conversion code can be used for a variety of operations, including arithmetic, equality comparison of numbers or strings, collation order comparison, current date and time arithmetic or equality comparison, and string concatenation. The 'A' conversion code is followed by its operands; 'A' is separated from its operands by either a blank space or a semicolon.

All 'A' operations ignore the istring value, and operate on the values following the 'A' code. For example, an addition operation is performed as follows:

  PRINT OCONV(123,"A '5'+'6'");   ! returns 11

Note that the istring value must be present but is not used; it could just as easily be an alphabetic string or an empty string placeholder. Also note that numeric literals must be enclosed by delimiters; you may use double quotes ("), single quotes ('), or backslashes (\) to enclose the code string. Within the code string you may use double quotes (") or single quotes (') as literal delimiters.

'A' conversion code operations only process one multivalue at a time. When using 'A' with a multivalue, you must set up the following @ variables: @RECORD = item to get data from; @ID = item Id to use for attribute 0; @NV = specific multivalue to use; @NS = specific subvalue to use.

The following are supported arithmetic and logical operators:

+ Addition: "A 'n'+'m'"
- Subtraction: "A 'n'-'m'"
* Multiplication: "A 'n'*'m'"
/ Division: "A 'n'/'m'". Division is integer division only; neither fractions nor a remainder are returned. Dividing a number by 0 returns 0.
R() Remainder: "A R('n','m')". Returns the remainder of dividing n by m as an integer or fractional number. Attempting to divide by zero results in a <DIVIDE> error.

=

EQ

Equal to: "A 'n'='m'". Returns either 0 (not equal) or 1 (equal). Can compare numeric or non-numeric strings for equality. Numbers are compared in canonical form; plus signs and leading and trailing zeros are ignored.

#

NE

Not Equal to: "A 'n'#'m'". Returns either 0 (equal) or 1 (not equal). Can compare numeric or non-numeric strings for equality. Numbers are compared in canonical form; plus signs and leading and trailing zeros are ignored.

<

LT

Less Than: "A 'n'<'m'". Returns either 0 or 1. Compares collation sequence of non-numeric strings.

>

GT

Greater Than: "A 'n'>'m'". Returns either 0 or 1. Compares collation sequence of non-numeric strings.

<=

LE

Less Than or Equal to: "A 'n'<='m'". Returns either 0 or 1. Compares collation sequence of non-numeric strings.

>=

GE

Greater Than or Equal to: "A 'n'>='m'". Returns either 0 or 1. Compares collation sequence of non-numeric strings.

By default, Caché MVBasic order of operations is to perform division, then multiplication, then subtraction, then addition, then equality/inequality tests. You can change this order of operations by using parentheses to nest operations. Note that ObjectScript uses a different order of operations; it uses strict left-to-right evaluation of operators.

You can use the AND and OR logical operators to group multiple equality/inequality operations.

  PRINT OCONV(123,"A '5'='6' OR '3'<'7'");   ! returns 1

You can use IF, THEN, and ELSE functions with an equality/inequality operation to return a user-specified result. You must specify a THEN function (boolean 1). The ELSE function is optional; if ELSE is omitted, boolean 0 returns the empty string.

  PRINT OCONV(123,"A IF('5'='6') THEN('equal') ELSE('not equal')");   ! returns 'not equal'

  PRINT OCONV(123,"A IF('5'='6') THEN('equal')");   ! returns empty string

You can use the D and T variables to specify the current date or time in arithmetic or logical operations. Dates and times are represented in internal format as integers:

  PRINT OCONV(123,"A D+'7'");   ! returns the current date plus 7 days

This example returns a value such as 15622.

Caché MVBasic provides additional operators, as described in the Operators page of this manual.

String Concatenation and Delimiter Insertion

You can use either the 'A' code or the 'C' code for string concatenation. These codes can concatenate multiple substrings into a single string. In both cases, the concatenation operands follow the letter code.

'A' Code Concatenation

'A' is used for simple concatenation, using the colon character. For example, the following returns ‘quickbrownfox’:

PRINT OCONV(123,"A;'quick':'brown':'fox'")

Although the istring is required, it is always ignored by the 'A' conversion code. The istring can be any value, including an empty string.

'C' Code Concatenation with Insertion

'C' is used for concatenation that inserts a single-character delimiter. For example, the following returns ‘quick^brown^fox’:

PRINT OCONV(123,"C;'quick'^'brown'^'fox'")

'C' is also used for concatenation that inserts the istring value as specified by the asterisk wildcard.

The character following the 'C' code must be a semicolon; you cannot separate 'C' from its operands with a blank space.

Although the istring is required, the 'C' code ignores the istring value unless you specify an asterisk wildcard (as described below). The istring can be any value, except an empty string.

The 'C' code can specify the same single-character delimiter, or different single-character delimiters between items. For example, the following returns ‘quick^brown*fox’:

PRINT OCONV(123,"C;'quick'^'brown'*'fox'")

You can specify almost any single character, including the blank space, as a delimiter when concatenating substrings into a string. You can specify a string delimiter (single quote, double quote, or backslash) if that delimiter is not already in use. For example, the following returns ‘quick"brown"fox’:

PRINT OCONV(123,\C;'quick'"'brown'"'fox'\)

The semicolon is a special case. The semicolon is used to specify concatenation with no inserted delimiter. For example, the following returns ‘quick^brownfox’:

PRINT OCONV(123,"C;'quick'^'brown';'fox'")

The colon, which elsewhere in MultiValue is a concatenation operator, is here simply a literal character. For example, the following returns ‘quick:brownfox’:

PRINT OCONV(123,"C;'quick':'brown';'fox'")

The 'C' code can insert the istring value in to the returned string by using an asterisk as a substring wildcard. An asterisk can be used as either an inserted delimiter or a wildcard.

The following example uses the asterisk as an inserted delimiter. It returns ‘quick*fox*dog’:

PRINT OCONV('red',"C;'quick'*'fox'*'dog'")

The following example uses blank spaces as the inserted delimiters, and uses the asterisk as a wildcard for the istring value. It returns ‘quick red fox red dog’:

PRINT OCONV('red',"C;'quick' * 'fox' * 'dog'")

You can use an asterisk wildcard to specify the substring delimiter in istring. This wildcard delimiter can be a single character or a multiple character string. It can be a dynamic array delimiter variable, such as @FM. It can even be a semicolon.

The following example uses asterisks to specify the substring delimiter as a wildcard, with its value supplied by istring. It returns ‘quick^^brown^^fox’:

PRINT OCONV('^^',"C;'quick';*;'brown';*;'fox'")

The following example uses asterisks to specify the substring delimiter as a wildcard, with a semicolon as the istring value. It returns ‘quick;brown;fox’:

PRINT OCONV(';',"C;'quick';*;'brown';*;'fox'")

The following example uses asterisks to specify the substring delimiter as a wildcard, with @FM (the field mark delimiter) as the istring value:

PRINT OCONV(@FM,"C;'quick';*;'brown';*;'fox'")

Dynamic Array Element Extraction

You can use the 'ZVn' code to extract a single value mark element from a dynamic array. Here n is a positive integer specifying the position of the element in the dynamic array, counting from 1. Specifying a n value of 0 returns the entire dynamic array. Specifying a n value larger than the number of elements in the dynamic array returns the null string.

The following program returns the string “Barney”:

x="Fred":@VM:"Barney":@VM:"Wilma"
CRT OCONV(x,"ZV2")

Implicit Formatting

Many OCONV conversion codes are identical to FMT formatting codes. You can, therefore, perform many of the OCONV conversions using implicit formatting. Implicit formatting simply specifies the code value as the second argument in a CRT, DISPLAY, or PRINT statement. It is functionally identical to explicit formatting using the FMT function. For example, the following are equivalent date conversions:

  PRINT OCONV(14100,"D");   ! "08 AUG 2006"
  PRINT 14100 "D";          ! "08 AUG 2006"
  PRINT FMT(14100,"D");     ! "08 AUG 2006"

When there is a difference between OCONV conversion and FMT formatting, implicit formatting behaves like FMT:

  • Because the letters "L" and "R" are used as FMT formatting codes, the OCONV length ("L", "Ln", "Ln-m" or "Ln,m") and range ("Rn,m") conversion codes cannot be used for implicit formatting.

  • The "MLn" and "MRn" conversion codes are not the same as the corresponding format codes. In OCONV these conversion codes move the decimal point the specified number of digits. In FMT (and implicit formatting) these codes append the specified number of fractional digits. Thus:

      PRINT OCONV(12345,"ML2");   ! "123.45"
      PRINT 12345 "ML2";          ! "12345.00"
      PRINT FMT(12345,"ML2");     ! "12345.00"
    

    The "MDn" code is the same for both OCONV and FMT. Thus:

      PRINT OCONV(12345,"MD2");   ! "123.45"
      PRINT 12345 "MD2";          ! "123.45"
      PRINT FMT(12345,"MD2");     ! "123.45"
    

Examples

The following example shows date conversions:

DateConversions:
  ! Month Abbreviation Formats:
  PRINT OCONV(0,"D");       ! "31 DEC 1967"
  PRINT OCONV(14100,"D");   ! "08 AUG 2006"
  PRINT OCONV(14100,"D2");  ! "08 AUG 06"
  PRINT OCONV(DATE(),"D");  ! current date, for example "20 APR 2011"
  PRINT OCONV(@DATE,"D");   ! current date, for example "20 APR 2011"
  PRINT OCONV(14120,"D-")   ! "08-28-2006"
  PRINT OCONV(14120,"D/")   ! "08/28/2006"
  PRINT OCONV(14120,"DE")   ! "28/08/2006"
  PRINT OCONV(14120,"D2/")  ! "08/28/06"
  PRINT OCONV(14120,"D2-E") ! "28-08-06"

The following example shows time conversions:

TimeConversions:
  PRINT OCONV(0,"MT")         ! "00:00"
  PRINT OCONV(TIME(),"MT")    ! current time, for example "21:02"
  PRINT OCONV(TIME(),"MTH")   ! current time, for example "09:02PM"
  PRINT OCONV(TIME(),"MTS")   ! current time, for example "21:02:41"
  PRINT OCONV(TIME(),"MTS.")  ! current time, for example "21.02.41"
  PRINT OCONV(TIME(),"MTHS*") ! current time, for example "09*02*41PM"

The following example shows case conversions:

CaseConversions:
  mystr="The qUICK BrOwn foX"
  PRINT OCONV(mystr,"MCU")  
   ! Returns: THE QUICK BROWN FOX
  PRINT OCONV(mystr,"MCL")  
   ! Returns: the quick brown fox
  PRINT OCONV(mystr,"MCT")  
   ! Returns: The Quick Brown Fox

The following example shows decimal-to-hex and hex-to-decimal conversions. It shows both the OCONV conversions and the inverse ICONV conversions:

HexConversions:
  PRINT OCONV(10,"MCXD");     ! Returns 16
  PRINT OCONV(10,"MCDX");     ! Returns A

  PRINT ICONV(10,"MCXD");     ! Returns A
  PRINT ICONV(10,"MCDX");     ! Returns 16

The following example shows character-to-code and code-to-character conversions. It shows both the OCONV conversions and the inverse ICONV conversions:

CharConversions:
  PRINT OCONV("mnop","MCAX");     ! Returns 6D6E6F70
  PRINT OCONV("6D6E6F70","MCXA"); ! Returns mnop

  PRINT ICONV("mnop","MCXA");     ! Returns 6D6E6F70
  PRINT ICONV("6D6E6F70","MCAX"); ! Returns mnop

The following example shows masked decimal conversions with moving of the decimal point, rounding or truncation to the specified number of fractional digits, and zero padding when needed. The first integer (n) is number of fractional digits, the second integer (m) is the leftward shift of the decimal point, the third integer (k or #k) is a returned character count mask.

MaskedDecimalConversions:
  PRINT OCONV("123456","MD2");      ! Returns 1234.56  n=2 m=n by default
  PRINT OCONV("123456","MD12");     ! Returns 1234.6   n=1 m=2 round by default
  PRINT OCONV("123456","MD12T");    ! Returns 1234.5   n=1 m=2 T=truncate
  PRINT OCONV("123456","MD12T$");   ! Returns $1234.5  n=1 m=2 $=append dollar sign
  PRINT OCONV("123456","MD12T$,");  ! Returns $1,234.5 n=1 m=2 ,=add numeric group separator(s)
  PRINT OCONV("123456","MD12T$#3"); ! Returns 4.5      n=1 m=2 #k=3 char mask
  PRINT OCONV("123456","MD37");     ! Returns 0.012    n=3 m=7 zero padding
  PRINT OCONV("123456","MD374");    ! Returns .012     n=3 m=7 k=4 char mask

The following example shows length conversions:

LengthConversions:
  PRINT OCONV("abcd","L");     ! Returns 4
  PRINT OCONV("abcd","L0");    ! Returns 4
  PRINT OCONV("abcd","L4");    ! Returns "abcd"
  PRINT OCONV("abcd","L3");    ! Returns empty string
  PRINT OCONV("abcd","L5");    ! Returns empty string
  PRINT OCONV("abcd","L1-4");  ! Returns "abcd"
  PRINT OCONV("abcd","L0-5");  ! Returns "abcd"
  PRINT OCONV("abcd","L4-6");  ! Returns "abcd"
  PRINT OCONV("abcd","L4-4");  ! Returns "abcd"
  PRINT OCONV("abcd","L5-9");  ! Returns empty string

The following example shows pattern match extraction. It returns the input string if it matches the specified pattern. These examples perform pattern matches on 7 and 10 digit telephone numbers specified in the following commonly used formats: “123–4567”, "617-123-4567", and "(617) 123-4567":

PatternMatch:
  PRINT OCONV(telnum,"P(3n-3n-4n)")
    ! matches and returns 10-digit numbers 
    ! in hyphen format
  PRINT OCONV(telnum,"P(3n-4n);(3n-3n-4n)")
    ! matches and returns either 7-digit or 10-digit numbers
    ! in hyphen format.
  PRINT OCONV(telnum,"P(3n-3n-4n);('('3n')' 3n-4n)")
    ! matches and returns 10-digit numbers
    ! in either hyphen or parentheses formats.
  PRINT OCONV(telnum,"P('617'-3n-4n);('(617)' 3n-4n)")
    ! matches and returns 10-digit numbers
    ! in either hyphen or parentheses formats
    ! that begin with the 617 area code.

See Also

FeedbackOpens in a new tab