Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what is the difference between common functions on SQLServer,Oracle,DB2". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

We know that different databases support different functions, some of which are generic and some unique to some databases.

How to compare the functions commonly used on SQLServer,Oracle,DB2

1. Character function:

Characters in SQLServer are not case sensitive. But the lower,upper function is common in SQLServer,Oracle and DB2.

Such as:

SELECTempno,ename,deptnoFROMempWHEREupper (ename) = upper ('blake')

Examples of connection characters:

SQLSERVER:select'Good'+'String'ORACLE:selectconcat ('Good','String') fromdual;DB2:selectconcat (' Good','String') fromsysibm.sysdummy1

Examples of functions that manipulate characters:

ORACLE and DB2 are written as follows:

SELECTename,CONCAT (ename,job), LENGTH (ename), INSTR (ename,'A') FROMempWHERESUBSTR (job,1,5) = 'SALES'

The equivalent syntax for SQLSERVER is as follows:

SQLSERVER:SELECTename, (ename+job), LEN (ename), PATINDEX ('% A% engineer gravity ename) FROMempWHERESUBSTRING (job,1,5) = 'SALES'

two。 Date function:

Take the current date:

SQLSERVER:selectgetdate () ORACLE:selectsysdatefromdual;DB2:SELECTcurrentdateFROMsysibm.sysdummy1

An example of the use of arithmetic operators in the previous issue:

Written on ORACLE:

SELECTename, (sysdate-hiredate) / 7weeksFROMempWHEREdeptno=10

Written on SQLSERVER:

SELECTename,datediff (day,hiredate,GETDATE ()) / 7weeksFROMempWHEREdeptno=10

Note:

The datepart () function returns the specified portion of time as an integer.

Usage: datepart (datepart,date)

How to compare the functions commonly used on SQLServer,Oracle,DB2

Parameter description: the time to be returned when datepart. Commonly used values are year, month, day, hour, and minute. Date is the specified time.

Example:

SELECTDATEPART (month,GETDATE ()) AS'MonthNumber'SELECTDATEPART (day,GETDATE ()) AS'MonthNumber'

The datediff () function returns the difference between two times calculated with the specified time portion. Returns an integer value. For example, the difference between 1991-6-12 and 1991-6-21 is 9 days, the difference between 1998-6-12 and 1999-6-23 is one year, and the difference between 1999-12-1 and 1999-3-12 is 9 months.

Usage: datediff (darepart,date1,date2)

Parameter description:

Datepart (ibid.)

Date1, date2 (ditto date)

Example:

Selectdatediff (month,'1991-6-12) asaselectdatediff (day,'1991-6-12) asa

Written on DB2:

SELECTename, (sysdate-hiredate) / 7weeksFROMempWHEREdeptno=10

Sysdate on DB2 is time-stamped. Cannot be used for calculation. However, if the statement does not report an error, the result is an error. Attention please!

The correct method:

SELECTename, (days (currentdate)-days (date (hiredate) / 7weeksFROMempWHEREdeptno=10

3. Conversion function:

An example of converting date to character on SQLSERVER:

SELECTename,convert (char (10), hiredate) HIREDATEFROMemp

Or:

SELECTename,cast (hiredateaschar (10)) HIREDATEFROMemp

Example of date character conversion on ORACLE:

SELECTename,TO_CHAR (hiredate,'fmDDMonthYYYY') HIREDATEFROMemp

Example of date character conversion on DB2:

Selectchar (currentdate) fromsysibm.sysdummy1;selectchar (currenttime) fromsysibm.sysdummy1

This is the end of the content of "what is the difference between the functions commonly used on SQLServer,Oracle,DB2". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.