Mathematical function 1. absolute value s: select abs (-1) Value o: select abs (-1) Value from DUAL
2. Remove (large) s: select ceiling (-1.001) Value o: select ceil (-1.001) Value from Dual
3. Take a complete (small) s: select floor (-1.001) Value o: select floor (-1.001) Value from Dual
4. Repeating (intercept) S: SELECT CAST (-1.002 as int) Value o: select trunc (-1.002) Value from Dual
5. Since the four rounds of S: SELECT ROUND (1.23456, 4) Value 1.23460 o: Select Round (1.23456, 4) Value from Dual 1.2346
6.E The power S: SELECT Exp (1) Value 2.7182818284590451 o: SELECT Exp (1) Value from Dual 2.71828182
7. Take E for the end of the log: SELECT log (2.7182818284590451) Value 1 o: select ln (2.7182818284590451) Value from dual; 1
8. Take 10 for the end of the log number 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 S: SELECT SQRT (4) Value 2 o: SELECT SQRT (4) Value from Dual 2
11. Consoior the power of the bottom S: SELECT POWER (3, 4) Value 81 o: SELECT POWER (3, 4) Value from Dual 81
12. Take the random number s: select rand () value o: select sys.dbms_random.value (0,1) value from dual;
13. Take the symbol S: SELECT SIGN (-8) value -1 o: select sign (-8) value from dual -1
14. Volume Rate S: SELECT PI () Value 3.1415926535897931 o: I don't know
15.SIN, COS, TAN parameters are in an arc, for example: SELECT SIN (Pi () / 2) Value gets 1 (SQLServer)
16.Ansin, ACOS, Atan, Atan2 Return Arc
17. Arc angle interchange (SQL Server, Oracle does not know) DegRees: radians -> angle radians: angle -> radia
Number comparison
18. Ask for a set 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. Ask for a collection Minimum S: Select Min (Value) Value from (SELECT 1 VALUE UNION SELECT-VALUE UNION SELECT 4 VALUE UNION SELECT 3 VALUE) A
O: SELECT Least (1, -2, 4, 3) Value from Dual
20. How to deal with NULL values (NULL in F2 is replaced by 10) S: SELECT F1, ISNULL (F2, 10) Value from TBL o: SELECT F1, NVL (F2, 10) Value from TBL21. SELECT SYSTEM S: SELECT ASCII ('A') Value O: SELECT ASCII ('A') Value from DUAL
22. Squiracy from the serial number S: SELECT Char (97) Value o: SELECT CHR (97) Value from DUAL
23. Connect S: SELECT '11' '22' '33' Value o: select contat ('11 ',' 22 ') || 33 Value from Dual
23. Subtrive position - Return 3 S: SELECT Charindex ('s', 'SDSQ', 2) Value o: SELECT INSTR ('SDSQ', 'S', 2) Value from Dual
23. The position of the fuzzy string - return 2, the parameter removes the middle% return 7 S: SELECT PATINDEX ('% D% q%', 'sdsfasdqe') Value o: Oracle did not find, but INSTR can pass the fourth Parameter controls SELECT INSTR ('sdsfasdqe', 'sd', 1, 2) Value from DUAL Returns 6
24. Subside s: Select Substring ('Abcd', 2, 2) Value O: Select Substr ('Abcd', 2, 2) Value from Dual
25. Subtrings instead of returning Aijklmnef S: Select Stuff ('Abcdef', 2, 3, 'ijklmn') Value O: SELECT Replace ('Abcdef', 'BCD', 'Ijklmn') Value from DUAL
26. Subrings replace S: Did not find o: select translate ('fasdbfasegas', 'fa', 'I') Value from Dual
27. Length S: Len, Datanceth o: Length
28. Size transformation Lower, Upper
29. The first letter of the word S: did not find o: select initcap ('Abcd DSAF DF') Value from Dual
30. Left-specific spaces (LPAD's first parameter is spaced with space functions) S: SELECT SPACE (10) 'Abcd' Value O: SELECT LPAD ('Abcd', 14) Value from DUAL
31. Right-tubular space (the first parameter of RPAD is spaced with space function) S: SELECT 'ABCD' SPACE (10) Value o: SELECT RPAD ('Abcd', 14) Value from DUAL
32. Delete spaces S: LTRIM, RTRIM O: LTRIM, RTRIM, TRIM
33. Repeat strings S: SELECT Replicate ('Abcd', 2) Value o: Did not find
34. Pronunciation Similarity Compare (the same, the same, the same, the same pronunciation) S: Select Soundex ('Smith'), SOUNDEX ('SMYTHE') O: SELECT SOUNDEX ('Smith'), Soundex ('SMYTHE') From Dual SQLServer compares the difference between SoundEx to compare the difference between 0-4, 4 as the homogenus, 1 function 35. System time s: select getdate () Value o: select sysdate value from Dual
36. Directly added to the integer before and after a few days
37. Search 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. Seeking time S: SELECT Convert (char (8), getdate (), 108) Value o: select to_char (sysdate, 'hh24: mm: ss') Value from Dual
39. Other parts of date time S: datepart and dateename function (first parameter decision) o: to_Char function second parameter decision
Parameters --------------------------------- The following table Need to add Year YY, YYYY Quarter QQ, Q (Quarter) Month MM, M (m o) Dayofyear Dy, Y (o Table Week) Day DD, D (D O invalid) Week wk, ww (wk o invalid) Weekday DW (O unclear) HOUR HH, HH12, HH24 (HH12 , HH24 S is invalid) Minute Mi, N (N o 无效) Second SS, S (S O invalid) MilliseCond MS (无 无效) --------------------- -------------------------
40. On the last day of the month S: I don't know o: select last_day (sysdate) Value from DUAL
41. One day in this week (such as Sunday) s: I don't know o: select next_day (sysdate, 7) vaule from dual;
42. String Time S: You can transfer 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. Seeking differences (such as seconds) s: select datediff (s, getdate (), getdate () 12.3) Value O: Download (for example, D1-D2 = 12.3) Select (for example, D1-D2 = 12.3) D1-D2) * 24 * 60 * 60 Vaule from Dual
44. According to the new date (such as minutes) s: select dateadd (MI, 8, getdate ()) Value o: SELECT SYSDATE 8/60/24 VAULE from Dual;
45. Ask for different time s: I don't know o: select new_time (sysdate, 'ydt', 'gmt') vaule from dual; ----- Time zone parameters, Beijing in the east 8 District should be YDT ----- - AST ADT Atlantic Standard Time BST BDT White Order Sea Standard Time CST CDT Central Standard Time EST EDT Eastern Standard Time GMT GMT East Standard 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
