SQL Reference

CHAR

Datetime to Character:

>>-CHAR--(--datetime-expression----+---------------+--)--------><
                                   '-,--+-ISO---+--'
                                        +-USA---+
                                        +-EUR---+
                                        +-JIS---+
                                        '-LOCAL-'

Character to Character:

>>-CHAR--(--character-expression----+-------------+--)---------><
                                    '-,--integer--'

Integer to Character:

>>-CHAR--(--integer-expression--)------------------------------><

Decimal to Character:

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

Floating-point to Character:

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

The schema is SYSIBM. However, the schema for CHAR(floating-point-expression) is SYSFUN.

The CHAR function returns a character-string representation of a:

Datetime value if the first argument is a date, time or timestamp
Character string value if the first argument is any type of character string
Integer number if the first argument is a SMALLINT, INTEGER or BIGINT
Decimal number if the first argument is a decimal number
Double-precision floating-point number if the first argument is a DOUBLE or REAL.

The result of the function is a fixed-length character 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.

Datetime to Character

datetime-expression

An expression that is one of the following three 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 occurs 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 occurs if the second argument is specified and is not a valid value (SQLSTATE 42703).
timestamp: The second argument is not applicable and must not be specified. SQLSTATE 42815 The result is the character string representation of the timestamp. The length of the result is 26.

The code page of the string is the code page of the database at the application server.

Character to Character

character-expression: An expression that returns a value that is CHAR, VARCHAR, LONG VARCHAR, or CLOB data type.
integer: the length attribute for the resulting fixed length character string. The value must be between 0 and 254.

If the length of the character-expression is less than the length attribute of the result, the result is padded with blanks up to the length of the result. If the length of the character-expression is greater than the length attribute of the result, truncation is performed. A warning is returned (SQLSTATE 01004) unless the truncated characters were all blanks and the character-expression was not a long string (LONG VARCHAR or CLOB).

Integer to Character

integer-expression: An expression that returns a value that is an integer data type (either SMALLINT, INTEGER or BIGINT).

The result is the character string representation 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 first argument is a small integer:
The length of the result is 6. If the number of characters in the result is less than 6, then the result is padded on the right with blanks to length 6.
If the first argument is a large integer:
The length of the result is 11. If the number of characters in the result is less than 11, then the result is padded on the right with blanks to length 11.
If the first argument is a big integer:
The length of the result is 20. If the number of characters in the result is less than 20, then the result is padded on the right with blanks to length 20.

The code page of the string is the code page of the database at the application server.

Decimal to Character

decimal-expression: An expression that returns a value that is a decimal data type. If a different precision and scale is desired, the DECIMAL scalar function can be used first 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 cannot be a digit, plus ('+'), minus ('-') or blank. (SQLSTATE 42815). The default is the period ('.') character

The result is the fixed-length character-string representation of the argument. The result includes a decimal character and p digits, where p is the precision of the decimal-expression with a preceding minus sign if the argument is negative. The length of the result is 2+p, where p is the precision of the decimal-expression. This means that a positive value will always include one trailing blank.

The code page of the string is the code page of the database at the application server.

Floating-point to Character

floating-point-expression: An expression that returns a value that is a floating-point data type (DOUBLE or REAL).

The result is the fixed-length character-string representation of the argument in the form of a floating-point constant. The length of the result is 24. 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 value is zero, the result is 0E0. Otherwise, the result includes 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 number of characters in the result is less than 24, then the result is padded on the right with blanks to length 24.

The code page of the string is the code page of the database at the application server.

Examples:

Assume the column PRSTDATE has an internal value equivalent to 1988-12-25.
```
 CHAR(PRSTDATE, USA)
```
Results in the value '12/25/1988'.
Assume the column STARTING has an internal value equivalent to 17.12.30, the host variable HOUR_DUR (decimal(6,0)) is a time duration with a value of 050000. (that is, 5 hours).
```
CHAR(STARTING, USA)
```
Results in the value '5:12 PM'.
```
  CHAR(STARTING + :HOUR_DUR, USA)
```
Results in the value '10:12 PM'.
Assume the column RECEIVED (timestamp) has an internal value equivalent to the combination of the PRSTDATE and STARTING columns.
```
  CHAR(RECEIVED)
```
Results in the value '1988-12-25-17.12.30.000000'.
Use the CHAR function to make the type fixed length character and reduce the length of the displayed results to 10 characters for the LASTNAME column (defined as VARCHAR(15)) of the EMPLOYEE table.
```
  SELECT CHAR(LASTNAME,10) FROM EMPLOYEE
```
For rows having a LASTNAME with a length greater than 10 characters (excluding trailing blanks), a warning that the value is truncated is returned.
Use the CHAR function to return the values for EDLEVEL (defined as smallint) as a fixed length character string.
```
  SELECT CHAR(EDLEVEL) FROM EMPLOYEE
```
An EDLEVEL of 18 would be returned as the CHAR(6) value '18 ' (18 followed by four blanks).
Assume that STAFF has a SALARY column defined as decimal with precision of 9 and scale of 2. The current value is 18357.50 and it is to be displayed with a comma as the decimal character (18357,50).
```
  CHAR(SALARY, ',')
```
returns the value '00018357,50 '.
Assume the same SALARY column subtracted from 20000.25 is to be displayed with the default decimal character.
```
  CHAR(20000.25 - SALARY)
```
returns the value '-0001642.75'.
Assume a host variable, SEASONS_TICKETS, has an integer data type and a 10000 value.
```
   CHAR(DECIMAL(:SEASONS_TICKETS,7,2))
```
Results in the character value '10000.00 '.
Assume a host variable, DOUBLE_NUM has a double data type and a value of -987.654321E-35.
```
   CHAR(:DOUBLE_NUM)                                       
```
Results in the character value of '-9.87654321E-33 '. Since the result data type is CHAR(24), there are 9 trailing blanks in the result.

[ Top of Page | Previous Page | Next Page ]