4. Elements of OpenSQL Statements : Functions : Scalar Functions : Data Type Conversion Functions
 
Share this page                  
Data Type Conversion Functions
The following table lists the data type conversion functions. (When converting decimal values to strings, the length of the result depends on the precision and scale of the decimal column.)
Name
Operand Type
Result Type
Description
byte(expr [, len])
any
byte
Converts the expression to byte binary data. If the optional length argument is specified, the function returns the leftmost len bytes. Len must be a positive integer value that does not exceed the length of the expr argument.
c(expr [, len])
any
c
Converts argument to c string. If the optional length argument is specified, the function returns the leftmost len characters. Len must be a positive integer value that does not exceed the length of the expr string.
char(expr [, len])
any
char
Converts argument to char string. If the optional length argument is specified, the function returns the leftmost len characters. Len must be a positive integer value that does not exceed the length of the expr string.
date(expr)
c, text, char, varchar
date
Converts a c, char, varchar or text string to internal date representation.
decimal(expr [,precision[,scale]])
any except date
decimal
Converts any numeric expression to a decimal value. If scale (number of decimal digits) is omitted, the scale of the result is 0. If precision (total number of digits) is omitted, the precision of the result is determined by the data type of the operand, as follows:
Operand Default
Datatype Precision
smallint 5
integer1 5
integer 11
float 15
float4 15
decimal 15
money 15
Decimal overflow occurs if the result contains more digits to the left of the decimal point than the specified or default precision and scale can accommodate.
dow(expr)
date
c
Converts an absolute date into its day of week (for example, 'Mon,' 'Tue'). The result length is 3.
float4(expr)
c, char, varchar, text, float, money, decimal, integer1, smallint, integer
float4
Converts the specified expression to float4.
float8(expr)
c, char, varchar, text, float, money, decimal, integer1, smallint, integer
float
Converts the specified expression to float.
hex(expr)
varchar, char, c, text
varchar
Returns the hexadecimal representation of the argument string. The length of the result is twice the length of the argument, because the hexadecimal equivalent of each character requires two bytes. For example, hex('A') returns '61' (ASCII) or 'C1' (EBCDIC).
int1(expr)
c, char, varchar, text, float, money, decimal, integer1, smallint, integer
integer1
Converts the specified expression to integer1. Decimal and floating-point values are truncated. Numeric overflow will occur if the integer portion of a floating-point or decimal value is too large to be returned in the requested format.
int2(expr)
c, char, varchar, text, float, money, decimal, integer1, smallint, integer
smallint
Converts the specified expression to smallint. Decimal and floating-point values are truncated. Numeric overflow will occur if the integer portion of a floating-point or decimal value is too large to be returned in the requested format.
int4(expr)
c, char, varchar, text, float, money, decimal, integer1, smallint, integer
integer
Converts the specified expression to integer. Decimal and floating-point values are truncated. Numeric overflow will occur if the integer portion of a floating-point or decimal value is too large to be returned in the requested format.
long_byte
(expr)
any
long byte
Converts the expression to long byte binary data.
long_varchar (expr)
any
long varchar
Converts the expression to a long varchar.
money(expr)
c, char, varchar,
text, float, money, decimal,
integer1,
smallint,
integer
money
Converts the specified expression to internal money representation. Rounds floating-point and decimal values, if necessary.
nchar(expr [, len])
any
nchar
Converts argument to nchar unicode string. If the optional length argument is specified, the function returns the leftmost len characters. Len must be a positive integer value that does not exceed the length of the expr string.
nvarchar(expr [, len])
any
nvarchar
Converts argument to nvarchar unicode string. If the optional length argument is specified, the function returns the leftmost len characters. Len must be a positive integer value that does not exceed the length of the expr string.
long_varchar (expr)
c, char, varchar, text, long varchar, long byte
long varchar
Converts the expression to a long varchar.
object_key(expr)
varchar, char, c, text
object_key
Converts the operand to an object_key.
table_key(expr)
varchar, char, c, text
table_key
Converts the operand to a table_key.
text(expr [, len])
any
text
Converts argument to text string. If the optional length argument is specified, the function returns the leftmost len characters. Len must be a positive integer value that does not exceed the length of the expr string.
unhex(expr)
varchar, c, text
varchar
Returns the opposite of the hex function. For example, unhex(x'61626320') returns 'abc' and unhex(x'01204161') returns '\001Aa'.
Exceptions can occur when a "c" data type suppresses the display of certain stored characters, or when the output data type differs from the input type.
 
 
 
Typically one character is generated for every two hex digits being converted to a printable character. If the hex digit pair being converted does not translate to a printable character, then the value is converted to a backslash (\), followed by the numeric value of the hex digit pair as a three-digit octal value.
varbyte(expr [, len])
any
byte varying
Converts the expression to byte varying binary data. If the optional length argument is specified, the function returns the leftmost len bytes. Len must be a positive integer value that does not exceed the length of the expr argument.
varchar(expr [, len])
any
varchar
Converts argument to varchar string. If the optional length argument is specified, the function returns the leftmost len characters. Len must be a positive integer value that does not exceed the length of the expr string.
If the optional length parameter is omitted, the length of the result returned by the data type conversion functions c(), char(), varchar(), and text() are as follows:
Data Type or Argument
Result Length
byte
Length of operand
byte varying
Length of operand
c
Length of operand
char
Length of operand
date
25 characters
decimal
Depends on precision and scale of column
float & float4
11 characters; 12 characters on IEEE computers
integer1 (smallint)
6 characters
integer
6 characters
integer4
13 characters
long byte
Length of operand
long varchar
Length of operand
money
20 characters
text
Length of operand
varchar
Length of operand