Comparison Between Common DB2 functions and Oracle Functions

1. type conversion functions: decimal, double, Integer, smallint, real, Hex (arg), date, time, timestamp, char, and varchar

1. type conversion functions: decimal, double, Integer, smallint, real, Hex (arg), date, time, timestamp, char, and varchar

1. type conversion functions: decimal, double, Integer, smallint, real, Hex (arg ),

Date, time, timestamp, char, varchar

Common examples:

-- Convert to string type

Oracle: select to_char (15.5000) from dual;

DB2: select char (15.5000) from sysibm. sysdummy1

-- Convert to date and time

Oracle: select to_date ('1970-01-01 ', 'yyyy-mm-dd') from dual;

DB2: select date ('1970-01-01 ') from sysibm. sysdummy1

2. Date: year, quarter, month, week, day, hour, minute, second

Dayofyear (arg): returns the Daily Value of arg within the year.

Dayofweek (arg): returns the Daily Value of arg within a week.

Days (arg): returns the integer representation of the date, from-01-01.

Midnight_seconds (arg): the number of seconds between midnight and arg.

Monthname (arg): returns the month name of arg.

Dayname (arg): returns the week of arg.

Common examples:

-- Returns the current system time.

Oracle: select sysdate from dual;

DB2: select current date from sysibm. sysdummy1

-- Returns the next day of the current time (same as the year and month)

Oracle: select sysdate, sysdate + interval '1' day from dual;

DB2: select current date + 1 day from sysibm. sysdummy1

3. String functions:

Length, lcase, ucase, ltrim, rtrim

Coalesce (arg1, arg2 ....) : The first non-null parameter in the returned parameter set.

Concat (arg1, arg2): connects two strings: arg1 and arg2.

Insert (arg1, pos, size, arg2): returns one. arg1 is deleted from the pos,

Insert arg2 to this location.

Left (arg, length): returns the leftmost length string of arg.

Locate (arg1, arg2, : Find the first position where arg1 appears in arg2, specify pos,

The first position of arg1 appears at the pos of arg2.

Posstr (arg1, arg2): returns the position where arg2 first appeared in arg1.

Repeat (arg1, num_times): returns the string that arg1 is repeated for num_times.

Replace (arg1, arg2, arg3): replace all arg2 in arg1 with arg3.

Right (arg, length): returns a string consisting of the Left length bytes of arg.

Space (arg): returns a string containing arg spaces.

Substr (arg1, pos, : Returns the length starting from the pos position in arg1. If the length is not specified, the remaining characters are returned.

Common examples:

-- Null value processing function

Oracle: select nvl (null, 'aaa') from dual;

DB2: db2 select coalesce (comm, 1000) from staff;

-- Remove space:

Oracle: select trim ('abc') from dual; -- remove spaces at both ends by default

Select trim (heading ''from 'abc') from dual -- remove only spaces on the left side, or remove other characters

Select trim (trailing ''from 'abc') from dual -- removes only the right-side space and can be used to remove other characters

DB2:

Select ltrim (rtrim ('abc') from sysibm. sysdummy1

Select ltrim ('abc') from sysibm. sysdummy1

Select rtrim ('abc') from sysibm. sysdummy1

-- Returns an uppercase string value, which can be upper.

Oracle: select upper ('abc') from dual;

DB2: select upper ('abc') from sysibm. sysdummy1

Or

Select ucase ('abc') from sysibm. sysdummy1

-- Merge strings. Oracle and DB2 can both be the same as the following:

Oracle: select concat ('abcd', 'efg') from dual;

Select 'abcd' | 'efg' from dual;

DB2: select concat ('abcd', 'efg') from sysibm. sysdummy1

Select 'abcd' | 'efg' from sysibm. sysdummy1

4. mathematical functions:

Abs, count, max, min, sum

Ceil (arg): returns the smallest integer greater than or equal to arg.

Floor (arg): returns the smallest integer less than or equal to the parameter.

Mod (arg1, arg2): returns the remainder of arg1 divided by arg2. the symbol is the same as that of arg1.

Rand (): returns a random number between 1 and 10.

Power (arg1, arg2): returns the arg2 Power of arg1.

Round (arg1, arg2): rounding to truncation. arg2 is the number of digits. If arg2 is negative, rounding to the number before the decimal point.

Sigh (arg): returns the symbol indicator of arg. -1, 0, 1 indicates.

Truncate (arg1, arg2): truncates arg1. arg2 is the number of digits. If arg2 is a negative number, the arg2 before the decimal point of arg1 is retained.