SQL functions Summary "featured articles"

1. Absolute value
Sql: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 (interception)
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 is the power of the bottom
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 as the base logarithm
S:select log10 (Ten) value 1
O:select log (10,10) value from dual; 1
9. Take the Square
S:select SQUARE (4) value 16
O:select Power (4,2) value from dual 16
10. Take the square root
S:select SQRT (4) Value 2
O:select SQRT (4) value from dual 2
11. Seek the power of any number as the base
S:select Power (3,4) value 81
O:select Power (3,4) value from dual 81
12. Take a 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 sign ( -8) value from Dual-1
----------Mathematical Functions
14. Pi
S:select PI () value 3.1415926535897931
O: I don't know
15.sin,cos,tan parameters are measured in radians
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. Find the maximum value of the set
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
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
Comparison between--------values
21. Find the character number
S:select ASCII (' a ') value
O:select ASCII (' a ') value from dual
22. To find a character from an ordinal
S:select char (value)
O:select Chr (value from dual)
23. Connect
S:select ' one ' + ' + ' + ' value '
O:select CONCAT (' 11 ', ' 22 ') | | Value from dual
23. Sub-string 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-returns 2, the parameter minus the middle% returns 7
S:select patindex ('%d%q% ', ' sdsfasdqe ') value
O:oracle did not find, but INSTR can through the fourth haze asked acutely to see the squid ask?br> select INSTR (' sdsfasdqe ', ' SD ', ") value from dual return 6
24. Finding substrings
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 from dual
26. Sub-string Replace all
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. Capitalize the first letter of the word
S: Not Found
O:select initcap (' ABCD Dsaf df ') value from dual
30. Left fill space (the first parameter of the Lpad is a space, the same as the space function)
S:select space + ' ABCD ' value
O:select lpad (' ABCD ', +) value from dual
31. Right-fill space (the first argument of Rpad is a space, the same space function)
S:select ' ABCD ' +space (Ten) value
O:select rpad (' ABCD ', +) value from dual
32. Remove spaces
S:ltrim,rtrim
O:ltrim,rtrim,trim
33. Repeating strings
S:select REPLICATE (' ABCD ', 2) value
O: Not Found
34. Comparison of pronunciation similarity (these two words return the same value, pronounced the same)
S:select SOUNDEX (' Smith '), SOUNDEX (' Smythe ')
O:select SOUNDEX (' Smith '), SOUNDEX (' Smythe ') from dual
SQL Server compares soundex differences with Select difference (' Smithers ', ' smythers ')
Return 0-4,4 for the same, 1 highest
--------------Date function
35. System Time
S:select getdate () value
O:select Sysdate value from dual
36. A few days before and after
Add and subtract directly from integers
37. Date of request
S:select convert (char), GETDATE (), Value
O:select trunc (sysdate) value from dual
Select To_char (sysdate, ' yyyy-mm-dd ') value from dual
38. Ask for 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 the date time
S:datepart and Datename functions (the first parameter is determined)
O:to_char functionThe second parameter determines
Parameter---------------------------------the table below needs to be supplemented
Year yy, yyyy
Quarter QQ, Q (quarterly)
month mm, m (invalid for M o)
DayOfYear dy, y (o table week)
Day DD, D (invalid for 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: I don't know.
O:select last_day (sysdate) value from dual
41. One day of the week (e.g. Sunday)
S: I don't 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. Date of novelty based on difference (e.g. minutes)
S:select DateAdd (Mi,8,getdate ()) value
O:select sysdate+8/60/24 vaule from DUAL;
45. Find time in different time zones
S: I don't know.
O:select new_time (sysdate, ' ydt ', ' GMT ') vaule from DUAL;
-----Time zone parameter, Beijing should be ydt in the East 8 district-------
AST ADT Atlantic Standard Time
BST BDT Bering Sea Standard Time
CST CDT Central Standard Time
EST EDT Eastern Standard Time
GmtGreenwich Standard Time
HST HDTAlaska-Hawaii Standard Time
MST MDT Mountain Standard Time
NSTNewfoundland Standard Time
PST PDT Pacific Standard Time
YST YDT Yukon Standard Time
Oracle supports character functions and their Microsoft SQL Server equivalent functions:
function Oracle Microsoft SQL Server
Converts a character to ASCII ASCII ASCII
String connection CONCAT (expression + expression)
Convert ASCII to character CHR CHAR
Returns the start character in a string (from left) INSTR CHARINDEX
Converts the character to lowercase LOWER LOWER
Convert characters to uppercase UPPER UPPER
padding string left Lpad N/A
Clears the beginning blank LTRIM LTRIM
Clear the trailing blank RTRIM RTRIM
The starting pattern in the string (pattern) INSTR PATINDEX
Multiple repeated strings Rpad REPLICATE
The phonetic representation of a string SOUNDEX SOUNDEX
string of repeated spaces rpad space
Converting from digital data to character data To_char STR
Sub-string SUBSTR SUBSTRING
Replacement character Replace STUFF
Capitalize the first letter of each word in a string initcap N/A
Translate string TRANSLATE N/A
String lengths length Datelength or LEN
The largest string in the list greatest N/A
The smallest string in the list LEAST N/A
If NULL, the string is converted NVL ISNULL

Date function
function Oracle Microsoft SQL Server
Date addition (Date column +/-value) or
Add_months DATEADD
Two date difference (date column +/-value) or
Months_between DATEDIFF
Current date and Time Sysdate GETDATE ()
Last day of one months Last_day N/A
Time zone conversion new_time N/A
The first Sunday after the dateNext_day N/A
String representing the date To_char datename
Integer To_number representing the date
(To_char)) DATEPART
Date Rounding ROUND CONVERT
Date truncation TRUNC CONVERT
Convert string to date to_date convert
If NULL, the conversion date NVL ISNULL

Conversion functions
function Oracle Microsoft SQL Server
Convert numbers to characters To_char convert
Convert character to digital To_number convert
Date converted to character To_char convert
Convert character to date to_date convert
16 binary conversion to 2 binary Hex_to_raw convert
2 binary conversion to 16 binary Raw_to_hex convert

Other line-level functions
function Oracle Microsoft SQL Server
Returns the first non-empty expression DECODE coalesce
Current sequence value Currval N/A
Next sequence value Nextval N/A
If EXP1 = EXP2, returns null DECODE NULLIF
User Login account ID number UID suser_id
User Login username User suser_name
User Database ID number UID user_id
User Database name user_name
Current User Current_User Current_User
User Environment (audit trail) USERENV N /A
Levels in the Connect by clause level n /A

Aggregate functions
function Oracle Microsoft SQL Server
Average avg Avg
Count Count Count
Maximum Max Max
Minimum min min
Standard deviation STDDEV STDEV or STDEVP
Summation sum sum
Variance Variance VAR or VARP
Oracle also has a useful function, extract, that extracts and returns a specific time field in a datetime or time interval expression:
EXTRACT (year from date)

SQL functions Summary "featured articles"