Mathematics function
1. Absolute value
Copy Code code as follows:
S:select ABS ( -1) value
O:select ABS ( -1) value from dual
2. Rounding (Large)
Copy Code code as follows:
S:select Ceiling ( -1.001) value
O:select ceil ( -1.001) value from dual
3. Rounding (small)
Copy Code code as follows:
S:select Floor ( -1.001) value
O:select Floor ( -1.001) value from dual
4. Rounding (interception)
Copy Code code as follows:
S:select cast ( -1.002 as int) value
O:select trunc ( -1.002) value from dual
5. Rounding
Copy Code code as follows:
S:select round (1.23456,4) value 1.23460
O:select round (1.23456,4) value from dual 1.2346
6.E to the bottom of the power
Copy Code code as follows:
S:select EXP (1) Value 2.7182818284590451
O:select EXP (1) value from dual 2.71828182
7. Take e as the base of the logarithm
Copy Code code 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
Copy Code code as follows:
S:select log10 (a) value 1
O:select log (10,10) value from dual; 1
9. Take the Square
Copy Code code as follows:
S:select SQUARE (4) value 16
O:select Power (4,2) value from dual 16
10. Take the square root
Copy Code code as follows:
S:select SQRT (4) Value 2
O:select SQRT (4) value from dual 2
11. To find any number of the bottom of the power
Copy Code code as follows:
S:select Power (3,4) value 81
O:select Power (3,4) value from dual 81
12. Take random number
Copy Code code as follows:
S:select rand () value
O:select Sys.dbms_random.value (0,1) value from dual;
13. Take the symbol
Copy Code code as follows:
S:select sign ( -8) value-1
O:select sign ( -8) value from Dual-1
14. Pi
Copy Code code as follows:
S:select PI () value 3.1415926535897931
O: Not Found
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 found)
DEGREES: Radian-〉 Angle
RADIANS: Angle-〉 radians
Ii. comparison between numerical values
18. Find the maximum value of the set
Copy Code code 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,-2,4,3) value from dual
19. Find the minimum value of the set
Copy Code code 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,-2,4,3) value from dual
20. How to handle null values (Null in F2 in lieu of 10)
Copy Code code as follows:
S:select f1,isnull (f2,10) value from TBL
O:select F1,NVL (f2,10) value from TBL
21. Ask for character serial number
Copy Code code as follows:
S:select ASCII (a) value
O:select ASCII (a) value from dual
22. Ask for character from ordinal number
Copy Code code as follows:
S:select char () value
O:select chr () value from dual
23. Connection
Copy Code code as follows:
S:select 11+22+33 Value
O:select CONCAT (11,22) | | Value from dual
24. Sub-string position--return 3
Copy Code code as follows:
S:select CHARINDEX (s,sdsq,2) value
O:select INSTR (sdsq,s,2) value from dual
25. Position of fuzzy substring--return 2, parameter minus middle% return 7
Copy Code code as follows:
S:select patindex (%D%Q%,SDSFASDQE) value
O:oracle didn't find it, but InStr can control the number of occurrences with a fourth parameter.
Select INSTR (sdsfasdqe,sd,1,2) value from dual returns 6
26. Find the substring
Copy Code code as follows:
S:select substring (abcd,2,2) value
O:select substr (abcd,2,2) value from dual
27. Substring instead of return AIJKLMNEF
Copy Code code as follows:
S:select STUFF (abcdef, 2, 3, IJKLMN) value
O:select Replace (abcdef, BCD, IJKLMN) value from dual
28. Sub-string Replace all
Copy Code code as follows:
S: I didn't find it.
O:select Translate (FASDBFASEGAS,FA, I) value from dual
29. Length
Copy Code code as follows:
S:len,datalength
O:length
30. Capitalization Conversion Lower,upper
31. Capitalize the first letter of the word
Copy Code code as follows:
S: I didn't find it.
O:select Initcap (ABCD Dsaf DF) value from dual
32. Left fill space (the first parameter of Lpad is a space function)
Copy Code code as follows:
S:select Space (a) +ABCD value
O:select Lpad (abcd,14) value from dual
33. Right complement space (the first parameter of Rpad is a space function)
Copy Code code as follows:
S:select Abcd+space (+) value
O:select Rpad (abcd,14) value from dual
34. Delete Space
Copy Code code as follows:
S:ltrim,rtrim
O:ltrim,rtrim,trim
35. Duplicate string
Copy Code code as follows:
S:select REPLICATE (abcd,2) value
O: Not Found
36. Comparison of pronunciation similarity (these two words return the same value, the same pronunciation)
Copy Code code as follows:
S:select SOUNDEX (Smith), SOUNDEX (Smythe)
O:select SOUNDEX (Smith), SOUNDEX (Smythe) from dual
Compare Soundex difference with Select difference (Smithers, smythers) in SQL Server
Return 0-4,4 to the homonym, 1 highest
Third, date function
37. System Time
Copy Code code as follows:
S:select getdate () value
O:select Sysdate value from dual
38. A few days before and after
Add and subtract directly from integers
39. Request a Date
Copy Code code as follows:
S:select convert (char (), GETDATE (), Value
O:select trunc (sysdate) value from dual
Select To_char (SYSDATE,YYYY-MM-DD) value from dual
40. Ask for Time
Copy Code code as follows:
S:select Convert (char (8), GETDATE (), 108) value
O:select To_char (SYSDATE,HH24:MM:SS) value from dual
