oracle/mysql/sqlserver function Differences

 MySQL date and time format conversion Linux SCP Usage Detailedoracle/mysql/sqlserver Function Differences

2011-07-01 12:34:36|  Category: MySQL technology | Tags:mysql sqlsever oracle functions | Report | Font size Big medium small subscription

SQL code

1. Type conversion

--oracle Select To_number (' 123 ') from dual; --123;  Select To_char from dual; --33; Select To_date (' 2004-11-27 ', ' yyyy/mm/dd ') from dual;--2004-11-27

--mysql Select cast (' 123 ' as signed integer);  --123 Select cast (as char (2));  --33; Select To_days (' 2000-01-01 '); --730485

--sqlserver Select cast (' 123 ' as Decimal (30,2));  --123.00 Select cast (as char (2)); --33; select CONVERT (varchar, GETDATE (), 120)

2. Rounding function differences

--oracle Select Round (12.86*10)/10 from dual; --12.9

--mysql Select Format (12.89,1); --12.9

--sqlserver Select round (12.89,1); --12.9

3. Date-time functions

--oracle select Sysdate from dual; --Date and time

--mysql select Sysdate ();   --Date Time select Current_date (); --Date

--sqlserver select GETDATE (); --Date Time Select DateDiff (Day, ' 2010-01-01 ', cast (GETDATE () as varchar (10)));--Date difference days

4.Decode function

--oracle Select Decode (sign (in), 1,1,0,0,-1) from Dual;--1

--mysql/sqlserver Select Case [=1 then 1] sign (a) =0 then 0 else-1 end;--1

5. Empty function

--oracle Select NVL (1,0) from dual; --1

--mysql Select Ifnull (1,0); --1

--sqlserver Select IsNull (1,0); --1

6. String Join function

--oracle select ' 1 ' | | '  2 ' from dual;   --12 Select Concat (' 1 ', ' 2 '); --12

--mysql Select Concat (' 1 ', ' 2 '); --12

--sqlserver Select ' 1 ' + ' 2 '; --12

7. Record restriction functions

--oracle Select 1 from dual where rownum <= 10;

--mysql Select 1 from dual limit 10;

--sqlserver Select Top 10 1

8. String Intercept function

--oracle Select substr (' 12345 ', 1,3) from dual;

--mysql/sqlserver Select substring (' 12345 ', 1, 3);

8. Convert multiple rows into a consolidated column

--oracle Select wm_concat (column name) from dual; --Multi-line records are converted into one column for use, split

--mysql/sqlserver Select group_concat (column name);

Comparison of common functions between SQL Server and Oracle

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

2. Rounding (Large) S:select ceiling ( -1.001) value O:select ceil ( -1.001) value from dual

3. Rounding (small) s:select floor ( -1.001) value o:select floor ( -1.001) value from dual

4. Rounding (intercept) 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 for the bottom power s:select exp (1) Value 2.7182818284590451 o:select exp (1) value from dual 2.71828182

7. Take e as the base logarithm s:select log (2.7182818284590451) value 1 o:select ln (2.7182818284590451) value from dual; 1

8. Take 10 for the base logarithm S:select log10 (Ten) value 1 o:select log (10,10) value from dual; 1

9. Take Square S:select Square (4) Value O:select Power (4,2) value from dual 16

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

11. For any number of power s:select power (3,4) value o:select power (3,4) value from dual 81

12. Take the 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 signs ( -8) value from Dual-1----------mathematical function

14. Pi S:select () value 3.1415926535897931 O: Do not know

The 15.sin,cos,tan parameters are in radians, for example: Select Sin (PI ()/2) value gets 1 (SQL Server)

16.asin,acos,atan,atan2 return radians

17. Radian Angle Interchange (sqlserver,oracle not known) DEGREES: radian-〉 angle RADIANS: Angle-〉 radians

Comparison between---------values

18. Set Maximum 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. Find the Set minimum 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--------values Comparison

21. Find the character ordinal s:select ASCII (' a ') value o:select ASCII (' a ') value from dual

22. Character S:select char (value o:select CHR) value from dual

23. Connect S:select ' + ' + ' + ' value o:select CONCAT (' 11 ', ' 22 ') | | 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. The position of the fuzzy substring-return 2, the parameter minus the middle% is returned 7 s:select patindex ('%d%q% ', ' sdsfasdqe ') value o:oracle not found, but InStr can through the fourth haze ask acutely a squid ask?br> se Lect INSTR (' sdsfasdqe ', ' SD ', $) value from dual return 6

24. Substring s:select substring (' ABCD ', 2,2) value
O:select substr (' ABCD ', 2,2) value from dual

25. Substring instead of return AIJKLMNEF s:select STUFF (' abcdef ', 2, 3, ' ijklmn ') value o:select replace (' abcdef ', ' BCD ', ' ijklmn ') value F Rom Dual

26. Substring replacement S: Not found O:select Translate (' Fasdbfasegas ', ' fa ', ' I ') value from dual

27. Length S:len,datalength O:length

28. Case Conversion Lower,upper

29. Initial capital letter S: Not found O:select initcap (' ABCD Dsaf df ') value from dual

30. Left fill space (Lpad the first parameter is a space function) S:select space (Ten) + ' ABCD ' value o:select lpad (' ABCD ') value from dual

31. Right fill space (Rpad the first parameter is a space function) S:select ' ABCD ' +space (x) value O:select rpad (' ABCD ') value from dual

32. Remove the space S:ltrim,rtrim O:ltrim,rtrim,trim

33. Repeating string S:select REPLICATE (' ABCD ', 2) value
O: Not Found

34. Comparison of pronunciation similarity (the two words return the same value, pronounced the same) s:select SOUNDEX (' Smith '), SOUNDEX (' Smythe ') O:select SOUNDEX (' Smith '), SOUNDEX (' Smyth E ') from dual SQL Server, compare Soundex with Select difference (' Smithers ', ' smythers ') to return 0-4,4 for the same sounds, 1 maximum

--------------Date function

35. System Time S:select getdate () value O:select sysdate value from dual

36. Direct and integer addition and subtraction

37. Date S:select Convert (char (ten), GETDATE (), Value O:select trunc (sysdate) value from dual select To_char (Sysdate, ' Yyyy-mm-dd ') value from dual

38. Find Time S:select convert (char (8), GETDATE (), 108) value O:select to_char (sysdate, ' hh24:mm:ss ') value from dual

39. Take the other parts of the DateTime S:datepart and DATENAME functions (the first parameter determines) the second parameter of the O:to_char function determines the parameter---------------------------------the following table needs to be supplemented by year   YY, yyyy quarter QQ, Q (quarter) month mm, m (M o invalid) dayofyear dy, y (o table week) Day DD, D (invalid in d o) week wk, ww (wk o Invalid)   Weekday DW (o unclear) Hour hh,hh12,hh24 (hh12,hh24 s invalid) minute MI, n (n o Invalid) second SS, s (S o invalid) millisecond ms (o invalid) ----------------------------------------------

40. Last day of the month S: Do not know O:select last_day (sysdate) value from dual

41. One day of the week (e.g. Sunday) S: Do not know O:select next_day (sysdate,7) vaule from DUAL;

42. String Turn time S: can go directly or SELECT cast (' 2004-09-08 ' as datetime) value O:select to_date (' 2004-01-05 22:09:38 ', ' Yyyy-mm-dd hh24- Mi-ss ') Vaule from DUAL;

43. Find the difference between a part of two dates (e.g. seconds) S:select DateDiff (Ss,getdate (), GETDATE () +12.3) value O: Subtract directly from two dates (e.g. d1-d2=12.3) Select (D1-D2) *24* 60*60 Vaule from DUAL;

44. The date of novelty based on the difference (e.g. minutes) S:select DateAdd (Mi,8,getdate ()) value O:select sysdate+8/60/24 vaule from DUAL;

45. Ask for different time zone times S: Do not know O:select new_time (sysdate, ' ydt ', ' GMT ') vaule from DUAL;

-----Time zone parameter, Beijing in the East 8 district should be Ydt-------AST ADT Atlantic Standard Time BST BDT Bering Sea Standard Time CST CDT Central Standard Time EST EDT Eastern Standard time GMT Greenwich Mean Time HST H DT Alaska-Hawaii Standard Time MST MDT Mountain Standard Time NST Newfoundland standard Time PST PDT Pacific Standard Time YST YDT Yukon Standard Time

oracle/mysql/sqlserver function Differences