Comparison between SQL Server and common oracle Functions

1. absolute value S: selectabs (-1) valueO: selectabs (-1) valuefromdual2. INTEGER (large) S: selectceiling (-1.001) valueO: selectceil (-1.001) valuefromdual3. INTEGER (small) S: selectfloor (-1.001) valueO: selectfloor (-1.001) valuefromdual4. INTEGER (truncation

1. absolute value S: select abs (-1) value O: select abs (-1) value from dual 2. round (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

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: oracle didn't find it, but instr can throw a glance at the issue via the fourth haze? BR> select INSTR ('sdsfasdqe ', 'sd', 1, 2) value from dual returns 6

24. Seek substrings
S: select substring ('abcd', 2, 2) value
O: select substr ('abc', 2, 2) value from dual

25. Return aijklmnef as a substring
S: select stuff ('abcdef', 2, 3, 'ijklmn ') value
O: SELECT Replace ('abcdef', 'bcd', 'ijklmn ') value from dual

26. replace all substrings
S: No
O: select Translate ('fasdbfasegas', 'fa ', 'my') value from dual

27. Length
S: len, datalength
O: length

28. case-insensitive lower, upper

29. uppercase letters
S: No
O: select INITCAP ('abcd dsaf df') value from dual

30. Left fill space (the first parameter of LPAD is space, which is the same as the space function)
S: select space (10) + 'abc' value
O: select LPAD ('abc', 14) value from dual

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

32. Delete Spaces
S: ltrim, rtrim
O: ltrim, rtrim, trim

33. Duplicate string
S: select REPLICATE ('abcd', 2) value
O: No

34. Comparison of pronunciation similarity (the two words return the same value and have the same pronunciation)
S: select soundex ('Smith '), SOUNDEX ('smy ')
O: select soundex ('Smith '), SOUNDEX ('smy') from dual
Comparison of soundex with select difference ('smithers', 'smythers ') in SQLServer
Returns 0-4, website space, 4 for homophone, Vm, 1 highest

-------------- Date functions

35. system time
S: select getdate () value
O: select sysdate value from dual

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

37. Calculate the date
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. Time
S: select convert (char (8), getdate (), 108) value
O: select to_char (sysdate, 'hh24: mm: ss') value from dual

39. Take other parts of date and time
S: DATEPART and DATENAME functions (determined by the first parameter)
O: The second parameter of the to_char function is determined.
Parameter --------------------------------- The following table needs to be supplemented.
Year yy, yyyy
Quarter qq, q (quarter)
Month mm, m (m O is invalid)
Dayofyear dy, y (O table week)
Day dd, d (d O is invalid)
Week wk, ww (wk O is invalid)
Weekday dw (unclear O)
Hour hh, hh12, hh24 (hh12, hh24 S is invalid)
Minute mi, n (n O is invalid)
Second ss, s (s O is invalid)
Millisecond MS (invalid O)
----------------------------------------------

40. Last day of the month
S: Unknown
O: select LAST_DAY (sysdate) value from dual

41. A day of the week (such as Sunday)
S: Unknown
O: SELECT Next_day (sysdate, 7) vaule from dual;

42. String Conversion time
S: Can be directly converted to or select cast ('2017-09-08 'as datetime) value
O: SELECT To_date ('2017-01-05 22:09:38 ', 'yyyy-mm-dd hh24-mi-ss') vaule from dual;

43. Calculate the difference of a part of the two dates (for example, seconds)
S: select datediff (ss, getdate (), getdate () + 12.3) value
O: Subtract directly with two dates (for example, d1-d2 = 12.3)
SELECT (d1-d2) * 24*60*60 vaule from dual;

44. Calculate a new date (for example, minute) based on the difference value)
S: select dateadd (mi, 8, getdate () value
O: SELECT sysdate + 8/60/24 vaule from dual;

45. Time in different time zones
S: Unknown
O: SELECT New_time (sysdate, 'ydt ', 'gmt') vaule from dual;

----- Time Zone parameter. Beijing must be in the east 8 zone of Ydt -------
Ast adt Standard Atlantic time
Bst bdt Standard Time
Standard Time for central CST CDT
Est edt standard Eastern Time
GMT Standard Time
Hst hdt Alaska-Hawaii Standard Time
Standard Time for mst mdt Mountainous Areas
NST standard time for new Finland
Pst pdt Pacific Standard Time
Yst ydt yukon standard time, US space