Comparison of common functions of SQL Server and Oracle
1. absolute value S: Select ABS (-1) value o: Select ABS (-1) value from dual2. INTEGER (large) S: Select ceiling (-1.001) value O: select Ceil (-1.001) value from dual3. INTEGER (small) S: Select floor (-1.001) value o: Select floor (-1.001) value from dual4. 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.22.166.e is the base power S: S Elect exp (1) Value 2.7182818284590451 O: Select exp (1) value from dual 2. 718281827. base E logarithm S: Select log (2.7182818284590451) value 1 O: Select Ln (2.7182818284590451) value from dual; 18. take 10 as the base logarithm S: Select log10 (10) value 1 O: Select log (10, 10) value from dual; 19. take the square S: Select square (4) value 16 O: Select power (1610) value from dual. take the square root s: Select SQRT (4) value 2 O: Select SQRT (4) value from dual 211. request Power s with a base weight: Select power (3, 4) value 81 O: Select power (3, 4) value from dual 8112. random Number S: Select rand () value o: select Sys. dbms_random.value (0, 1) value from dual; 13. take the symbol S: Select sign (-8) Value-1 O: Select sign (-8) value from dual-1 ---------- mathematical function 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 return radian 17. degrees Interchange (SQL Server, Oracle does not know) degrees: radians-> Angle radians: Angle-> radians --------- comparison of values 18. returns the maximum value of a set. Values: 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 dual19. evaluate the minimum value of the Set S: select Min (value) value from (select 1 value Union select-2 value Union select 4 value unionselect 3 value) a o: select least (1,-2, 4, 3) value from dual20. how to deal with nu Ll value (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 character No. S: Select ASCII ('A') value o: Select ASCII ('A') value from dual22. evaluate character S: Select char (97) value O: select CHR (97) value from dual23. connection s: Select '11' + '22' + '33' value o: Select Concat ('11', '22 ') | 33 value from dual23. substring position -- returns 3 S: Select charindex ('s', 'sdsq', 2) value o: Select instr ('sdsq ', S', 2) value from dual23. position of the fuzzy substring -- 2 is returned. If % is removed from the middle, 7 S is returned: Select patindex (' % d % Q % ', 'sdsfasdqe ') value o: Oracle did not find it, but instr can use the fourth haze to ask why? BR> select instr ('sdsfasdqe ', 'sd', 1, 2) value from dual returns 624. substring S: Select substring ('abcd',) value o: Select substr ('abcd',) value from dual25. substring instead of returning aijklmnef s: select stuff ('abcdef', 2, 3, 'ijklmn ') value o: Select Replace ('abcdef', 'bcd', 'ijklmn ') value From dual26. substring replace all S: No found o: Select translate ('fasdbfasegas', 'fa ',' I ') value from dual27. length S: Len, datalength O: length28. case-sensitive conversion lower, upper29. the first letter of the word is uppercase. S: no o found: Select initcap ('abcd dsaf df ') value From dual30. left fill space (the first parameter of lpad is space, which is the same as the space function) S: Select space (10) + 'abcd' value o: Select lpad ('abcd ', 14) value from dual31. right fill space (the first parameter of rpad is space, which is the same as the space function) S: Select 'abcd' + space (10) value O: select rpad ('abcd', 14) value from dual32. Delete space s: ltrim, rtrim O: ltrim, rtrim, trim33. 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 ('smythe') O: Select soundex ('Smith '), soundex ('smy') from dual sqlserver uses select difference ('smithers', 'smythers ') to compare the soundex difference and returns 0-4 as the homophone, 1 as the highest -------------- date function 35. system time s: Select getdate () value o: Select sysdate value from dual36. add or subtract 37 directly from the integer on the first and second days. evaluate 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 dual38. evaluate the time s: Select convert (char (8), getdate (), 108) value o: Select to_char (sysdate, 'hh24: MM: ss') value from dual39. take other part 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 ----------------------------- 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 invalid) Week wk, ww (WK o invalid) weekday DW (O unknown) hour HH, hh12, hh24 (hh12, hh24 s invalid) minute Mi, n (n o is invalid) second SS, S (s o is invalid) millisecond MS (O is invalid) ------------------------------------------ 40. last day of the month s: Unknown O: Select last_day (sysdate) value from dual 41. one 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. evaluate the difference (for example, seconds) between two dates. S: Select datediff (SS, getdate (), getdate () + 12.3) value O: subtract directly with two dates (e.g. d1-d2 = 12.3) Select (d1-d2) * 24*60*60 vaule from dual; 44. calculate the new date (such as minute) S: Select dateadd (MI, 8, getdate () value o: Select sysdate + 8/60/24 vaule from dual; 45. evaluate the time s in different time zones: Unknown O: Select new_time (sysdate, 'ydt ', 'gmt') vaule from dual; ----- Time Zone parameter, beijing in the east 8 zone should be ydt ------- ast ADT Atlantic Standard Time bst bdt white ocean Standard Time cst cdt Central Standard Time est EDT Eastern Standard Time GMT Greenwich Mean Time hst hdt Alaska-Hawaii Standard Time mst mdt Mountain Standard Time NST new Finland Standard Time pst pdt Pacific Standard Time yst ydt Yukon Standard Time
Character functions supported by Oracle and Their Microsoft SQL Server equivalent functions:
Function Oracle Microsoft SQL Server converts character to ASCII string connection Concat (expression + expression) convert ASCII to character CHR char returns the start character (left) in the string) instr charindex converts characters to lower-case lower to upper-case upper fill string left lpad N/A clear start blank ltrim clear trailing blank rtrim start mode in the string (pattern) the speech of the instr patindex repeat character string rpad replicate multiple times indicates that the character string rpad space of soundex repeat space is converted from numeric data to character data to_char STR substring substr substring replacement character replace stuff the first letter of each word is capital initcap N/A translation string translate N/A String Length length datelength or Len list the largest string greatest N/A list the smallest string least N/A if it is null convert the string nvl isnull date function Oracle Microsoft SQL Server date addition (date column +/-value) oradd_months dateadd date difference (date column +/-value) ormonths_between datediff current date and time sysdate getdate () the last day of a month last_day N/A Time Zone converts new_time N/the first Sunday next_day after the date N/A represents the date string to_char datename represents the date integer to_number (to_char )) datepart date rounding round convert date truncation trunc convert String Conversion to date to_date convert if it is null convert date nvl isnull Conversion Function function Oracle Microsoft SQL server number conversion to character to_char convert character conversion to number to_number convert date conversion to to_char convert character conversion to date to_date convert hexadecimal conversion to binary hex_to_raw convert hexadecimal conversion to hexadecimal raw_to_hex convert other row-level functions Oracle microsoft SQL Server Returns the first non-empty expression decode coalesce current Sequence Value currval N/A next Sequence Value nextval N/A if exp1 = exp2, return NULL decode nullif user login account ID number uid suser_id User Login User suser_name user database ID number uid user_id User Database Name User user_name current user CURRENT_USER user environment (audit trail) userenv N/A in the connect by clause level N/A aggregate function Oracle Microsoft SQL Server average AVG count maximum Max minimum Min standard deviation stddev STDev or stdevp summation sum variance var or varp oracle also has a useful function extract, extract and return a specific time field in the date or time interval expression: extract (year from date)
