VARCHAR

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:


>>-VARCHAR(integer-expression)---------------------------------><

Decimal to Varchar:


>>-VARCHAR(decimal-expression-+----------------------+-)-------><
                              '-,--decimal-character-'

Floating-point to Varchar:


>>-VARCHAR(floating-point-expression)--------------------------><

Decimal floating point to Varchar:


>>-VARCHAR(decimal-floating-point-expression)------------------><

Character to Varchar:

>>-VARCHAR(character-expression-+------------------------------------+-)-><
                                '-,--integer--+--------------------+-'     
                                              '-,--+-CODEUNITS16-+-'       
                                                   +-CODEUNITS32-+         
                                                   '-OCTETS------'

Graphic to Varchar:

>>-VARCHAR(graphic-expression-+------------------------------------+-)-><
                              '-,--integer--+--------------------+-'     
                                            '-,--+-CODEUNITS16-+-'       
                                                 '-CODEUNITS32-'

Datetime to Varchar:


>>-VARCHAR(datetime-expression)--------------------------------><

Row ID to Varchar:


>>-VARCHAR(row-ID-expression)----------------------------------><

The schema is SYSIBM.

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 (VARCHAR) of the argument in the form of an SQL integer constant.

The length attribute of the result depends on whether the argument is a small or large integer as follows:

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

The actual length of the result is the smallest number of characters that can be used to represent the value of the argument. If the argument is negative, the first character of the result is a minus sign. Otherwise, the first character is a digit.

The CCSID of the result is the SBCS CCSID of the appropriate encoding scheme.

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 (CHAR or VARCHAR) 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.

Start of change The result is a varying-length character string representation of the argument. 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. Leading zeros are not returned. Trailing zeros are returned. End of change

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

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. If the argument is negative, the result begins with a minus sign. Otherwise, the result begins with a digit. Start of change If the scale of decimal-expression is zero, the decimal character is not returned. End of change

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.

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 (VARCHAR) of the argument in the form of an SQL floating-point constant.

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. 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 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.

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.

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

The length attribute of the result is 42 bytes.

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.

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 32767, 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 between 1 and 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.

Examples

Example 1: Assume that host variable JOB_DESC is defined as VARCHAR(8). Using sample table DSN8A10.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 DSN8A10.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'