CONVERT

The CONVERT function converts values from one type of data to another. CONVERT is primarily useful for changing values from a numeric or DATE data type to a text data type, or vice versa.

Return Value

The return value depends on the value of the type argument.

Syntax

CONVERT(expression, type [argument...])

Arguments

expression

The expression or variable to be converted.

type

The type of data to which you want to convert expression. The keywords that represent the types are described in Table 9-2, "Keywords for the type Argument of the CONVERT Function".

Table 9-2 Keywords for the type Argument of the CONVERT Function

Keyword	Description
TEXT	Conversion to standard Oracle OLAP data types. Corresponds to CHAR and VARCHAR2 data types in the Oracle relational database. A TEXT character is encoded in the database character set.
NTEXT	Conversion to standard Oracle OLAP data types. Corresponds to the NCHAR and NVARCHAR2 data types of the relational database. An NTEXT character is encoded in UTF8 Unicode. This encoding might be different from the NCHAR character set of the database, which can be UTF16. A conversion from NTEXT to TEXT can result in data loss when the NTEXT value cannot be represented in the database character set.
ID	Conversion to standard Oracle OLAP data types.
DATE	Conversion to standard Oracle OLAP data types.
NUMBER	Conversion to standard Oracle OLAP data types.
BOOLEAN	Conversion to standard Oracle OLAP data types.
INTEGER	Conversion to standard Oracle OLAP data types.
SHORTINTEGER	Conversion to standard Oracle OLAP data types.
LONGINTEGER	Conversion to standard Oracle OLAP data types.
DECIMAL	Conversion to standard Oracle OLAP data types.
SHORTDECIMAL	Conversion to standard Oracle OLAP data types.
DATETIME	Conversion to standard Oracle OLAP data types.
BYTE	Converts a single character into an ASCII integer value in the range 0 to 255. Or BYTE converts an INTEGER within this range into a character. An INTEGER outside this range is taken modulo 256 and then converted; that is, 256 is subtracted from the INTEGER until the remainder is less than 256, and that within-range remainder is then converted into a character.
INFILE	Encloses an ID, TEXT, DATE, or RELATION value within single quotes, so that it can be read with an INFILE command. This means that expression must have TEXT, ID, DATE, or RELATION values. In the case of TEXT values with no alphanumeric equivalent, INFILE converts them to the correct escape sequences.
PACKED	Converts a number to a decimal value and then to packed format -- a text value 8 bytes long containing 15 digits and a plus or minus sign. Fractions cannot be represented in packed numbers; therefore the conversion process rounds decimal numbers to the nearest integer. See "PACKED and BINARY Conversion".
BINARY	Does not indicate conversion to a standard Oracle data type but allows additional conversion capabilities. BINARY does no conversion. The internal representation of every value, regardless of data type, is returned as a text value. For TEXT data types, the result will be the value itself and will therefore be of variable length. For ID and DECIMAL data types, the result will be 8 bytes long; ID values will be blank filled, when necessary. For BOOLEAN or INTEGER, the default result will be 2 or 4 bytes long respectively (see the arguments explanation for an additional argument that lets you vary the width slightly). For all other data types, the result will be 4 bytes long. See "PACKED and BINARY Conversion".

argument

When you specify TEXT, NTEXT, ID, DATE, or INFILE for the type, you can specify additional arguments to determine how the conversion should be done:

• Numeric values to TEXT values

  TEXT [decimal-int|DECIMALS [comma-bool|COMMAS [paren-bool|PARENS]]]

• Numeric values to NTEXT values

  NTEXT [decimal-int|DECIMALS [comma-bool|COMMAS [paren-bool|PARENS]]]

• Numeric values to ID values

  ID [decimal-int|DECIMALS]

• DATE values to TEXT, NTEXT, or ID values

  ID|TEXT|NTEXT ['date-format']

• A DATE value or the values of a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR with VNF

  ID [dwmqy-dimension]|TEXT [dwmqy-dimension|'vnf']

• TEXT, NTEXT, or ID values to DATE values

  DATE [date-order|dwmqy-dimname]

• INFILE conversion

  INFILE [width-exp|LSIZE [escape-int|0]]

• When specifying BINARY with BOOLEAN or INTEGER data

  BINARY [width-exp]

• Onverting between TEXT and NTEXT values

  NOXLATE

decimal-int

An INTEGER expression that controls the number of decimal places to be used when converting numeric data to TEXT or ID values. When this argument is omitted, CONVERT uses the current value of the DECIMALS option (the default is 2).

comma-bool

A Boolean expression that determines whether commas are used to mark thousands and millions in the text representation of the numeric data. When the value of the expression is YES, commas are used. When this argument is omitted, CONVERT uses the current value of the COMMAS option (the default is YES).

paren-bool

A Boolean expression that determines whether negative values are enclosed in parentheses in the text representation of the numeric data. When the value of the expression is YES, parentheses are used; when the value is NO, a minus sign precedes negative values. When this argument is omitted, CONVERT uses the current value of the PARENS option (the default is NO).

date-format

A text expression that specifies the template to use when converting a DATE expression to text. The template can include format specifications for any of the four components of a date (day, month, year, and day of the week). Each component in the template must be preceded by a left angle bracket (<)and followed by a right angle bracket (>). You can include additional text before, after, or between the components.

The valid formats for each date component are the same as the formats allowed in the DATEFORMAT option.

In the following statement, CONVERT returns today's date as a text value that is formatted by a date-format argument.

SHOW CONVERT(TODAY TEXT '<MM>-<DD>-<YY>')

In this example, today's date is March 31, 1998, and the SHOW command presents it in the following format.

03-31-98

When you do not include the date-format argument, the format of the result is determined by the current setting of the DATEFORMAT option.

dwmqy-dimension

The name of a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR. Oracle OLAP uses the VNF of dwmqy-dimension when converting a DATE value to a TEXT or an ID value. When you have not specified the VNF of dwmqy-dimension, Oracle OLAP uses its default VNF.

In the following statement, CONVERT returns today's date as a text value that is formatted by the VNF of the YEAR dimension.

show convert(today text year)

In this example, today's date is March 31, 1998, and the SHOW command presents it in the following format.

YR98

vnf

A text template that specifies the value name format to use when converting values of a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR to text. The template can include format specifications for any of the components of a time period. Time period components include all the components of a date (day, month, year, and day of the week), plus the fiscal year and period components. The template can also include the name of the DAY, WEEK, MONTH, QUARTER, or YEAR dimension as a component. Each component in the template must be preceded by a left angle bracket and followed by a right angle bracket. You can include additional text before, after, or between the components.

The vnf argument to the CONVERT function is similar to the template in a VNF command. However, a VNF command template must be designed for precise and unambiguous interpretation of input, while the vnf argument is not so constrained. Therefore, the format styles allowed in the vnf argument are more extensive than those allowed in a VNF command template.

Valid format styles for a vnf argument include all the format styles allowed in the template of a VNF command, plus all the format styles allowed in a DATEFORMAT template. DATEFORMAT provides the following format styles that are not allowed in VNF command templates but that are valid in the vnf argument to the CONVERT function:

• Ordinal styles for the day of the month (DT and DTL)

• First-letter style for the month (MT)

• Styles for the day of the week (W, WT, WTXT, WTXTL, WTEXT, and WTEXTL)

Append a B code to any of these formats to indicate that you want to display the beginning day or month of the period, rather than the final day or month.

You can use any combination of VNF and DATEFORMAT format styles with for any dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR. This contrasts with the template in a VNF command, in which only certain format combinations are valid for each type of dimension.

In the following statement, CONVERT returns the current value of the MONTH dimension as a text value that is formatted by a vnf argument.

SHOW CONVERT(month TEXT '<MTEXTL>, <YYYY>')

In this example, the first MONTH value in status is DEC97, and the SHOW command presents it in the following format.

December, 1997

When you do not include the vnf argument, the format of the result is determined by the VNF of the dimension whose values you are converting. When the dimension has no VNF, the result is formatted according to the default VNF for the type of dimension being converted.

date-order

A text expression that specifies how to interpret the specified text value as a DATE value when the order of the text value's components (month, day, and year) is ambiguous. The expression can be one of the following: 'MDY', 'DMY', 'YMD', 'YDM', 'MYD', or 'DYM'. Each letter represents a component of the date: M stands for month, D stands for day, and Y stands for year.

dwmqy-dimname

The name of a dimension of type DAY, WEEK, MONTH, QUARTER, or YEAR whose VNF or default date-order determines how to interpret the specified text value as a DATE value when the order of the text value's components is ambiguous.

The following examples show how you can control the conversion of a text expression by using a date-order or dwmqy-dimname argument.

The following statements use the date-order argument.

SHOW CONVERT('1/3/98' DATE 'MDY')
SHOW CONVERT('1/3/98' DATE 'DMY')

These statements produce the following output.

03JAN98
01MAR98

The following statement uses the dwmqy-dimname argument. It specifies the qrtr dimension, which was defined as a dimension of type QUARTER.

show convert('96-2' date qrtr)

The statement produces the following output.

31MAR96

The following statement also uses the dwmqy-dimname argument. It specifies the fyear dimension, which has the following definition.

DEFINE fyear DIMENSION YEAR ENDING JUNE
VNF 'TY<ff>'

This statement

SHOW CONVERT('jan97' DATE fyear)

produces the following output.

30JUN97

When you do not include the date-order or dwmqy-dimname argument, any ambiguity in the interpretation of a text expression is resolved by the current setting of the DATEORDER option. Refer to the DATEORDER option for a complete description of DATE values and how they are interpreted.

width-exp

An INTEGER expression that indicates the width of the output from CONVERT. The minimum width is 7. The default width is the current value of the LSIZE option. This argument is required when you specify the escape-int argument.

escape-int

Indicates whether escape sequences are to be used in the output. For this argument you can specify one of the values listed in Table 9-3, "Values for escape-int Argument of the CONVERT".

Table 9-3 Values for escape-int Argument of the CONVERT

Value	Description
-1	Do not use escapes. Precede -1 with a comma (,-1) so that Oracle OLAP does not subtract 1 from a preceding WIDTH argument.
0	Use escapes for unprintable characters. (Default)
1	Use escapes for all characters.

For more information on escape sequences in the OLAP DML, see "Escape Sequences".

width-exp

An INTEGER expression that controls the width of the converted result. It can evaluate to 1, 2, or 4 bytes. The default width is 2 for BOOLEAN, or 4 for INTEGER. When an integer is too large to fit in the specified width, the result is NA. When the width is invalid or specified for some other data type, an error occurs.

NOXLATE

A keyword indicating that no character set conversion should be performed. Instead, Oracle OLAP only tags the converted value with the target data type, leaving the data as it was before the CONVERT function was called. Use this keyword only when it is necessary to store binary data in a TEXT or NTEXT variable.

Notes

The maximum number of characters in a line is 4000. An error occurs when you try an INFILE conversion that produces a line with more than 4000 characters. This can occur when the source line exceeds 99 characters and enough of them need escape sequences.

When you convert a DATE value to an INTEGER value, the result is the sequence number that represents the date (the sequence number 1 represents January 1, 1900). When you convert a DATE value to another numeric type, the date's integer sequence number is converted to the specified numeric data type.

When you convert an INTEGER value to a DATE value, the result is the date whose sequence number matches the specified integer (January 1, 1900 is represented by the sequence number 1). When you convert from another numeric type to a DATE value, the number is converted to an INTEGER, then the INTEGER is converted to a DATE value.

When you convert a value of a dimension of type DATE, DAY, WEEK, MONTH, QUARTER, or YEAR to an ID value, and the result is more than eight characters long, the result is truncated.

When you convert a given value of a relation into an INTEGER value, the result represents the position of the value in the relation's dimension. This behavior reflects the fact that the values of a relation are dimension values, not TEXT values.

When you convert a number to a DATE value and the result is outside the range of valid dates, CONVERT returns NA. Valid dates range from January 1, 1900 (sequence number 1) to December 31, 9999 (sequence number 2,958,464).

When you try to convert a number larger than 2,147,483,647 or smaller than -2,147,483,647 (the maximum and minimum integer values), to an INTEGER, you get a result of NA.

Likewise, when you try to convert a number larger than 32,767 or smaller than -32,768 to a SHORTINTEGER, you get a result of NA. For a value of type DATE, the integer 32,767 represents the date September 17, 1992. Therefore, CONVERT returns NA when you attempt to convert any date later than this to a SHORTINTEGER value.

When you convert a null string to a BYTE, you get a result of 32. CONVERT returns the same value for a null string ('') as it does for a blank string (' ').

The PACKED and BINARY types are useful for creating binary files that contain PACKED and BINARY data. To create such a file, use FILEOPEN statement with the BINARY keyword to open the file and FILEPUT to write values to it. You can use the ROW function as an argument to the FILEPUT command to help format the file.

Examples

LIMIT month TO 'Jul96'
LIMIT product to 'Canoes'
SHOW JOINCHARS('Price of Canoes = $' CONVERT(price TEXT 0))
Price of Canoes = $200

DEFINE textvar VARIABLE TEXT
textvar = 'testvalue'
SHOW CONVERT(textvar INFILE 9 1)
'\d116\d101\d115\d116\d118\d097\d108\d117\d101'
ESCAPEBASE = 'x'
SHOW CONVERT(textvar INFILE 9 1)
'\x74\x65\x73\x74\x76\x61\x6C\x75\x65'