--------- Mathematical function
1. Absolute value S: SELECT ABS (-1) Value SELECT ABS (-1) Value from DUAL
2. Remove (large) s: select ceiling (-1.001) Value SELECT CEIL (-1.001) Value from DUAL
3. Take a complete (small) s: select floor (-1.001) Value SELECT FLOOR (-1.001) Value from DUAL
4. Repeating (intercept) S: SELECT CAST (-1.002 as int) value select trunc (-1.002) Value from DUAL
5. Since the top 20 into S: SELECT ROUND (1.23456, 4) Value 1.23460 Select Round (1.23456, 4) Value from Dual 1.2346
6.E Based S: SELECT Exp (1) Value 2.7182818284590451 Select Exp (1) Value from Dual 2.71828182
7. Take E for the end of the log: Select Log (2.7182818284590451) Value 1 Select LN (2.7182818284590451) Value from dual; 1
8. Take 10 for the bottom log S: SELECT log10 (10) Value 1 Select log (10, 10) Value from Dual; 1
9. Take the square S: Select Square (4) Value 16 Select Power (4, 2) Value from Dual 16
10. Take the square root S: SELECT SQRT (4) Value 2 Select SQRT (4) Value from Dual 2
11. Asking for the power S: SELECT POWER (3, 4) Value 81 Select Power (3, 4) Value from Dual 81
12. Take the number of randhes S: select rand () value select sys.dbms_random.value (0,1) value from dual;
13. Take symbol S: SELECT SIGN (-8) value -1 select sign (-8) value from dual -1 ---------- Mathematical function
14. Volume Rate S: SELECT PI () Value 3.1415926535897931 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
SELECT GRETEST (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
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 SELECT F1, NVL (F2, 10) Value from TBL -------- Numerical Interlayer
21. Ask Characters S: SELECT ASCII ('' a ') Value SELECT ASCII (' 'A') Value from Dual
22. Squiracy from the serial number S: SELECT Char (97) Value SELECT CHR (97) Value from DUAL
23. Connect S: SELECT '' '' 33 '' 'Value Select Concat (' '11', '' 22 ') || 33 Value from DUAL
23. Subtrive position - Return 3 S: SELECT Charindex ('' s ',' 'sdsq' ', 2) Value 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 Oracle did not find, but Instr can pass Saitude and ask 刂 刂 鱿? BR> SELECT INSTR ('' sdsfasdqe '', '' sd ', 1, 2) Value from Dual Returns 6
24. Subside Springs S: Select Substring ('' Abcd '', 2, 2) Value SELECT SUBSTR ('' Abcd ', 2, 2) Value from Dual
25. Subtrial replacement returns Aijklmnef S: Select Stuff ('' Abcdef ', 2, 3,' 'Ijklmn') Value SELECT Replace ('' Abcdef ',' 'BCD', 'Ijklmn' ') Value from Dual
26. Subrings all replace S: did not find SELECT TRANSLATE ('Fasdbfasegas'', 'FA', '' I '') Value from Dual
27. Length S: Len, Datancength Length
28. Size transformation Lower, Upper
29. The first letter of the word S: didn't find 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 SELECT LPAD ('' Abcd ', 14) Value from DUAL
31. Right-tubular space (the first parameter of RPAD is spaced with space functions) s: select '' Abcd '' space (10) Value SELECT RPAD ('' Abcd ', 14) Value from DUAL32. Delete Space S: LTRIM, RTRIM LTRIM, RTRIM, TRIM
33. Repeat strings S: SELECT Replicate ('' Abcd '', 2) Value did not find
34. Pronunciation Similarity comparison (the same, the same, the same, the same pronunciation) S: SELECT SOTEX ('' Smith '), Soundex (' SMYTHE ') Select Soundex (' SMITH '), SOUNDEX '' Smythe '') from Dual Sql Server with select Difference ('' smithers '' "compared to SoundEx's difference between 0-4, 4 is the same sound, 1 highest
-------------- Date function
35. System time s: select getdate () Value 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 SELECT TRUNC (SYSDATE) VALUE from Dual Select to_Char (Sysdate, 'YYYY-mm-DD') Value from DUAL
38. Summary S: Select Convert (Char (8), GetDate (), 108) Value Select to_Char (Sysdate, '' HH24: mm: ss') Value from DUAL
39. Other parts of date time S: datepart and dateename functions (first parameter decision) 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 Select last_day (sysdate) Value from DUAL
41. One day (such as Sunday) S: I don't know Select next_day (sysdate, 7) vaule from dual;
42. Character string turn time S: You can turn directly or select Cast ('' 2004-09-08'AS DateTime) Value 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 (such as second) s: select datediff (ss, getdate (), getdate () 12.3) Value directly with two Date reduction (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 ()) Value Select Sysdate 8/60/24 Vaule from DUAL;
45. Ask for a different time S: I don't know Select new_time (sysdate, 'ydt', '' gmt '') vaule from dual;
---- Time zone parameters, Beijing 8th District should be YDT ------- AST ADT Atlantic Standard Time BST BDT White Water Sea Standard Time CST CDT Central Standard Time EST EDT Eastern Standard Time GMT GMT Time HST HDT HST HDT Alaska - Hawaiian standard time MST MDT Mountain standard time NST Newfoundland standard time PST PDT YUKON standard time yst YDT YUKON standard time ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -------------
