VARCHAR scalar function

The VARCHAR function returns a varying-length character string representation of a value of a different data type.

Binary integer to VARCHAR

Read syntax diagramSkip visual syntax diagramVARCHAR(integer-expression )

Decimal to VARCHAR

Read syntax diagramSkip visual syntax diagramVARCHAR(decimal-expression ,decimal-character )

Floating-point to VARCHAR

Read syntax diagramSkip visual syntax diagramVARCHAR(floating-point-expression ,decimal-character )

Decimal floating-point to VARCHAR

Read syntax diagramSkip visual syntax diagramVARCHAR(decimal-floating-point-expression ,decimal-character )

Character string to VARCHAR

Read syntax diagramSkip visual syntax diagramVARCHAR(character-expression ,integer)

Graphic string to VARCHAR

Read syntax diagramSkip visual syntax diagramVARCHAR(graphic-expression ,integer)

Binary string to VARCHAR

Read syntax diagramSkip visual syntax diagram VARCHAR ( binary-expression ,integer )

Datetime to VARCHAR

Read syntax diagramSkip visual syntax diagramVARCHAR(datetime-expression ,ISOUSAEURJISLOCAL)

Boolean to VARCHAR

Read syntax diagramSkip visual syntax diagram VARCHAR ( boolean-expression )

The schema is SYSIBM. The function name cannot be specified as a qualified name when keywords are used in the function signature.

Binary integer to VARCHAR
integer-expression
An expression that returns a value that is of an integer data type (SMALLINT, INTEGER, or BIGINT).
The result is the varying-length string representation of integer-expression in the form of an SQL integer constant. The length attribute of the result depends on whether integer-expression is a small, large or big integer as follows:
  • If the first argument is a small integer, the maximum length of the result is 6.
  • If the first argument is a large integer, the maximum length of the result is 11.
  • If the first argument is a big integer, the maximum length of the result is 20.

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

The code page of the result is the code page of the section.

Decimal to VARCHAR
decimal-expression
An expression that returns a value that is a decimal data type. The DECIMAL scalar function can be used to change the precision and scale.
decimal-character
Specifies the single-byte character constant that is used to delimit the decimal digits in the result character string. The character constant cannot be a digit, the plus sign (+), the minus sign (-), or a blank (SQLSTATE 42815). The default is the period (.) character.

The result is a varying-length character string representation of decimal-expression in the form of an SQL decimal constant. 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. Leading zeros are not included. If decimal-expression is negative, the first character of the result is a minus sign; otherwise, the first character is a digit or the decimal character. If the scale of decimal-expression is zero, the decimal character is not returned.

The code page of the result is the code page of the section.

Floating-point to VARCHAR
floating-point-expression
An expression that returns a value that is a floating-point data type (DOUBLE or REAL).
decimal-character
Specifies the single-byte character constant that is used to delimit the decimal digits in the result character string. The character constant cannot be a digit, the plus sign (+), the minus sign (-), or a blank (SQLSTATE 42815). The default is the period (.) character.

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

The maximum length of the result is 24. The actual length of the result is the smallest number of characters that can represent the value of floating-point-expression such that the mantissa consists of a single digit other than zero followed by the decimal-character and a sequence of digits. If floating-point-expression is negative, the first character of the result is a minus sign; otherwise, the first character is a digit. If floating-point-expression is zero, the result is 0E0.

The code page of the result is the code page of the section.

Decimal floating-point to VARCHAR
decimal-floating-point-expression
An expression that returns a value that is a decimal floating-point data type (DECFLOAT).
decimal-character
Specifies the single-byte character constant that is used to delimit the decimal digits in the result character string. The character constant cannot be a digit, the plus sign (+), the minus sign (-), or a blank (SQLSTATE 42815). The default is the period (.) character.

The result is a varying-length character string representation of decimal-floating-point-expression in the form of an SQL decimal floating-point constant. The maximum length of the result is 42. The actual length of the result is the smallest number of characters that can represent the value of decimal-floating-point-expression. If decimal-floating-point-expression is negative, the first character of the result is a minus sign; otherwise, the first character is a digit. If decimal-floating-point-expression is zero, the result is 0.

If the value of decimal-floating-point-expression is the special value Infinity, sNaN, or NaN, the strings INFINITY, SNAN, and NAN, respectively, are returned. If the special value is negative, the first character of the result is a minus sign. The decimal floating-point special value sNaN does not result in a warning when converted to a string.

The code page of the result is the code page of the section.

Character string to VARCHAR
character-expression
An expression that returns a value that is a built-in character string data type.
integer
An integer constant that specifies the length attribute for the resulting varying-length character string. The value must be between 0 and the maximum length for the VARCHAR data type in the string units of the result.
If the second argument is not specified:
  • If the character-expression is the empty string constant, the length attribute of the result is 0.
  • Otherwise, the length attribute of the result is the lower of the following values:
    • The maximum length for the VARCHAR data type in the string units of the result
    • The length attribute of the first argument

The result is a varying-length character string. The length attribute of the result is determined by the value of integer. If character-expression is the FOR BIT DATA subtype, the result is FOR BIT DATA.

If the length of character-expression is greater than the length attribute of the result, several scenarios exist:
  • If the string unit of the result is CODEUNITS32, truncation is performed. If only blank characters are truncated and character-expression is CHAR or VARCHAR, no warning is returned. Otherwise, a warning is returned (SQLSTATE 01004).
  • If integer is specified, truncation is performed. If only blank characters are truncated and character-expression is CHAR or VARCHAR, no warning is returned. Otherwise, a warning is returned (SQLSTATE 01004). When part of a multi-byte character is truncated, that partial character is replaced with the blank character. Do not rely on this behavior because it might change in a future release.
  • If integer is not specified, an error is returned (SQLSTATE 22001).
Graphic string to VARCHAR
graphic-expression
An expression that returns a value that is a built-in graphic string data type.
integer
An integer constant that specifies the length attribute for the resulting varying-length character string. The value must be between 0 and the maximum length for the VARCHAR data type in the string units of the result.
If the second argument is not specified:
  • If the graphic-expression is the empty string constant, the length attribute of the result is 0.
  • If the string units of graphic-expression is CODEUNITS32, the length attribute of the result is the lower of the following values:
    • The maximum length for the VARCHAR data type in the string units of the result
    • The length attribute of the first argument
  • Otherwise, the length attribute of the result is the lower of the following values:
    • The maximum length for the VARCHAR data type in the string units of the result
    • 3 * length attribute of the first argument

The result is a varying-length character string that is converted from graphic-expression. The length attribute of the result is determined by the value of integer.

If the length of graphic-expression that is converted to a character string is greater than the length attribute of the result, several scenarios exist:
  • If the string unit of the result is CODEUNITS32, truncation is performed. If only blank characters are truncated and graphic-expression is GRAPHIC or VARGRAPHIC, no warning is returned. Otherwise, a warning is returned (SQLSTATE 01004).
  • If integer is specified and graphic-expression is a GRAPHIC or VARGRAPHIC, truncation is performed with no warning returned.
  • If integer is specified and graphic-expression is a DBCLOB, truncation is performed with a warning returned (SQLSTATE 01004).
  • If integer is not specified and graphic-expression is a GRAPHIC or VARGRAPHIC, truncation is performed with no warning returned.
  • If integer is not specified and graphic-expression is a DBCLOB, an error is returned (SQLSTATE 22001).
Binary string to VARCHAR
binary-expression
An expression that returns a value that is a built-in binary string data type.
integer
An integer constant that specifies the length attribute for the resulting varying-length character string. The value must be between 0 and the maximum length for the VARCHAR data type in the string units of the result.

The result is a FOR BIT DATA character string.

Datetime to VARCHAR
datetime-expression
An expression that is of one of the following data types:
DATE
The result is the character string representation of the date in the format specified by the second argument. The length of the result is 10. An error is returned if the second argument is specified and is not a valid value (SQLSTATE 42703).
TIME
The result is the character string representation of the time in the format specified by the second argument. The length of the result is 8. An error is returned if the second argument is specified and is not a valid value (SQLSTATE 42703).
TIMESTAMP
The result is the character string representation of the timestamp. If the data type of datetime-expression is TIMESTAMP(0), the length of the result is 19. If the data type of datetime-expression is TIMESTAMP(n), where n is between 1 and 12, the length of the result is 20+n. Otherwise, the length of the result is 26. The second argument must not be specified (SQLSTATE 42815).

The code page of the result is the code page of the section.

Boolean to VARCHAR
boolean-expression
An expression that returns a Boolean value (TRUE or FALSE). The result is either 'TRUE' or 'FALSE'.

Result

The VARCHAR function returns a varying-length character string representation of:
  • An integer number, if the only argument is a SMALLINT, INTEGER, or BIGINT value
  • A decimal number, if the first argument is a DECIMAL value
  • A double-precision floating-point number, if the first argument is a floating-point (DOUBLE or REAL) value
  • A decimal floating-point number, if the first argument is a decimal floating-point (DECFLOAT) value
  • A character string, if the first argument is a character string (CHAR, VARCHAR, or CLOB) value
  • A graphic string (Unicode databases only), if the first argument is a graphic string (GRAPHIC, VARGRAPHIC, or DBCLOB) value
  • A datetime value, if the first argument is a datetime (DATE, TIME, or TIMESTAMP) value
  • A Boolean value ('TRUE' or 'FALSE'), if the only argument is a BOOLEAN value (TRUE or FALSE)
In a non-Unicode database, the string units of the result is OCTETS. Otherwise, the string units of the result is determined by the data type of the first argument.
  • OCTETS, if the first argument is character string or a graphic string with string units of OCTETS, CODEUNITS16, or double bytes.
  • CODEUNITS32, if the first argument is character string or a graphic string with string units of CODEUNITS32.
  • Determined by the default string unit of the environment, if the first argument is not a character string or a graphic string.
In a Unicode database, when the output string is truncated part-way through a multiple-byte character:
  • If the input was a character string, the partial character is replaced with one or more blanks
  • If the input was a graphic string, the partial character is replaced by the empty string
Do not rely on either of these behaviors, because they might change in a future release.

If the first argument can be null, the result can be null. If the first argument is null, the result is the null value.

Notes

  • Increasing portability of applications: If the first argument is numeric, or if the first argument is a string and the length argument is specified, use the CAST specification instead of this function to increase the portability of your applications.

Examples

  • Example 1: Make EMPNO varying-length with a length of 10. 
       SELECT VARCHAR(EMPNO,10)
     INTO :VARHV
     FROM EMPLOYEE
  • Example 2: Set the host variable JOB_DESC, defined as VARCHAR(8), to the VARCHAR equivalent of the job description (which is the value of the JOB column), defined as CHAR(8), for employee Dolores Quintana. 
       SELECT VARCHAR(JOB)
     INTO :JOB_DESC
     FROM EMPLOYEE
     WHERE LASTNAME = 'QUINTANA'
  • Example 3: The EDLEVEL column is defined as SMALLINT. The following returns the value as a varying-length character string.
       SELECT VARCHAR(EDLEVEL)
     FROM EMPLOYEE
     WHERE LASTNAME = 'HAAS'
    Results in the value '18'.
  • Example 4: The SALARY and COMM columns are defined as DECIMAL with a precision of 9 and a scale of 2. Return the total income for employee Haas using the comma decimal character.
       SELECT VARCHAR(SALARY + COMM, ',')
     FROM EMPLOYEE
     WHERE LASTNAME = 'HAAS'
    Results in the value '56970,00'.
  • Example 5: The following statement returns a string of data type VARCHAR with the value 'TRUE'.
       values VARCHAR(3=3)
  • Example 6: The following statement returns a string of data type VARCHAR with the value 'FALSE'.
       values VARCHAR(3>3)