Oracle and SQL Server Analysis and mining functions

The functions mentioned in this article are not all functions of Oracle and SQL Server. Especially the analysis and mining functions are not fully covered. Please give priority to solving the problem in practice.

1. Absolute Value
S: select abs (-1) value
O: select abs (-1) value from dual

2. INTEGER (large)
S: select ceiling (-1.001) value
O: select ceil (-1.001) value from dual

3. Round (small)
S: select floor (-1.001) value
O: select floor (-1.001) value from dual

4. Round (truncation)
S: select cast (-1.002 as int) value
O: select trunc (-1.002) value from dual

5. Rounding
S: select round (1.23456, 4) value 1.23460
O: select round (1.23456, 4) value from dual 1.2346

6. e is the base power
S: select Exp (1) value 2.7182818284590451
O: select Exp (1) value from dual 2.71828182

7. Take the base logarithm of e.
S: select log (1, 2.7182818284590451) value 1
O: select ln (2.7182818284590451) value from dual; 1

8. Use 10 as the base logarithm.
S: select log10 (10) value 1
O: select log (10, 10) value from dual; 1

9. 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. Evaluate the base power of any number
S: select power (3, 4) value 81
O: select power (3, 4) value from dual 81

12. Random Number acquisition
S: select rand () value
O: select sys. dbms_random.value (0, 1) value from dual;

13. Get the symbol
S: select sign (-8) value-1
O: select sign (-8) value from dual-1

---------- Mathematical functions

14. Circumference Rate
S: select pi () value 3.1415926535897931
O: Unknown

15. sin, cos, and tan parameters are in radians.
For example, select sin (PI ()/2) value to get 1 (SQLServer)

16. Asin, Acos, Atan, Atan2 return radians

17. radian angle Interchange (, Oracle unknown)
DEGREES: Radian-> Angle
RADIANS: Angle-> radian

--------- Comparison of Values

18. Calculate the maximum value of the Set
S: select max (value) value from
(Select 1 value
Union
Select-2 value
Union
Select 4 value
Union
Select 3 value)

O: select greatest (1,-2, 4, 3) value from dual

19. Minimum value of the Set
S: select min (value) value from
(Select 1 value
Union
Select-2 value
Union
Select 4 value
Union
Select 3 value)

O: select least (1,-2, 4, 3) value from dual

20. How to Handle null values (replace null with 10 in F2)
S: select F1, IsNull (F2, 10) value from Tbl
O: select F1, nvl (F2, 10) value from Tbl

-------- Comparison of Values

21. Search for the character serial number
S: select ascii ('A') value
O: select ascii ('A') value from dual

22. calculate characters from the serial number
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. substring position -- return 3
S: select CHARINDEX ('s ', 'sdsq', 2) value
O: select INSTR ('sdsq', 's', 2) value from dual

23. Position of the fuzzy substring -- 2 is returned. If % is removed from the parameter, 7 is returned.
S: select patindex ('% d % q %', 'sdsfasdqe ') value
O: no oracle found, but instr can use the fourth parameter to control the number of occurrences.
Select INSTR ('sdsfasdqe ', 'sd', 1, 2) value from dual returns 6

　　

• 2 pages in total:
• Previous Page
• 1
• 2
• Next Page