Comparison of Oracle and ms SQL Server Functions

--------- Mathematical function 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. INTEGER (truncated)
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 () value from dual; 1 9. Square
S: Select square (4) value 16
O: Select power () 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
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: Do not know 15.sin, cos, Tan parameters are measured in radians
For example: Select sin (PI ()/2) value to get 1 (sqlserver) 16. asin, ACOs, atan, atan2 returns radian 17. radian angle Interchange (sqlserver, Oracle does not know)
Degrees: Radian-> Angle
Radians: Angle-> radians --------- comparison of values 18. returns the maximum value of a set.
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. Evaluate the 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) a o: Select least (1,-2, 4, 3) value from dual 20. How to Handle null values (null in F2 is replaced by 10)
S: Select F1, isnull (F2, 10) value from TBL
O: Select F1, nvl (F2, 10) value from TBL -------- comparison between values 21. Evaluate the Character Sequence Number
S: Select ASCII ('A') Value
O: Select ASCII ('A') value from dual 22. Evaluate the character from the sequence number
S: Select char (97) Value
O: Select CHR (97) value from dual 23. Join
S: Select '11' + '22' + '33' Value
O: Select Concat ('11', '22') | 33 value from dual 23. substring position -- 3 is returned
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. substring
S: Select substring ('abcd', 2, 2) Value
O: Select substr ('abcd',) value from dual 25. The substring is returned instead of aijklmnef.
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-sensitive conversion 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 ('abcd', 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 for the same tone, 1 for the maximum -------------- date function 35. system time
S: Select getdate () Value
O: Select sysdate value from dual 36. days before and after
Add or subtract directly 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. Evaluate the 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 the date and time
S: datepart and datename functions (determined by the first parameter)
O: The second parameter of the to_char function determines the 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 ('1970-01-05 22:09:38 ', 'yyyy-mm-dd hh24-mi-ss') vaule from dual; 43. Evaluate the difference between a part of two dates (such as 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. New date (for example, minute) based on Difference)
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 ydt ------- in the east 8 zone -------
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