oracle/mysql/sqlserver function Differences

Source: Internet
Author: User
Tags abs getdate rtrim sin square root

 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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.