Send you some
1. Absolute value
SQL: Select abs(- 1) value.
O: select abs(- 1) value from dual.
2. Round (large)
S: Select the upper limit (-1.00 1) value.
O: select the value of ceil(- 1.00 1) from dual.
3. Round (small)
S: select the lower limit (-1.00 1) value.
O: select the bottom value from dual (-1.00 1).
4. Rounding (Truncation)
S: select the value of cast(- 1.002 as int).
O: select trunc(- 1.002) value from dual.
round up or down
S: select round (1.23456,4) value 1.23460.
O: select the value of round (1.2346,4) from dual/kloc-0.
6.e is the power of radix.
S: choose Exp( 1) value of 2.7182818284590451
O: select Exp( 1) value from dual 2.7 1828 182.
7. logarithm based on e
S: select the log (2.7 1 82818284590451) value1
O: select ln (2.7182818284590451) value from dual; 1
8. Logarithm based on 10.
S: select the log 10( 10) value 1
O: select the log( 10, 10) value from dual; 1
9. Occupy the square
S: select the square (4) value 16.
O: select the power (4,2) value from dual 16.
10. Find the square root
S: select SQRT(4) value 2.
O: select SQRT(4) value from dual 2.
1 1. Find the base of any number.
S: choose power (3,4) value 8 1
O: select the power (3,4) value from dual 8 1
12. Random number
S: select rand () value.
O: select the value of sys.dbms_random.value(0, 1) from dual;
13. Take this symbol.
S: Select symbol (-8) value-1
O: select the symbol (-8) value from dual-1
-Mathematical function
14. Pi
S: Select the value of PI () 3.1415926535897931
Question: I don't know.
15. The parameters of sin, cos and tan are in radians.
For example, choose the value of sin(PI()/2) to get 1(SQLServer).
16.asin, acos, atan and atan2 return radians.
17. radian angle exchange (SQLServer, Oracle doesn't know)
Degree: radian-> angle
Radian: Angle-> radian
-Comparison between values.
18. Find the maximum value of a set
S: select the maximum value.
(Select 1 value.
alliance
Select a value of -2.
alliance
Select 4 values
alliance
Select 3 values) a
O: select the maximum value from dual (1,-2,4,3).
19. Find the minimum value of a set
S: select the minimum value from it.
(Select 1 value.
alliance
Select a value of -2.
alliance
Select 4 values
alliance
Select 3 values) a
O: select the minimum (1,-2,4,3) value from dual.
20. How to deal with null values (null in F2 is replaced by 10)
S: select F 1, IsNull(F2, 10) from Tbl.
O: select F 1, nvl(F2, 10) from Tbl.
-Comparison between values
2 1. Find the character number
S: select ascii('a') value.
O: select ascii('a') value from dual.
22. Find characters from serial numbers
S: select char(97) value.
O: select the value of chr(97) from dual.
23. Connect
S: select the value of' 1 1'+'22'+'33'.
O: value in select concat ('11','22 ')| | 33 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-2 is returned, and 7 is returned if the% in the middle of the parameter is removed.
S: select patindex('%d%q%',' sdsfasdqe') value.
O: Oracle Bone Inscriptions didn't find it, but instr can ask questions through the fourth haze. BR> select instr ('sdsfasdqe',' SD', 1, 2) value from dual returns 6.
24. Find substring
S: Select substring ('abcd', 2,2) value.
O: select substr('abcd', 2,2) value from dual.
25. substring instead of returning aijklmnef.
S: select the value of STUFF('abcdef', 2,3,' ijklmn').
O: select the value of Replace('abcdef',' bcd',' ijklmn') from dual.
26. All substrings are replaced.
S: I can't find it.
O: select translate ('fasdbfasegas',' fa',' I') values from dual.
27. Length
S:len, data length
O: length
28. Uppercase and lowercase conversion
Capitalize the first letter of a word.
S: I can't find it.
O: select INITCAP('abcd dsaf df') value from dual.
30. Fill in the blank on the left (the first parameter of LPAD is empty, which has the same function as blank space)
S: Select space (10)+'abcd' value.
O: select LPAD('abcd', 14) value from dual.
3 1. Fill the space to the right (if the first parameter of RPAD is a space, it is the same as the space function).
S: Select the value of' ABCD '+ space (10).
O: Select RPAD('abcd', 14) value from dual.
32. Delete space
Student: ltrim, rtrim
O:ltrim、rtrim、trim
33. Repeat the string
S: select copy ('abcd', 2) value.
O: I didn't find it.
34. Comparison of pronunciation similarity (these two words have the same return value and the same pronunciation)
S: Select SOUNDEX ('Smith'), SOUNDEX ('Smythe').
O: select SOUNDEX ('Smith') and SOUNDEX ('Smythe') from dual.
Use select difference ('Smithers',' Smythers') in SQLServer to compare the differences of soundex.
Returns 0-4, where 4 is a homophone, 1 highest.
-Date function
35. System time
S: select the getdate () value.
O: select the system date value from dual.
36. Before and after a few days
Add and subtract directly with integers.
37. Ask for a date
S: select convert(char( 10), getdate (), 20) values.
O: select trunc(sysdate) value from dual.
Select to_char(sysdate,' yyyy-mm-dd') value from dual.
38. Seek time
S: select the value of convert(char(8), getdate (), 108).
O: select to_char(sysdate,' hh24:mm:ss') value from dual.
39. Take other parts of the date and time.
S:DATEPART and DATENAME functions (determined by the first parameter)
The second parameter of O:to_char function determines
Parameters-The following table needs to be supplemented.
Yyyy year
Quarterly qq, Q (Quarterly)
Month mm, m (m O is invalid)
Dayofyear dy, y (O stands for week)
Day, day (day is invalid)
Zhou Zhou, Zhou Zhou (Zhou Zhou is invalid)
Working day dw (O unclear)
Hours hh, HH 12, HH24 (HH 12, HH24S is invalid)
Minute mi, n(n 0 is valid)
Second ss, s (s O is invalid)
Seconded ms (o is invalid)
-
40. Last day of every month
S: I don't know
O: select the last day (system date) value from dual.
4 1. Someday this week (such as Sunday)
S: I don't know
O: select the Next_day(sysdate, 7) value from DUAL;
42. String to time
S: You can directly switch or select the value of cast ('2004-09-08' as the date and time).
O: select to _ date ('2004-01-05 22: 09: 38',' yyyy-mm-dd hh24-mi-ss') values from DUAL;
43. Find the difference (for example, seconds) of a part of two dates.
S: select datediff(ss, getdate (), getdate()+ 12.3) values.
O: directly subtract two dates (for example, d 1-d2= 12.3).
SELECT(d 1-D2)* 24 * 60 * 60 value FROM DUAL；
44. Find a new date (such as minutes) according to the difference.
S: select dateadd(mi, 8, getdate ()) value.
O: select sysdate+8/60/24 value from DUAL;
45. Find the time in different time zones
S: I don't know
O: select New_time(sysdate,' ydt',' gmt') value from DUAL;
-Time zone parameter, Beijing in the East Eighth District should be YDT-
Atlantic standard time
BST BDT Bering sea standard time
Central standard time
eastern standard time
GMT Greenwich mean time
HST HDT Alaska-Hawaii Standard Time
MST MDT mountain standard time
Newfoundland standard time
Pacific Standard Time
YST YDT Yukon standard time
Character functions supported by Oracle and their Microsoft SQL Server equivalents;
Function Oracle Microsoft SQL Server
Convert characters to ASCII ASCII ASCII.
String concatenation (expression+expression)
Convert ASCII to character CHR CHAR.
Returns the starting character (starting from the left) in a string.
Convert characters to lowercase lowercase lowercase
Convert characters to uppercase.
Fill in the left LPAD N/A of the string.
Clear the blank LTRIM LTRIM at the beginning
Clear the blank RTRIM RTRIM at the end.
The starting pattern in the string instr patindex.
The repeating string RPAD is copied many times.
The phonetic representation of the string is SOUNDEX SOUNDEX.
Chord RPAD space with repetitive space
TO_CHAR string converted from digital data to character data
Substring substring
Substitution character replacement material
Capitalize the first letter of each word in the string initcap n/a.
Translation string translation is not applicable.
String length length date length or length
Maximum does not apply, the largest string in the list.
The smallest string in the list is not applicable.
If NULL, the conversion string NVL ISNULL.
Date function
Function Oracle Microsoft SQL Server
Date column+/-value) or
Add _ Month Date Add
Difference between two dates (date column+/-value) or
Number of months between DATEDIFF
Current date and time SYSDATE GETDATE ()
The last day of a month. The last day is not applicable.
Time zone conversion new time is not applicable.
The first Sunday after the second day does not apply.
The string TO_CHAR DATENAME representing the date.
The integer TO_NUMBER representing the date
(TO_CHAR)) date part
Date rounding rounding conversion
TRUNC conversion of date truncation
The string TO_DATE CONVERT converted to a date.
If it is empty, the conversion date NVL is empty.
mental set shifting
Function Oracle Microsoft SQL Server
Numbers are converted to the character TO_CHAR CONVERT.
Convert characters to numbers TO_NUMBER CONVERT
The date is converted to the character TO_CHAR CONVERT.
Character to date conversion
16 binary conversion to binary HEX_TO_RAW CONVERT
Convert binary to 16 RAW_TO_HEX CONVERT.
Other line-level functions
Function Oracle Microsoft SQL Server
Returns the first non-empty expression decoding merge.
Current Sequence Value Current value is not applicable.
Next sequence value NEXTVAL N/A
If exp 1 = exp2, null decoding null is returned.
User login account ID number UID SUSER_ID
User login user name
User database ID number UID USER_ID
User Database Name User User Name
Current user current user current user
User environment (audit trail) USERENV N/A
Level N/A in CONNECT BY clause
Aggregation function
Function Oracle Microsoft SQL Server
Average AVG AVG
Count, count, count
Max-max
Min min min
Standard deviation STDDEV STDEV or STDEVP
Sum sum
Variance VARiance var or VARP
Oracle also has a useful function EXTRACT, which can extract and return a specific time domain in a date-time or time interval expression:
Excerpt (year from date)