VARCHAR

Edit online

The VARCHAR function returns a character-string representation.

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 to Varchar

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

Graphic to Varchar

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

Datetime to Varchar

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

Boolean to Varchar

Read syntax diagramSkip visual syntax diagramVARCHAR (boolean-expression)
End of change

The VARCHAR function returns a character-string representation of:

  • An integer number if the first argument is a SMALLINT, INTEGER, or BIGINT.
  • A decimal number if the first argument is a packed or zoned 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 DECFLOAT.
  • A character string if the first argument is any type of character string.
  • A graphic string if the first argument is any graphic string.
  • A date value if the first argument is a DATE.
  • A time value if the first argument is a TIME.
  • A timestamp value if the first argument is a TIMESTAMP.
  • Start of changeA Boolean value if the first argument is a BOOLEAN.End of change

The result of the function is a varying-length string. If the first argument can be null, 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 an integer data type (either SMALLINT, INTEGER, or BIGINT).

The result is a varying-length character string of the argument in the form of an SQL integer constant. 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. It is left justified.

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

The actual length of the result is the smallest number of characters that can be used to represent the value of the argument. Leading zeroes 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 CCSID of the result is the default SBCS CCSID at the current server.

Decimal to Varchar

decimal-expression
An expression that returns a value that is a packed or zoned decimal data type (either DECIMAL or NUMERIC). If a different precision and scale is wanted, the DECIMAL scalar function can be used to make the change.
decimal-character
Specifies the single-byte character constant that is used to delimit the decimal digits in the result character string. The character must be a period or comma. If the second argument is not specified, the decimal point is the default decimal point. For more information, see Decimal point.

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 the decimal-expression with a preceding minus sign if the argument is negative. Leading zeros are not returned. Trailing zeros are 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 the 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 characters are included. Leading zeros are not included. If the argument is negative, the result begins with a minus sign. Otherwise, the result begins with a digit or the decimal-character.

The CCSID of the result is the default SBCS CCSID at the current server.

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 must be a period or comma. If the second argument is not specified, the decimal point is the default decimal point. For more information, see Decimal point.

The result is a varying-length character string representation of the argument in the form of a 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 the decimal-character 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 or the decimal-character. If the argument is zero, the result is 0E0.

The CCSID of the result is the default SBCS CCSID at the current server.

Decimal floating-point to Varchar

decimal-floating-point expression
An expression that returns a value that is a decimal floating-point data type.
decimal-character
Specifies the single-byte character constant that is used to delimit the decimal digits in the result character string. The character must be a period or comma. If the second argument is not specified, the decimal point is the default decimal point. For more information, see Decimal point.

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

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 decimal-character. 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 the decimal-character. If the argument is zero, the result is 0.

If the DECFLOAT value is Infinity, sNaN, or NaN, the strings 'INFINITY', 'SNAN', and 'NAN', respectively, are returned. If the special value is negative, a minus sign will be the first character in the string. The DECFLOAT special value sNaN does not result in an exception when converted to a string.

The CCSID of the result is the default SBCS CCSID at the current server.

Character to Varchar

character-expression
An expression that returns a value that is a built-in CHAR, VARCHAR, or CLOB data type.1
length
An integer constant that specifies the length attribute for the resulting varying length character string. The value must be between 1 and 32740 (32739 if nullable). If the first argument is DBCS-only mixed data, the second argument cannot be less than 4.

If the second argument is not specified or DEFAULT is specified:

  • If the character-expression is an empty string constant, the length attribute of the result is 1.
  • Otherwise, the length attribute of the result is the same as the length attribute of the first argument.

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 the character-expression is greater than the length attribute of the result, truncation is performed. A warning (SQLSTATE 01004) is returned unless the truncated characters were all blanks.

integer
An integer constant that specifies the CCSID of the result. It must be a valid SBCS CCSID, mixed data CCSID, or 65535 (bit data). If the third argument is an SBCS CCSID, then the result is SBCS data. If the third argument is a mixed CCSID, then the result is mixed data and the length attribute of the result cannot be less than 4. If the third argument is 65535, then the result is bit data. If the third argument is a SBCS CCSID, then the first argument cannot be a DBCS-either or DBCS-only string.

If the third argument is not specified then:

  • If the first argument is SBCS data, then the result is SBCS data. The CCSID of the result is the same as the CCSID of the first argument.
  • If the first argument is mixed data and the length attribute of the result is greater than or equal to 4, then the result is mixed data. The CCSID of the result is the same as the CCSID of the first argument.
  • If the first argument is mixed data that is DBCS-open or DBCS-either and the length attribute of the result is less than 4, the CCSID of the result is the associated SBCS CCSID for the mixed data CCSID.

Graphic to Varchar

graphic-expression
An expression that returns a value that is a GRAPHIC, VARGRAPHIC, and DBCLOB data type. It must not be DBCS-graphic data.
length
An integer constant that specifies the length attribute for the resulting varying length character string. The value must be between 1 and 32740 (32739 if nullable). If the first argument contains DBCS data, the second argument cannot be less than 4.

If the second argument is not specified or DEFAULT is 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 (2.5*(n-1)) + 4.

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 character-expression is greater than the length attribute of the result, truncation is performed. A warning (SQLSTATE 01004) is returned unless the truncated characters were all blanks.

integer
An integer constant that specifies the CCSID of the result. It must be a valid SBCS CCSID or mixed data CCSID. If the third argument is an SBCS CCSID, then the result is SBCS data. If the third argument is a mixed CCSID, then the result is mixed data and the length attribute of the result cannot be less than 4. The third argument cannot be 65535.

If the third argument is not specified, the CCSID of the result is the default CCSID at the current server. If the default CCSID is mixed data and the length attribute of the result is greater than or equal to 4, then the result is mixed data. Otherwise, the result is SBCS data.

Datetime to Varchar

datetime-expression
An expression that is one of the following three built-in data types
date
The result is the varying-length character string representation of the date in the format specified by the second argument. If the second argument is not specified, the format used is the default date format. If the format is ISO, USA, EUR, or JIS, the length attribute and actual length of the result is 10. Otherwise the length attribute and actual length of the result is the length of the default date format. For more information see String representations of datetime values.
time
The result is the varying-length character string representation of the time in the format specified by the second argument. If the second argument is not specified, the format used is the default time format. The length attribute and actual length of the result is 8. For more information see String representations of datetime values.
timestamp
The second argument is not applicable and must not be specified.

The result is the varying-length character string representation of the timestamp. If datetime-expression is a TIMESTAMP(0), the length attribute and actual length of the result is 19. If the data type of datetime-expression is a TIMESTAMP(n), the length attribute and actual length of the result is 20+n. Otherwise, the length attribute and actual length of the result is 26.

The CCSID of the string is the default SBCS CCSID at the current server.
ISO, EUR, USA, or JIS
Specifies the date or time format of the resulting character string. For more information, see String representations of datetime values.
LOCAL
Specifies that the date or time format of the resulting character string should come from the DATFMT, DATSEP, TIMFMT, and TIMSEP attributes of the job at the current server. Use the Display Job (DSPJOB) command to determine the date and time formatting options in effect for the job.
Start of change

Boolean to Varchar

boolean-expression
An expression that returns a Boolean value.

The result is the varying-length character string representation of the Boolean value, either 'TRUE' or 'FALSE'. The length attribute is 5. If boolean-expression is TRUE, the actual length of the result is 4. If boolean-expression is FALSE, the actual length of the result is 5.

The CCSID of the string is the default SBCS CCSID at the current server.

End of change

Note

Syntax alternatives: The CAST specification should be used to increase the portability of applications when the first argument is a string and the length argument is specified. For more information, see CAST specification.

Example

  • Make EMPNO varying-length with a length of 10. 
      SELECT VARCHAR(EMPNO,10)
    INTO :VARHV
    FROM EMPLOYEE
1 A binary string is also allowed if a CCSID is not specified or if a CCSID of 65535 is explicitly specified.