Summary of common functions of DB2

Recently with DB2, the difference between the database is still very large, a lot of functions are not the same

1. Go to Space
*DB2: Ltrim () is to go to the left space, RTrim () is to go to the right space.
*informix: Trim () is the space to go.
Usage: Example: string a= "abc";
*DB2: Ltrim (a) = "abc"; RTrim (a) = "abc", RTrim (Ltrim (a)) = "ABC";
*informix: Trim (a) = "abc".
Note: There is no trim () function in DB2, so go to space to execute left and right.

2. If the value is null, substitute a value for value (parm,string) or COALESCE (parm,string) to take the field from the table Parm, if it is a null value, use string instead.
*DB2: Value (parm,string) or coalesce (parm,string)
In *informix: NVL (parm,string).
Usage: Example:
*DB2: Select value (name, ' hehe ') from Student;select coalesce (name, ' hehe ') from student;
If the Name field in the student table is a null value, the ' hehe ' is displayed, and the value of the Name field is displayed if there is a value
*informix: Select NVL (name, ' hehe ') from student

Note: There is no trim () function in DB2, so go to space to execute left and right.

3.repeat (Parm,int z), repeat the field after Z output
*DB2: Select name from Student,select repeat (name,2) from student
The first sentence output is: ' hehe ',
The second sentence output is: ' hehe hehe '

4. Insert multiple rows into the table
*DB2: INSERT INTO table1 (select Parm1,parm2 ... from table2)
* Note: You must take the same multi-field value from the Table2 as the Table1.

5.CASE when

Select Case when maintain = ' 0 ' and then ' a ' when maintain = ' 1 ' and ' a ' when maintain was NULL then ' a ' ELSE maintain END From V_repairpart