SQL CHAR scalar function in DB2 for i SQL

SQL CHAR scalar function in DB2 for i SQL

CHAR

The CHAR function returns a fixed length character string of the passed expression. The passed expression can be of different data types and its syntax differs with different Input data types.

Integer to Character

CHAR(integer-expression)

Integer expression can be SMALLINT, INTEGER, or BIGINT

Example#1:

SELECT char(00100) FROM SYSIBM.SYSDUMMY1

Output:

CHAR ( 001 )
100         

Decimal to Character

CHAR(decimal-expression, decimal character)

2nd parameter to this function is optional to pass and when not passed then default period (.) character is used to represent decimal in results.

Example#2:

SELECT char(010.01,',') FROM SYSIBM.SYSDUMMY1

Output:

CHAR
10,01

The first parameter is an decimal data type expression, and the second parameter is the single byte character constant and it cannot be digit, plus sign(+), minus sign(-) or a blank. If not passed then the default is period character (.). Leading zeros are not included. Trailing zeros are included. If decimal-expression is negative then the first character of the result is a minus sign otherwise the first character is a digit or the decimal character.

Or, we can ignore passing second parameter with decimal-expression.

SELECT char(-010.01) FROM SYSIBM.SYSDUMMY1

Output:

CHAR ( - 010.00 )
     -10.00      

Floating-point to Character

CHAR(floating-point-expression, decimal-character)

Floating point expression can be DOUBLE or REAL

2nd parameter to this function is optional to pass and when not passed then default period (.) character is used to represent decimal in results.

Example#3:

SELECT char(-7.2E+75,',') FROM SYSIBM.SYSDUMMY1

Output:

CHAR   
-7,2E75

The first parameter is an floating type expression, and the second parameter is the single byte character constant and it cannot be digit, plus sign(+), minus sign(-) or a blank. If not passed then the default is period character (.). Leading zeros are not included. Trailing zeros are included. If floating-point-expression is negative then the first character of the result is a minus sign otherwise the first character is a digit or the decimal character.

Or, we can ignore passing second parameter with floating-point-expression.

SELECT char(-7.2E+75) FROM SYSIBM.SYSDUMMY1

Output:

CHAR   
-7.2E75

Decimal floating-point to Character

CHAR(decimal-floating-point-expression, decimal-character)

Decimal Floating point expression can be DECFLOAT

2nd parameter to this function is optional to pass and when not passed then default period (.) character is used to represent decimal in results.

Example#4:

SELECT char(-894.545442E-34,',') FROM SYSIBM.SYSDUMMY1

Output:

CHAR                   
-8,9454544200000005E-32

The first parameter is an decimal floating type expression, and the second parameter is the single byte character constant and it cannot be digit, plus sign(+), minus sign(-) or a blank. If not passed then the default is period character (.). Leading zeros are not included. Trailing zeros are included. If decimal-floating-point-expression is negative then the first character of the result is a minus sign otherwise the first character is a digit or the decimal character.

Or, we can ignore passing second parameter with decimal-floating-point-expression.

SELECT char(-894.545442E-34) FROM SYSIBM.SYSDUMMY1

Output:

CHAR                   
-8.9454544200000005E-32

Character to Character

CHAR(character-expression, integer, codeunits16/CodeUnits32/Octets)

2nd parameter to this function is optional to pass and when not passed then length attribute of the character-expression in first parameter is assumed.

Example#5:

SELECT char('TESTDATA',4) FROM SYSIBM.SYSDUMMY1

Output:

CHAR
TEST

The first four characters are returned.

Or, we can ignore passing second parameter with character-expression.

SELECT char('TESTDATA') FROM SYSIBM.SYSDUMMY1

Output:

CHAR ( 'TESTDATA' )
     TESTDATA      

Graphic to Character

CHAR(graphic-expression, integer, codeunits16/CodeUnits32)

1st parm is graphic string, 2nd parm is optional and length of data to be returned is mentioned here, 3rd parm is optional and here we need to specify integer ccsid

Execute below queries to understand this.

CREATE TABLE QTEMP/TABLE1 (NAME GRAPHIC ( 10) CCSID 1200 NOT NULL
WITH DEFAULT);

INSERT INTO QTEMP/TABLE1 VALUES('TEST DATA');

Example#6

SELECT char(name,5, 1208) FROM qtemp.table1

Output:

CHAR
TEST

Datetime to Character

CHAR(datetime-expression, ISO/USA/EUR/JIS/LOCAL)

Datetime expression can be date, time, or timestamp

Example#7:

Timestamp to character

SELECT char('2005-11-16-15.11.15.544784') FROM SYSIBM.SYSDUMMY1

Output:

CHAR                      
2005-11-16-15.11.15.544784

Date to character

SELECT char(DATE('2005-11-16'), USA) FROM SYSIBM.SYSDUMMY1

Output:

CHAR conversion
  11/16/2005   

Time to character

SELECT char(TIME('15:10:25'), USA) FROM SYSIBM.SYSDUMMY1

Output:

CHAR conversion
   03:10 PM    

RowId to Character

CHAR(rowid-expression)

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

Example#8:

CREATE TABLE easyclass1.TABLE1 (NUMBER ROWID , NAME CHAR ( 10) 
NOT NULL WITH DEFAULT);

INSERT INTO EASYCLASS1.TABLE1 (NAME) VALUES('TESTNAME');

SELECT char(NUMBER), number as rowidfield, name FROM easyclass1.table1;

Please execute above queries from Run SQL Script ACS and see the result at your end.