---------- Mathematical function 1. absolute value s: select abs (-1) Valueo: SELECT ABS (-1) Value from Dual 2. Taken (large) S: SELECT CEILING (-1.001) Value O: select ceil (-1.001) Value from Dual 3. Taken (small) S: SELECT FLOOR (-1.001) Value o: select floor (-1.001) Value from Dual 4. Adjacent (intercept) S: SELECT CAST -1.002 as int) Value o: select trunc (-1.002) Value from Dual 5. Since Since S: SELECT ROUND (1.23456, 4) Value 1.23460o: SELECT ROUND (1.23456, 4) Value from Dual 1.2346 6.e power S: select Exp (1) value 2.7182818284590451 O: select Exp (1) value from dual 2.71828182 7. e taken as logarithm S: select log (2.7182818284590451) value 1O: select ln (2.7182818284590451) value from dual; 1 8. Take 10 for the end of the logarithm s: 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. Search for the power S: SELECT POWER (3, 4) Value 81o: SELECT Power (3,4) Value from Dual 81 12. Take the number S: select rand () value o: select sys.dbms_random.value (0, 1) Value from dual; 13. Take symbol S: SELECT SIGN (-8) value -1o: select Sign (-8) Value from dual -1 --------- Triangle function related 14. Wen Zhou S: SELECT Pi () Value 3.1415926535897931O: I don't know if II, COS, TAN parameters are in an arc, for example: SELECT SIN (Pi () / 2) Value gets 1 (SQL Server) 16.Ansin, ACOS, Atan, Atan2 Return Arc 17. Arc angle interchange (SQL Server, Oracle does not know) DegRees: radians -> angle RADIANS:
Angle -> Arc --------- Numerical Comparison 18. Seeking Collection Max 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 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 (NULL in F2) 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. Sequence Number 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. Subsidence - 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 be controlled by the fourth parameter Number Select INSTR ('SDSfasDQE', 'SD', 1, 2) Value from Dual Returns 6 24. Subside Springs S: Select Substring ('Abcd', 2, 2) Value O: SEL ECT SUBSTR ('Abcd', 2, 2) Value from Dual 25. Subtrite Replacement Returns Aijklmnefs: Select Stuff ('Abcdef', 2, 3, 'Ijklmn') Valueo: SELECT Replace ('Abcdef', 'BCD', 'ijklmn') Value from Dual 26. Subride all replace S: did not find o: select translate ('fasdbfasegas', 'fa', 'I') Value from Dual 27. Length S: Len, Datalengtho: Length 28. Size Write conversion LOWER, UPPER 29. Word first letters uppercase S: No o: select initcap ('Abcd DSAF DF') Value from Dual 30. Left Space (The first parameter of the LPAD is space with the space function) S: Select Space (10) 'Abcd' Valueo: SELECT LPAD ('Abcd', 14) Value from Dual 31. Right Complete Space (
The first parameter of RPAD is spaced with space functions) S: select 'abcd' space (10) value: select rpad ('abcd', 14) Value from Dual 32. Delete space S: LTRIM, RTRIMO: LTRIM, RTRIM, TRIM 33. Repeat strings S: SELECT Replicate ('Abcd', 2) Value O: No 34. Dental Similarity Compare (the same, the same, the same pronunciation) S: SELECT SOUNDEX ('Smith' ), SOUNDEX ('SMYTHE') O: Select Soundex ('Smith'), Soundex ('Smythe') from Dual Sql Server ('Smithrs', 'Smythers') Compare SoundEx's difference Return 0-4, 4 For the same sound, 1 highest -------------- Date function 35. System time s: select getdate () value: select sysdate value from Dual 36. Directly adding to the integer before and after a few days 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 DateName functions (first Parameter decision) o: to_Char function second parameter determines parameters ------------------------------- Supplementary YYY, YYYY Quarter QQ, Q (Quarter) MONTH MM, M (MO Invalid) 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 ineffective) Minute Mi, N (ineffective) Second SS, S (无 无效) MilliseCond MS (O invalid) - -------------------------------------------- 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: Can be transferred 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 differences (such as seconds) s: select datediff (ss, getdate (), getdate () 12.3) VALUEO: Direct Download (SS, GETDATE ()
