What are the differences between common DB2 functions and Oracle functions?

Source: Internet
Author: User
Tags db2 functions month name

The following articles mainly introduce the comparison between common DB2 functions and Oracle functions. If you are curious about common DB2 functions and Oracle functions, this article will unveil the secrets of this article, the following is a detailed description of the main content of the article. I hope you will have a better understanding of it after browsing.

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

 
 
  1. date, time,timestamp,char, varchar 

Common examples:

-- Convert to string type

 
 
  1. Oracle: select to_char(15.5000) from dual;  
  2. DB2:  select char(15.5000) from sysibm.sysdummy1  

-- Convert to date and time

 
 
  1. Oracle: select to_date('2008-01-01','yyyy-mm-dd') from dual;  
  2. DB2:  select date('2008-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.

 
 
  1. Oracle: select sysdate from dual;  
  2. DB2:  select current date from sysibm.sysdummy1  

Returns the next day of the current time)

 
 
  1. Oracle: select sysdate,sysdate + interval '1' day  from dual;  
  2. DB2:  select current date + 1 day from sysibm.sysdummy1  

3. String functions:

 
 
  1. 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,: searches for the location where arg1 appears for the first time in arg2, specifying 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:

DB2 common functions: null value processing functions

 
 
  1. Oracle: select nvl(null,'aaa') from dual;  
  2. 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

 
 
  1. DB2:  
  2. select ltrim(rtrim(' abc ')) from sysibm.sysdummy1  
  3. select ltrim('  abc ') from sysibm.sysdummy1  
  4. select rtrim('  abc ') from sysibm.sysdummy1  

Common DB2 functions return string uppercase values, which can be upper

 
 
  1. Oracle: select upper('abc') from dual;  
  2. DB2:  select upper('abc') from sysibm.sysdummy1  

Or

 
 
  1. select ucase('abc') from sysibm.sysdummy1 

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

 
 
  1. Oracle: select concat('abcd','efg') from dual;  
  2. select 'abcd' || 'efg' from dual;  
  3. DB2:  select concat('abcd','efg') from sysibm.sysdummy1  
  4. select 'abcd' || 'efg' from sysibm.sysdummy1  

4. mathematical functions:

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

The above content is a comparison of common DB2 functions with Oracle. I hope you will find some gains.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.