Shulou(Shulou.com)05/31 Report--

This article mainly explains "what are the common functions of SQLServer and Oracle". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn what are the common functions of SQLServer and Oracle.

1. Mathematical function

1. absolute value

The copy code is as follows:

S:select abs (- 1) value

O:select abs (- 1) value from dual

two。 Rounding (large)

The copy code is as follows:

S:select ceiling (- 1.001) value

O:select ceil (- 1.001) value from dual

3. Rounding (small)

The copy code is as follows:

S:select floor (- 1.001) value

O:select floor (- 1.001) value from dual

4. Rounding (intercept)

The copy code is as follows:

S:select cast (- 1.002 as int) value

O:select trunc (- 1.002) value from dual

5. Round off

The copy code is as follows:

S:select round (1.23456 and 4) value 1.23460

O:select round (1.23456 and 4) value from dual 1.2346

6. The power of the base e

The copy code is as follows:

S:select Exp (1) value 2.7182818284590451

O:select Exp (1) value from dual 2.71828182

7. Logarithm with e as base

The copy code is as follows:

S:select log (2.7182818284590451) value 1

O:select ln (2.7182818284590451) value from dual; 1

8. Take 10 as the base logarithm

The copy code is as follows:

S:select log10 (10) value 1

O:select log (10, 10) value from dual; 1

9. Take the square

The copy code is as follows:

S:select SQUARE (4) value 16

O:select power (4 and 2) value from dual 16

10. Take the square root

The copy code is as follows:

S:select SQRT (4) value 2

O:select SQRT (4) value from dual 2

11. Find the power of any number as the base

The copy code is as follows:

S:select power (3pr 4) value 81

O:select power (3pr 4) value from dual 81

twelve。 Take the random number

The copy code is as follows:

S:select rand () value

O:select sys.dbms_random.value (0Pol 1) value from dual

13. Take a symbol

The copy code is as follows:

S:select sign (- 8) value-1

O:select sign (- 8) value from dual-1

14. Pi

The copy code is as follows:

S:SELECT PI () value 3.1415926535897931

O: can't find it.

15. The parameters of singhting coscoscurtan are all measured in radians.

For example: select sin (PI () / 2) value gets 1 (SQLServer)

16. AsinrecoverAcosRegy AtanrecoverAtan2 returns Radian

17. Radian Angle Exchange (SQLServer,Oracle not found)

DEGREES: Radian-> Angl

RADIANS: angle-> Radian

2. Comparison between numerical values

18. Find the maximum value of the set

The copy code is as follows:

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) value from dual 2 (4) value from dual

19. Find the minimum value of the set

The copy code is as follows:

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) value from dual 2 (4) value from dual

20. How to handle null values (null in F2 is replaced by 10)

The copy code is as follows:

S:select F1 Magi IsNull (F2m 10) value from Tbl

O:select F1 NVL (F2mem10) value from Tbl

21. Find the character serial number

The copy code is as follows:

S:select ascii (a) value

O:select ascii (a) value from dual

twenty-two。 Find a character from a serial number

The copy code is as follows:

S:select char (97) value

O:select chr (97) value from dual

23. Connect

The copy code is as follows:

S:select 11 "22" 33 value

O:select CONCAT (11 and 22) | | 33 value from dual

24. Substring position-returns 3

The copy code is as follows:

S:select CHARINDEX (SDQ Magi 2) value

O:select INSTR (sdsq,s,2) value from dual

25. The position of the fuzzy substring-returns 2, and returns 7 if the parameter is removed from the middle%.

The copy code is as follows:

S:select patindex (% d% Q% dsfasdqe) value

O:oracle didn't find it, but instr can control the number of occurrences through the fourth parameter.

Select INSTR (sdsfasdqe,sd,1,2) value from dual returns 6

twenty-six。 Ask for a substring

The copy code is as follows:

S:select substring (abcd,2,2) value

O:select substr (abcd,2,2) value from dual

twenty-seven。 Return aijklmnef instead of substring

The copy code is as follows:

S:SELECT STUFF (abcdef, 2, 3, ijklmn) value

O:SELECT Replace (abcdef, bcd, ijklmn) value from dual

twenty-eight。 Replace all substrings

The copy code is as follows:

S: nothing.

O:select Translate (fasdbfasegas,fa, me) value from dual

twenty-nine。 Length

The copy code is as follows:

S:len,datalength

O:length

thirty。 Case conversion lower,upper

thirty-one。 Capitalize the first letter of a word

The copy code is as follows:

S: nothing.

O:select INITCAP (abcd dsaf df) value from dual

thirty-two。 Fill in the space to the left (if the first argument of LPAD is a space, it is the same as the space function)

The copy code is as follows:

S:select space (10) + abcd value

O:select LPAD (abcd,14) value from dual

thirty-three。 Fill in the space to the right (if the first argument of RPAD is a space, it is the same as the space function)

The copy code is as follows:

S:select abcd+space (10) value

O:select RPAD (abcd,14) value from dual

thirty-four。 Delete whitespace

The copy code is as follows:

S:ltrim,rtrim

O:ltrim,rtrim,trim

thirty-five。 Repeat string

The copy code is as follows:

S:select REPLICATE (abcd,2) value

O: nothing.

thirty-six。 Comparison of pronunciation similarity (the return value of the two words is the same, the pronunciation is the same)

The copy code is as follows:

S:SELECT SOUNDEX (Smith), SOUNDEX (Smythe)

O:SELECT SOUNDEX (Smith), SOUNDEX (Smythe) from dual

Compare the difference of soundex with SELECT DIFFERENCE (Smithers, Smythers) in SQLServer

Returns 0-4. 4 is homonym, 1 is the highest.

III. Date function

thirty-seven。 System time

The copy code is as follows:

S:select getdate () value

O:select sysdate value from dual

thirty-eight。 Before and after a few days

Add and subtract directly from integers

thirty-nine。 Ask for a date

The copy code is as follows:

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

forty。 Ask for time

The copy code is as follows:

S:select convert (char (8), getdate (), 108) value

O:select to_char (sysdate,hh34:mm:ss) value from dual

Thank you for your reading, these are the contents of "what are the common functions of SQLServer and Oracle". After the study of this article, I believe you have a deeper understanding of what the common functions of SQLServer and Oracle have, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.