Differences between oracle and SQL (common functions) (3) _ MySQL-mysql tutorial

Differences between oracle and SQL (common functions) (3) ORACLE functions

BitsCN.com

Rn S: select patindex ('% d % q %', 'sdsfasdqe ') value O: oracle not found, however, instr can use the fourth parameter to control the number of occurrences. select INSTR ('sdsfasdqe ', 'SD', 1, 2) value from dual returns 6

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

25. substring 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: Replace

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 space 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 ('smythe') O: select soundex ('Smith '), SOUNDEX ('smy') from dual SQLServer use select difference ('smithers', 'smythers ') to compare the soundex DIFFERENCE and return 0-4 as the homophone, with 1 as the 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. Evaluate 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 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 invalid) dayofyear dy, y (O table week) day dd, d (d O 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 d

BitsCN.com