Comparison and summary of SQL Server and Oracle common function instances

I. mathematical functions

1. Absolute Value
Copy codeThe Code is as follows: S: select abs (-1) value
O: select abs (-1) value from dual

2. INTEGER (large)
Copy codeThe Code is as follows: S: select ceiling (-1.001) value
O: select ceil (-1.001) value from dual

3. Round (small)
Copy codeThe Code is as follows: S: select floor (-1.001) value
O: select floor (-1.001) value from dual

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

5. Rounding
Copy codeCode: 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
Copy codeThe Code is as follows: S: select Exp (1) value 2.7182818284590451.
O: select Exp (1) value from dual 2.71828182

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

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

9. Square
Copy codeCode: S: select SQUARE (4) value 16
O: select power (4, 2) value from dual 16

10. Take the square root
Copy codeCode: S: select SQRT (4) value 2
O: select SQRT (4) value from dual 2

11. Evaluate the base power of any number
Copy codeThe Code is as follows: S: select power (3, 4) value 81
O: select power (3, 4) value from dual 81

12. Random Number acquisition
Copy codeThe Code is as follows: S: select rand () value
O: select sys. dbms_random.value (0, 1) value from dual;

13. Get the symbol
Copy codeThe Code is as follows: S: select sign (-8) value-1
O: select sign (-8) value from dual-1

14. Circumference Rate
Copy codeThe Code is as follows: S: select pi () value 3.1415926535897931
O: not found

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 (SQLServer, Oracle not found)
DEGREES: Radian-> Angle
RADIANS: Angle-> radian

2. Comparison between values

18. Calculate the maximum value of the Set
Copy codeThe Code is as follows: 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
Copy codeThe Code is as follows: 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)
Copy codeCode: S: select F1, IsNull (F2, 10) value from Tbl
O: select F1, nvl (F2, 10) value from Tbl

21. Search for the character serial number
Copy codeThe Code is as follows: S: select ascii (a) value
O: select ascii (a) value from dual

22. calculate characters from the serial number
Copy codeCode: S: select char (97) value
O: select chr (97) value from dual

23. Connection
Copy codeCode: S: select 11 + 22 + 33 value
O: select CONCAT (11,22) | 33 value from dual

24. substring position -- return 3
Copy codeCode: S: select CHARINDEX (s, sdsq, 2) value
O: select INSTR (sdsq, s, 2) value from dual

25. Position of the fuzzy substring -- 2 is returned. If % is removed from the parameter, 7 is returned.
Copy codeCode: 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

26. substring search
Copy codeCode: S: select substring (abcd, 2, 2) value
O: select substr (abcd, 2, 2) value from dual

27. substring instead of aijklmnef
Copy codeThe Code is as follows: S: select stuff (abcdef, 2, 3, ijklmn) value
O: SELECT Replace (abcdef, bcd, ijklmn) value from dual

28. replace all substrings
Copy codeCode: S: not found
O: select Translate (fasdbfasegas, fa, I) value from dual

29. Length
Copy codeThe Code is as follows: S: len, datalength
O: length

30. case-insensitive lower, upper

31. uppercase letters
Copy codeCode: S: not found
O: select INITCAP (abcd dsaf df) value from dual

32. Left fill space (the first parameter of LPAD is space, which is the same as the space function)
Copy codeThe Code is as follows: S: select space (10) + abcd value
O: select LPAD (abcd, 14) value from dual

33. Right fill space (the first parameter of RPAD is space, which is the same as the space function)
Copy codeCode: S: select abcd + space (10) value
O: select RPAD (abcd, 14) value from dual

34. Delete Spaces
Copy codeThe Code is as follows: S: ltrim, rtrim
O: ltrim, rtrim, trim

35. Duplicate string
Copy codeThe Code is as follows: S: select REPLICATE (abcd, 2) value
O: No

36. Comparison of pronunciation similarity (the two words return the same value and have the same pronunciation)
Copy codeThe Code is as follows: S: select soundex (Smith), SOUNDEX (Smythe)
O: select soundex (Smith), SOUNDEX (Smythe) from dual
Comparison of soundex with select difference (Smithers, Smythers) in SQLServer
Returns 0-4, 4 for homophone, 1 for maximum

Iii. Date Functions

37. system time
Copy codeThe Code is as follows: S: select getdate () value
O: select sysdate value from dual

38. days before and after
Directly add or subtract from an integer

39. Calculate the date
Copy codeCode: 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

40. Time
Copy codeCode: S: select convert (char (8), getdate (), 108) value
O: select to_char (sysdate, hh24: mm: ss) value from dual