Mathematical function 1. absolute value s: select abs (-1) Value o: 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. Take (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.23460 o: SELECT ROUND (1.23456, 4) Value from Dual 1.2346 6.e for the bottom power S: SELECT Exp (1) Value 2.7182818284590451 o: SELECT Exp (1) Value from Dual 2.71828182 7. Take E forctory log: SELECT log (2.7182818284590451) Value 1 o: select ln (2.7182818284590451) Value from dual; 1 8. Take 10 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. Search for the power S: SELECT POWER (3, 4) Value 81 o: SELECT POWER (3 , 4) Value from Dual 81 12. Take the randancy S: select rand () value o: select sys.dbms_rando M.Value (0,1) value from dual; 13. Take symbol S: SELECT SIGN (-8) value -1 o: select Sign (-8) Value from Dual -1 14. Value Rate S: SELECT PI () VALUE 3.1415926535897931 o: Do not 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 doesn't know) DegRees: radia -> angle RADIANS:
Angle -> Arc Number Comparison 18. Seeking Collection Max S: SELECT MAX (Value) Value from (SELECT 1 VALUE UNION SELECT-VALUE UNION SELECT 4 VALUE UNION SELECT 3 VALUE) a o: SELECT GREATEST (1, -2) , 4, 3) Value from Dual 19. Seeking Collection Minimum S: Select Min (Value) Value from (SELECT 1 Value Union Select -2 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) S: SELECT F1, ISNULL (F2, 10) Value from TBL o: SELECT F1, NVL (F2, 10) Value From TBL 21. Ask character number S: SELECT ASCII ('a') Value O: Select ASCII ('a') Value from Dual 22. From the serial number character S: SELECT CHAR (97) Value o: SELECT CHR (97) Value from Dual 23. Connection S: SELECT '11' '22' '33' Value O: SELECT Concat ('11 ',' 22 ') || 33 Value from Dual 23. Subsidence - 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 intermediate% Returns 7 S: SELECT PATINDEX ('% D% Q%', 'SDSFASDQE') Value O: Oracle did not find, but INSTR can control the number of times Select INSTR ('SDSfasDQE', 'SD', 1, "by controlling the fourth parameter. 2) Value from DUAL Returns 6 24. Subsidence S: S Elect Substring ('Abcd', 2, 2) Value O: Select Substr ('Abcd', 2, 2) Value from Dual 25. Subtrite Replace Returns Aijklmnef S: Select Stuff ('Abcdef', 2, 3, 'Ijklmn ') Value o: SELECT Replace (' AbcDef ',' BCD ') Value from Dual 26. Subrings All S: No O: Select Translate (' Fasdbfasegas', 'Fa', 'I') Value from Dual 27. Length S: Len, Datalength O: Length 28. Calculation Translation Lower, Upper 29. Word Senior Letter S: No O: SELECT ITCAP ('Abcd DSAF DF') Value from Dual 30. Left Space (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 (RPAD's first parameter 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 string S: SELECT Replicate ('Abcd', 2) Value O: No 34. pronunciation Similarity comparison (both words return value, pronunciation) S: select soundex ('smith'), Soundex ('SMYTHE') o: select Soundex ('Smith'), Soundex ('Smythe') from Dual Sql Server Compare SoundEx's difference between SOUNDEX in Select Difference ('Smithrs', 'Smythers'), 1 Supreme, 1 Maximum Date Function 35. System Time S: Select Getdate () Value O: Select Sysdate Value from Dual 36. Directly added to the integer before and after 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 S: Select Convert (Char (8), Getdate (), 108) Value o: Select to_Char (Sysdate,' HH24: MM: SS ') Value from Dual 39. Take Date Time Other parts s: datepart and datename functions (first parameter decision) o: to_Char function second parameter determine parameters ------------------------ --------- The following table Need to add Year YY, YYYY quarter QQ, Q (quarter) MONTH MM, M (M O 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 is invalid) Minute Mi, N (N o Invalid) Second SS, S (Invalid) 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: You can turn directly 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 a part of a certain part of the two days (such as seconds)
