VARCHAR scalar function

The VARCHAR function returns a varying-length character string representation of the value specified by the first argument.

The syntax of the VARCHAR function depends on the data type of the input argument. The following types of input arguments are accepted.

Integer to Varchar:

Decimal to Varchar:

Floating-point to Varchar:

Decimal floating point to Varchar:

Character to Varchar:

Graphic to Varchar:

Datetime to Varchar:

Row ID to Varchar:

The schema is SYSIBM.

The VARCHAR function returns a varying-length character string representation of one of the following values:

An integer number if the first argument is a SMALLINT, INTEGER, or BIGINT
A decimal number if the first argument is a decimal number
A double-precision floating-point number if the first argument is a DOUBLE or REAL
A decimal floating-point number if the first argument is a DECFLOAT
A character string value if the first argument is any type of character string
A graphic string if the first argument is an EBCDIC or Unicode graphic string
A datetime value if the first argument is a date, time, or timestamp
A row ID value if the first argument is a row ID

The result of the function is a varying-length character string (VARCHAR).

The result can be null; if the first argument is null, the result is the null value.

Integer to Varchar

integer-expression: An expression that returns a value that is a built-in integer data type (SMALLINT, INTEGER, BIGINT).

The result is a varying-length character string representation of the argument in the form of an SQL integer constant. The actual length of the result is the smallest number of characters that can be used to represent the value of the argument. The result consists of n characters that are the significant digits that represent the value of the argument with a preceding minus sign if the argument is negative. A positive value starts with a digit. Leading zeroes are not included.

If the argument is a small integer, the length attribute of the result is 6.
If the argument is a large integer, the length attribute of the result is 11.
If the argument is a big integer, the length attribute of the result is 20.

Start of change The CCSID of the result is determined from the application encoding scheme. End of change

Decimal to Varchar

decimal-expression: An expression that returns a value that is a built-in decimal data type. To specify a different precision and scale for the expression's value, apply the DECIMAL function to the expression before applying the VARCHAR function.
decimal-character: Specifies the single-byte character constant that is used to delimit the decimal digits in the result character string. The character must not be a digit, a plus sign (+), a minus sign (-), or a blank. The default is the period (.) or comma (,). For information on what factors govern the choice, see Decimal point representation.

The result is a varying-length character string representation of the first argument. The actual length of the result is the smallest number of characters that can be used to represent the result, except that trailing zeros are included. The result includes a decimal character and up to p digits where p is the precision of decimal-expression with a preceding minus sign if the argument is negative. A positive value starts with a digit or the decimal-character. Leading zeros are not returned. If the scale of decimal expression is zero, the decimal character is not returned.

The length attribute of the result is 2+p where p is the precision of decimal-expression.

Start of change The CCSID of the result is determined from the application encoding scheme. End of change

Floating-point to Varchar

floating-point-expression: An expression that returns a value that is a built-in floating-point data type.

The result is a varying-length character string representation of the argument in the form of an SQL floating-point constant. If the argument is negative, the first character of the result is a minus sign; otherwise, the first character is a digit. If the argument is zero, the result is 0E0.

The length attribute of the result is 24. The actual length of the result is the smallest number of characters that can represent the value of the argument such that the mantissa consists of a single digit other than zero followed by a period and a sequence of digits.

Start of change The CCSID of the result is determined from the application encoding scheme. End of change

Decimal floating-point to Varchar

decimal-floating-point-expression: An expression that returns a value that is the built-in DECFLOAT data type.

The result is the varying-length character string representation of the argument in the form of an SQL decimal floating-point constant.

If the result value is one of the special values Infinity, sNaN, or NaN, the strings ’INFINITY’, ’SNAN’, or ’NAN’, respectively, are returned. The DECFLOAT special value sNaN does not result in an exception when it is converted to a string.

The length attribute of the result is 42. The actual length of the result is the smallest number of characters that represents the value of the argument, including the sign, digits, and period. Trailing zeros are significant. If the argument is negative, the first character of the result is a minus sign. Otherwise, the first character is a digit, or a letter if the result value is Infinity, sNaN, or NaN.

Start of change The CCSID of the result is determined from the application encoding scheme. End of change

Character to Varchar

character-expression

An expression that returns a value that is a built-in character data type.

integer

Specifies the length attribute for the resulting varying-length character string. The value must be between 1 and 32764, expressed in the units that are either implicitly or explicitly specified. If the length is not specified, the length of the result is the same as the length of character-expression.

If CODEUNITS16, CODEUNITS32, or OCTETS is specified, see Determining the length attribute of the final result for information about how to calculate the length attribute of the result string.

If a length attribute is not specified and if the character-expression is an empty string constant, the length attribute of the result is 1 and the result is an empty string. Otherwise, the length attribute of the result is the same as the length attribute of the first argument.

CODEUNITS16, CODEUNITS32, or OCTETS

Specifies the unit that is used to express integer. If character-expression is a character string that is defined as bit data, CODEUNITS16 and CODEUNITS32 cannot be specified.

CODEUNITS16: Specifies that integer is expressed in terms of 16-bit UTF-16 code units.
CODEUNITS32: Specifies that integer is expressed in terms of 32-bit UTF-32 code units.
OCTETS: Specifies that integer is expressed in terms of bytes.

For more information about CODEUNITS16, CODEUNITS32, and OCTETS, see String unit specifications.

The actual length of the result is the minimum of the length attribute of the result and the actual length of character-expression. If the length of character-expression is greater than the length attribute of the result, the result is truncated. Unless all the truncated characters are blanks appropriate for character-expression, a warning is returned.

If character-expression is bit data, the result is bit data. Otherwise, the CCSID of the result is the same as the CCSID of character-expression.

Graphic to Varchar

graphic-expression

An expression that returns a value that is a built-in graphic data type.

integer

The length attribute for the resulting varying-length graphic string. The value must be in the range 1–32704, expressed in the units that are either implicitly or explicitly specified.

If CODEUNITS16 or CODEUNITS32 is specified, see Determining the length attribute of the final result for information about how to calculate the length attribute of the result string.

If a length attribute is not specified, the length attribute of the result is determined as follows (where n is the length attribute of the first argument):

If the graphic-expression is the empty graphic string constant, the length attribute of the result is 1.
If the result is SBCS data, the result length is n.
If the result is mixed data, the result length is 3*(length(graphic-expression).

CODEUNITS16 or CODEUNITS32

Specifies the unit that is used to express integer.

CODEUNITS16: Specifies that integer is expressed in terms of 16-bit UTF-16 code units.
CODEUNITS32: Specifies that integer is expressed in terms of 32-bit UTF-32 code units.

For more information about CODEUNITS16 or CODEUNITS32, see String unit specifications.

The actual length of the result is the minimum of the length attribute of the result and the actual length of graphic-expression. If the length of the graphic expression is greater than the length attribute of the result, the result is truncated. Unless all the truncated characters were blanks appropriate for graphic-expression, a warning is returned.

The CCSID of the result is the character mixed CCSID that corresponds to the graphic CCSID of graphic-expression.

Datetime to Varchar

datetime-expression

An expression whose value has one of the following three built-in data types:

date

The result is a varying-length character string representation of the date in the format that is specified by the DATE precompiler option, if one is provided, or else field DATE FORMAT on installation panel DSNTIP4 specifies the format. If the format is to be LOCAL, field LOCAL DATE LENGTH on installation panel DSNTIP4 specifies the length of the result. Otherwise, the length attribute and actual length of the result is 10.

LOCAL denotes the local format at the Db2 that executes the SQL statement. If LOCAL is used for the format, a time exit routine must be installed at that Db2.

An error occurs if the second argument is specified and is not a valid value.

time

The result is a varying-length character string representation of the time in the format specified by the TIME precompiler option, if one is provided, or else field TIME FORMAT on installation panel DSNTIP4 specifies the format. If the format is to be LOCAL, the field LOCAL TIME LENGTH on installation panel DSNTIP4 specifies the length of the result. Otherwise, the length attribute and actual length of the result is 8.

LOCAL denotes the local format at the Db2 that executes the SQL statement. If LOCAL is used for the format, a time exit routine must be installed at that Db2.

An error occurs if the second argument is specified and is not a valid value.

timestamp

The result is the character string representation of the timestamp with time zone. The second argument must not be specified.

If datetime-expression is a TIMESTAMP (0) WITHOUT TIME ZONE, the length of the result is 19.
If datetime-expression is a TIMESTAMP (p) WITHOUT TIME ZONE, the length of the result is 20+p where p is the timestamp precision. The second argument must not be specified.
If datetime-expression is a TIMESTAMP (0) WITH TIME ZONE, the length of the result is 25.
If datetime-expression is a TIMESTAMP (p) WITH TIME ZONE, the length of the result is 26 +p where p is the timestamp precision. The second argument must not be specified

The CCSID of the result is determined from the context in which the function was invoked. For more information, see Determining the encoding scheme and CCSID of a string.

Row ID to Varchar

row-ID-expression: An expression that returns a value that is a built-in row ID data type.

The result is a varying-length character string representation (VARCHAR) of the argument. It is bit data.

The length attribute of the result is 40. The actual length of the result is the length of row-ID-expression.

Notes

Syntax alternatives: VARCHAR9 can be specified as an alternative to VARCHAR. The result of the function is the same, except when the first argument is decimal data.

Examples

Example 1: Assume that host variable JOB_DESC is defined as VARCHAR(8). Using sample table DSN8C10.EMP, set JOB_DESC to the varying-length string equivalent of the job description (column JOB defined as CHAR(8)) for the employee with the last name of 'QUINTANA'.

   SELECT VARCHAR(JOB)
     INTO :JOB_DESC
     FROM DSN8C10.EMP
     WHERE LASTNAME = 'QUINTANA';

Example 2: FIRSTNME is a VARGRAPHIC(6) column in a Unicode table T1. One of its values is the string 'Jürgen' (X'004A00FC007200670055006E'). When FIRSTNME has this value:

   Function ...                           Returns ...
   ----------------------------------------------------------
   VARCHAR(FIRSTNME,3,CODEUNITS32)        'Jür'  -- x'4AC3BC72'
   VARCHAR(FIRSTNME,3,CODEUNITS16)        'Jür'  -- x'4AC3BC72'
   VARCHAR(FIRSTNME,3,OCTETS)             'Jü'   -- x'4AC3BC'

¹ If the BIF_COMPATIBILITY system parameter is set to V9_DECIMAL_VARCHAR, or if the SYSCOMPAT_V9.VARCHAR function is used, the format of the result matches the result of the VARCHAR9 function with decimal input.