SQL and ORACLE function is relatively

2010-11-09  来源:本站原创  分类:Database  人气:110 

SQLServer and Oracle databases are all frequently used, to thank the authors conclude that these commonly used functions for your reference.

Mathematical functions:

1 Absolute S: SELECT abs (-1) value
O: SELECT abs (-1) value from dual

(2) rounded (large)
S: SELECT ceiling (-1.001) value
O: SELECT ceil (-1.001) value from dual

3 rounded (small)
S: SELECT floor (-1.001) value
O: SELECT floor (-1.001) value from dual

4 rounded (intercept)
S: SELECT cast (-1.002 as int) value
O: SELECT trunc (-1.002) value from dual

5 rounded S: SELECT round (1.23456,4) value 1.23460
O: SELECT round (1.23456,4) value from dual 1.2346

6.e the end of the power S: SELECT Exp (1) value 2.7182818284590451
O: SELECT Exp (1) value from dual 2.71828182

7. Take the logarithm base e S: SELECT log (2.7182818284590451) value 1
O: SELECT ln (2.7182818284590451) value from dual; 1

8 of 10 for the end of the number of S: SELECT log10 (10) value 1
O: SELECT log (10,10) value from dual; 1

9 Take the square S: SELECT SQUARE (4) value 16
O: SELECT power (4,2) value from dual 16

10 take the square root of S: SELECT SQRT (4) value 2
O: SELECT SQRT (4) value from dual 2

11 seeking to end the power of an arbitrary number of S: SELECT power (3,4) value 81
O: SELECT power (3,4) value from dual 81

12 to take a random number S: SELECT rand () value
O: SELECT sys.dbms_random.value (0,1) value from dual;

13. Check mark S: SELECT sign (-8) value -1
O: SELECT sign (-8) value from dual -1

14. Pi S: SELECT PI () value 3.1415926535897931
O: do not know

15.sin, cos, tan parameters are in radians, for example: SELECT sin (PI () / 2) value to be 1 (SQLServer)

16.Asin, Acos, Atan, Atan2 return arc

17. Radian angle swap (SQLServer, Oracle does not know)
DEGREES: arc -> point RADIANS: point -> radians

Numerical comparisons:

18. Find a collection of maximum S: SELECT max (value) value from
(SELECT 1 value
union
SELECT -2 value
union
SELECT 4 value
union
SELECT 3 value) a

O: SELECT greatest (1, -2,4,3) value from dual

19 minimum requirements set S: SELECT min (value) value from
(SELECT 1 value
union
SELECT -2 value
union
SELECT 4 value
union
SELECT 3 value) a

O: SELECT least (1, -2,4,3) value from dual

20 How to handle null values ​​(F2 in null instead of 10)
S: SELECT F1, IsNull (F2, 10) value from Tbl
O: SELECT F1, nvl (F2, 10) value from Tbl

21 seeking character serial number S: SELECT ascii ('a') value
O: SELECT ascii ('a') value from dual

22 characters from the serial number request S: SELECT char (97) value
O: SELECT chr (97) value from dual

23 Connect S: SELECT '11 '+ '22' + '33 'value
O: SELECT CONCAT ('11 ', '22') 33 value from dual

23 sub-string position - returns 3
S: SELECT CHARINDEX ('s', 'sdsq', 2) value
O: SELECT INSTR ('sdsq', 's', 2) value from dual

23 fuzzy string position - returns 2, the parameter 7 is returned to remove the middle%
S: SELECT patindex ('% d% q%', 'sdsfasdqe') value
O: oracle not found, but the instr parameter control by the fourth number of occurrences SELECT INSTR ('sdsfasdqe', 'sd', 1,2) value from dual returns 6

24. Praying string S: SELECT substring ('abcd', 2,2) value
O: SELECT substr ('abcd', 2,2) value from dual

25 sub-string instead of returning aijklmnef
S: SELECT STUFF ('abcdef', 2, 3, 'ijklmn') value
O: SELECT Replace ('abcdef', 'bcd', 'ijklmn') value from dual

26. Substring replace all S: not found O: SELECT Translate ('fasdbfasegas', 'fa', 'I') value from dual

27 Length S: len, datalength
O: length

28 case conversion lower, upper

29 words capitalized S: not found O: SELECT INITCAP ('abcd dsaf df') value from dual

30. Left up space (LPAD first parameter is the same space as the space function)
S: SELECT space (10) + 'abcd' value
O: SELECT LPAD ('abcd', 14) value from dual

31. Right up space (RPAD The first argument is the same space as the space function)
S: SELECT 'abcd' + space (10) value
O: SELECT RPAD ('abcd', 14) value from dual

32 Delete the spaces S: ltrim, rtrim
O: ltrim, rtrim, trim

33 Repeat the string S: SELECT REPLICATE ('abcd', 2) value
O: not found

34 pronunciation similarity comparison (the same as the return value of these two words, pronounced the same)
S: SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe')
O: SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe') from dual
SQLServer using SELECT DIFFERENCE ('Smithers', 'Smythers') comparing the difference soundex return 0-4,4 as homonyms, 1 up

Date of function:

35 system time S: SELECT getdate () value
O: SELECT sysdate value from dual

36 before and after a few days directly with integer addition and subtraction

37. Seeking date S: SELECT convert (char (10), getdate (), 20) value
O: SELECT trunc (sysdate) value from dual
SELECT to_char (sysdate, 'yyyy-mm-dd') value from dual

38 seek time S: SELECT convert (char (8), getdate (), 108) value
O: SELECT to_char (sysdate, 'hh24: mm: ss') value from dual

39 date and time to take the rest of S: DATEPART and DATENAME function (first parameter decision)
O: to_char function to determine the second parameter

Parameters --------------------------------- The following table need to add year yy, yyyy
quarter qq, q (quarter)
month mm, m (m O invalid)
dayofyear dy, y (O table weeks)
day dd, d (d O invalid)
week wk, ww (wk O invalid)
weekday dw (O is unclear)
Hour hh, hh12, hh24 (hh12, hh24 S invalid)
minute mi, n (n O invalid)
second ss, s (s O invalid)
millisecond ms (O invalid)
----------------------------------------------

40 the last day of the month S: do not know O: SELECT LAST_DAY (sysdate) value from dual

41. One day this week (for example, Sunday)
S: do not know O: SELECT Next_day (sysdate, 7) vaule FROM DUAL;

42 string to time S: can be transferred directly or SELECT cast ('2004-09-08 'as datetime) value
O: SELECT To_date ('2004-01-05 22:09:38 ',' yyyy-mm-dd hh24-mi-ss') vaule FROM DUAL;

43. Seek a certain part of the difference between two dates (such as seconds)
S: SELECT datediff (ss, getdate (), getdate () +12.3) value
O: directly subtract two dates (such as d1-d2 = 12.3)
SELECT (d1-d2) * 24 * 60 * 60 vaule FROM DUAL;

44 According to the difference between the novelty of the date (such as minutes)
S: SELECT dateadd (mi, 8, getdate ()) value
O: SELECT sysdate +8/60/24 vaule FROM DUAL;

45 seek time zones time S: do not know O: SELECT New_time (sysdate, 'ydt', 'gmt') vaule FROM DUAL;

----- Time zone parameters, Beijing in the East Area 8 should be Ydt -------
AST ADT Atlantic Standard Time BST BDT Bering Standard Time Central Standard Time CST CDT EST EDT Eastern Standard Time GMT Greenwich Mean Time HST HDT Alaska? Hawaii Standard Time MST MDT Mountain Standard Time NST Newfoundland Standard Time PST PDT Pacific Standard Time YST YDT YUKON Standard Time

相关文章
  • SQL and ORACLE function is relatively 2010-11-09

    SQLServer and Oracle databases are all frequently used, to thank the authors conclude that these commonly used functions for your reference. Mathematical functions: 1 Absolute S: SELECT abs (-1) value O: SELECT abs (-1) value from dual (2) rounded (l

  • ORACLE SQL statement and function more 2011-04-12

    SQL and ORACLE function is relatively 2008-01-15 Author: wxinw Source: CSDN SQLServer and Oracle databases are all frequently used, to thank the authors conclude that these commonly used functions for your reference. Mathematical functions: 1 Absolut

  • Oracle sql in a function that multi-branch selection 2010-12-31

    1.'s Recent look at sql stuff, today saw an oracle function decode. Look at an example. select sv.service_code, sv.domain_name, decode(sv.service_state, '02', ' Service period ', '04', ' Not filing ', '03', ' Renew period ', ' Other ') from service_v

  • ORACLE function Daquan 2010-03-02

    SQL in single-record function 1.ASCII Returns the character corresponding to the specified decimal number; SQL> select ascii ('A') A, ascii ('a') a, ascii ('0 ') zero, ascii (' ') space from dual; AA ZERO SPACE --------- --------- --------- ---------

  • [Memo] Oracle function Daquan 2010-03-03

    SQL in single-record function 1.ASCII Returns the character corresponding to the specified decimal number; SQL> select ascii ('A') A, ascii ('a') a, ascii ('0 ') zero, ascii (' ') space from dual; AA ZERO SPACE --------- --------- --------- ---------

  • ORACLE Function Summary 2010-03-08

    SQL in single-record function 1.ASCII Returns the character corresponding to the specified decimal number; SQL> select ascii ('A') A, ascii ('a') a, ascii ('0 ') zero, ascii (' ') space from dual; AA ZERO SPACE --------- --------- --------- ---------

  • SQL Server, Oracle, DB2 database, SQL statements more 2010-04-21

    SQL Server, Oracle, DB2 database, SQL statements more 1.1.1 fetch the first n records SQL Server: Select top n * from xtable Oracle: Select * from xtable where rownum <= n DB2: Select * from xtable fetch first n rows only 1.1.2 take the current date

  • collection of oracle function 2010-04-29

    In the operation of the database is frequently used functions, are summarized in the above SQL in single-record function 1.ASCII Returns the character corresponding to the specified decimal number; SQL> select ascii ('A') A, ascii ('a') a, ascii ('0

  • Oracle function Daquan (string function, mathematical function, the date function, logic operation function, other functions) 2010-05-12

    SQL in single-record function 1.ASCII Returns the character corresponding to the specified decimal number; SQL> select ascii ('A') A, ascii ('a') a, ascii ('0 ') zero, ascii (' ') space from dual; AA ZERO SPACE --------- --------- --------- ---------

  • Analysis of the implementation plan optimization SQL <3> ORACLE implementation plan (transfer) 2010-05-27

    Background: In order to better carry out the following elements we must understand some conceptual terms: Sql statement to not share the same The SQL statement parsing Zhong Fu (Yinweijiexi Caozuobijiao costs of resources, can cause performance degra

  • [Change] ORACLE function Daquan 2010-06-11

    Articles Category: Database SQL in single-record function 1.ASCII Returns the character corresponding to the specified decimal number; SQL> select ascii ('A') A, ascii ('a') a, ascii ('0 ') zero, ascii (' ') space from dual; AA ZERO SPACE --------- -

  • C # connection Access.SQL Server.Oracle.MySQL.DB2 and SyBase six different database source code 2010-06-17

    This article lists the C # to connect Access, SQL Server, Oracle, MySQL, DB2 and SyBase six different database programs and the need to pay attention to the point source. 1.C # Connection Access Code: using System.Data; using System.Data.OleDb; strin

  • C # connection Access.SQL Server.Oracle.MySQL.DB2 and SyBase six different database procedure source 2010-06-17

    This article lists the C # to connect Access, SQL Server, Oracle, MySQL, DB2 and SyBase six different database programs and the need to pay attention to the point source. 1.C # Connection Access Code: using System.Data; using System.Data.OleDb; strin

  • oracle function manual (with examples) 2010-07-30

    oracle function manual SQL in single-record function 1.ASCII Returns the character corresponding to the specified decimal number; SQL> select ascii ("A") A, ascii ("a") a, ascii ("0") zero, ascii ("") space from

  • Java Oracle function calls 2010-08-12

    oracle stored procedures and functions different from 1, can use out parameters to return multiple values, the general principle is that there are multiple return values using the process; if there is only one return value, use the function 2, the pr

  • SQL Server, Oracle, DB2 database, SQL statements more (Reprinted) 2010-09-01

    Reprinted: honeydh (http://www.51testing.com/?uid-191521-action-viewspace-itemid-101702) 1.1.1 fetch the first n records SQL Server: Select top n * from xtable Oracle: Select * from xtable where rownum <= n DB2: Select * from xtable fetch first n row

  • Oracle function: DECODE () function 2010-09-06

    DECODE() Function, it will enter the value and function compared to the parameter list , Returns a value corresponding to the input value. Function parameter list is the result of some numerical value and its corresponding dual form consisting of a n

  • SQL in single-record function 2010-09-15

    SQL in single-record function 1.ASCII return to the specified number of characters corresponding to decimal; SQL> select ascii ('A') A, ascii ('a') a, ascii ('0 ') zero, ascii (' ') space from dual; AA ZERO SPACE --------- --------- --------- -------

  • oracle function Xiangjie 2010-09-19

    1, SQL statement based on the following SQL statement is given a basic introduction. 1.1, SQL statement, the operator uses examples of symbols + - Indicates positive or negative, positive, eliminating the need for + -1234.56 + The sum of two numbers

  • SQL Server, Oracle, DB2 database, comparing SQL statement 2010-10-26

    Source: http://www.cnblogs.com/cwy173/archive/2010/09/20/1831611.html Reprinted from: Long tangwan 1.1.1 fetch the first n records SQL Server: Select top n * from xtable Oracle: Select * from xtable where rownum <= n DB2: Select * from xtable fetch f