Plsql and TSQL common function comparison _oracle

Mathematical functions
1. Absolute value
S: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 to the bottom of the power
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
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 (a) 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. To find any number of the bottom of the power
S:select Power (3,4) value 81
O:select Power (3,4) value from dual 81

12. Take 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

14. Pi
S:select PI () value 3.1415926535897931
O: I don't know.

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 don't know)
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 in lieu of 10)
S:select f1,isnull (f2,10) value from TBL
O:select F1,NVL (f2,10) value from TBL

21. Ask for character serial number
S:select ASCII (' a ') value
O:select ASCII (' a ') value from dual

22. Ask for character from ordinal number
S:select char () value
O:select chr () value from dual

23. Connection
S:select ' one ' + ' + ' + ' value
O:select CONCAT (' One ', ') ' 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. Position of fuzzy substring-return 2, parameter minus middle% then return 7
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 return 6

24. Find the substring
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: I didn't find it.
O:select Translate (' Fasdbfasegas ', ' fa ', ' I ') value from dual

27. Length
S:len,datalength
O:length

28. Capitalization Conversion Lower,upper

29. Capitalize the first letter of the word
S: I didn't find it.
O:select initcap (' ABCD Dsaf df ') value from dual

30. Left fill space (the first parameter of Lpad is a space function)
S:select space + ' ABCD ' value
O:select lpad (' ABCD ') value from dual

31. Right complement space (the first parameter of Rpad is a space function)
S:select ' ABCD ' +space (+) value
O:select rpad (' ABCD ') value from dual

32. Delete Space
S:ltrim,rtrim
O:ltrim,rtrim,trim

33. Duplicate string
S:select REPLICATE (' ABCD ', 2) value
O: Not Found

34. Comparison of pronunciation similarity (these two words return the same value, the same pronunciation)
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

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. Request a Date
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 the other part of the date time
S:datepart and DATENAME functions (first parameter decision)
The second parameter of the O:to_char function determines

Parameter ——————————— The following table needs to be supplemented
Year yy, yyyy
Quarter QQ, Q (quarterly)
month mm, m (M o Invalid)
DayOfYear dy, y (o table week)
Day DD, D (invalid for D O)
Week wk, ww (wk o Invalid)
Weekday DW (o not clear)
Hour Hh,hh12,hh24 (hh12,hh24 s is not valid)
Minute mi, n (N o Invalid)
Second SS, s (S o invalid)
Millisecond MS (O void)
———————————————-

40. The last day of the month
S: Don't know
O:select last_day (sysdate) value from dual

41. One day of the week (e.g. Sunday)
S: Don't know
O:select next_day (sysdate,7) vaule from DUAL;

42. String Turn Time
S: Can be 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. The date of novelty based on the difference (e.g. minutes)
S:select DateAdd (Mi,8,getdate ()) value
O:select sysdate+8/60/24 vaule from DUAL;

45. Different time zone
S: Don't know
O:select new_time (sysdate, ' ydt ', ' GMT ') vaule from DUAL;

-– time zone parameters, Beijing in the East 8 area should be Ydt ——-
AST ADT Atlantic Standard Time
BST BDT Bering Sea Standard Time
CST CDT Central Standard Time
EST EDT Eastern Standard Time
GMT Greenwich Standard Time
HST HDT Alaska? Hawaii Standard Time
MST MDT Mountain Standard Time
NST Newfoundland Standard Time
PST PDT Pacific Standard Time
YST YDT Yukon Standard Time

—–

Oracle-supported character functions and their Microsoft SQL Server equivalence functions:

Functions Oracle Microsoft SQL Server
Converts characters to ASCII ASCII ASCII
String connection CONCAT (expression + expression)
Converts ASCII to character CHR CHAR
Returns the start character from the string (left) INSTR CHARINDEX
Converts characters to lowercase LOWER LOWER
Converts characters to uppercase UPPER UPPER
Fill string left Lpad N/A
Clears the starting blank LTRIM LTRIM
Clears the trailing blank RTRIM RTRIM
Starting mode (pattern) in a string 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 length datelength or LEN
The largest string in the list greatest N/A
The smallest string in the list least n/A
Converts a string NVL if null ISNULL

Date function

Functions Oracle Microsoft SQL Server
Date Added (date column/+) or
Add_months DATEADD
Two-date difference (date column +/= value) or
Months_between DATEDIFF
Current date and Time Sysdate GETDATE ()
The last day of one months Last_day N/A
Time zone conversion new_time N/A
Next_day N/A on the first Sunday after the date
A string representing a date To_char datename
Integer To_number representing the date
(To_char)) DATEPART
Date rounded ROUND CONVERT
Date truncated TRUNC CONVERT
string conversion to date To_date convert
Convert date if NULL NVL ISNULL

Conversion functions

Functions Oracle Microsoft SQL Server
Convert numbers to characters To_char convert
Convert character to digital To_number convert
Convert date to character To_char convert
Character conversion to date to_date convert
16 binary conversion to 2 Hex_to_raw convert
2 binary conversion to 16 Raw_to_hex convert

Other row-level functions

Functions 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 a null DECODE Nullif
User Login account ID number UID suser_id
User Login Username Suser_name
User Database ID Digital UID user_id
User Database name username user_name
Current User Current_User Current_User
User Environment (audit trail) USERENV N/A
Level levels in the Connect BY clause N/A

Aggregate functions

Functions 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)