--------- Mathematical function
1. Absolute value s: select abs (-1) Valueo: 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 Since the Self-owned S: SELECT ROUND (1.23456, 4) Value 1.23460O: 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 logarithmic s: select log (2.7182818284590451) Value 1o: Select LN (2.7182818284590451) Value from dual; 1
8. Take 10 for the end log: SELECT log10 (10) Value 1o: SELECT log (10, 10) Value from Dual; 1
9. Take the square S: Select Square (4) Value 16o: SELECT POWER (4, 2) Value from Dual 16
10. Take the square root S: SELECT SQRT (4) Value 2O: SELECT SQRT (4) Value from Dual 2
11. Consister for the power S: SELECT POWER (3, 4) Value 81o: 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 -1o: select Sign (-8) Value from dual -1
---------- Triangle function
14. Volume Rate S: SELECT PI () Value 3.1415926535897931o: 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 value (NULL in F2 is replaced by 10) S: SELECT F1, ISNULL (F2, 10) Value from Tblo: SELECT F1, NVL (F2, 10) Value from TBL
-------- String function
21. Ask Characters S: SELECT ASCII ('A') Valueo: SELECT ASCII ('A') Value from Dual
22. Squiracy s: select char (97) Valueo: SELECT CHR (97) Value from Dual
23. Connection S: SELECT '11' '22' '33' Valueo: Select Concat ('11 ',' 22 ') || 33 Value from Dual
23. Subside position - Return 3s: 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 intermediate% return 7s: select Patindex ('% D% q%', 'sdsfasdqe') Value o: Oracle did not find, but INSTR can pass the fourth parameters Control the number of occurrences 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. Subsidence replacement returns Aijklmnefs: select stuff ('Abcdef', 2, 3, 'ijklmn') Valueo: 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, Datalengtho: 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-completed spaces (the first parameter of LPAD is spaced with space functions) S: SELECT SPACE (10) 'Abcd' Valueo: SELECT LPAD ('Abcd', 14) Value from DUAL
31. Right-tubular space (RPAD's first parameter is spaced with space function) S: SELECT 'abcd' space (10) Valueo: SELECT RPAD ('Abcd', 14) Value from DUAL
32. Delete spaces S: LTRIM, RTRIMO: 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 in Select Difference ('Smithers', 'Smythers') Compare SoundEx's difference Return 0-4, 4 is the same sound, 1 highest ------------- Date function
35. System time s: select getdate () Valueo: Select sysdate value from DUAL
36. Directly added to the integer before and after a few days
37. Ask the date S: Select Convert (Char (10), getdate (), 20) Valueo: 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) Valueo: 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 turn directly or Select Cast ('2004-09-08'as datetime) VALUEO: SELECT TO_DATE (' 2004-01-05 22:09:38 ',' YYYY-MM-DD HH24 -mi-ss') vaule from dual;
43. Seeking a certain part of a part of a part (such as second) s: select datediff (ss, getdate (), getdate () 12.3) Valueo: Directly subtract directly (such as D1-D2 = 12.3) SELECT (D1 -d2) * 24 * 60 * 60 vaule from dual;
44. According to the new date (such as minutes) S: Select DateAdd (MI, 8, getDate ()) Valueo: 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 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
