--------- 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 ---------- Mathematical function
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
---------- Compare between numerical
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 Dual20. How to deal with NULL values (NULL in F2) S: SELECT F1, ISNULL (F2, 10) Value from TBL o: SELECT F1, NVL (F2, 10) Value from TBL
-------- Comparison between values
21. Ask Character number 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 Ask 刂 刂 鱿? Br> 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 Sql Server compares the difference between SoundEx to the difference between 0-4, 4 for the same sound, 1 highest -------------------------------------- Date 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 GMT 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 ---------------------------------------
