MSSQL and Oracle Common function comparison

Mathematical functions

Serial number	function function	SQL Server usage	Oracle usage
1	Absolute	Select ABS ( -1) value	Select ABS ( -1) value from dual
2	Rounding (Large)	Select Ceiling ( -1.001) value	Select Ceil ( -1.001) value from dual
3	Rounding (small)	Select Floor ( -1.001) value	Select Floor ( -1.001) value from dual
4	Rounding (interception)	Select CAST ( -1.002 as int) value	Select Trunc ( -1.002) value from dual
5	Rounded	Select round (1.23456,4) value	Select round (1.23456,4) value from dual
6	To the power of the bottom	Select EXP (1) value	Select EXP (1) value from dual
7	Take e as the base logarithm	Select Log (2.7182818284590451)	Select ln (2.7182818284590451)
8	Take 10 for base logarithm	Select LOG10 (10)	Select Log (10,10)
9	Take Square	Select SQUARE (4)	Select Power (4,2)
10	Take the square root	Select SQRT (4)	Select SQRT (4)
11	To find the power of any number as the base	Select Power (3,4)	Select Power (3,4)
12	Take a random number	Select rand ()	Select Sys.dbms_random.value (0,1)
13	Take symbol	Select sign (-8)	Select sign (-8)
14	Pi	SELECT PI ()
15	Sin,cos,tan	Select Sin (PI ()/2)	Select Sin (PI ()/2)
16	To find the maximum value of a collection	Select Max (value) value from (Select 1 value Union Select-2 value Union Select 4 Value Union Select 3 Value) a	Select Greatest (1,-2,4,3) value from dual
17	To find the minimum value of a collection	Select min (value) value from (Select 1 value Union Select-2 value Union Select 4 Value Union Select 3 Value) a	Select Least (1,-2,4,3) value from dual
18	Handling null values (Null in F2 is replaced by 10)	Select F1,isnull (f2,10) value from TBL	Select F1,NVL (f2,10) value from TBL

Comparison between values

Serial number	function function	SQL Server usage	Oracle usage
1	To find a character ordinal	Select ASCII (' a ')	Select ASCII (' a ') value from dual
2	To find a character from an ordinal	Select char (value)	Select CHR value from dual
3	Connection	Select ' One ' + ' + ' + ' value '	Select CONCAT (' 11 ', ' 22 ') | | Value from dual
4	Sub-string position	Select CHARINDEX (' s ', ' SDSQ ', 2) value	Select InStr (' SDSQ ', ' s ', 2) value from dual
5	Bits of the fuzzy substring (return 2, parameter minus middle% is returned 7)	Select Patindex ('%d%q% ', ' sdsfasdqe ') value	Select INSTR (' sdsfasdqe ', ' SD ', $) value from dual returns 6
6	To find a child string	Select substring (' ABCD ', 2,2) value	Select substr (' ABCD ', 2,2) value from dual
7	Substring substitution (return AIJKLMNEF)	SELECT STUFF (' abcdef ', 2, 3, ' IJKLMN ') value	SELECT Replace (' abcdef ', ' BCD ', ' ijklmn ') value from dual
8	Sub-string Replace all	Didn't find	Select Translate (' Fasdbfasegas ', ' fa ', ' I ') value from dual
9	Length	Len or Datalength	Length
10	Uppercase and lowercase conversions	Lower,upper	Lower,upper
11	Capitalize the first letter of the word	Didn't find	Select Initcap (' ABCD Dsaf df ') value from dual
12	Left Fill space	Select Space + ' ABCD ' value	Select Lpad (' ABCD ', +) value from dual
13	Right fill space	Select ' ABCD ' +space (Ten) value	Select Rpad (' ABCD ', +) value from dual
14	Remove spaces	Ltrim,rtrim	Ltrim,rtrim,trim
15	Repeating string	Select REPLICATE (' ABCD ', 2) value	Lpad (' d ', 6, ' 0 '), Rpad (' d ', 6, ' 0 ')
16	Comparison of pronunciation similarities (the two words return the same value, pronounced the same)	SELECT SOUNDEX (' Smith '), SOUNDEX (' Smythe ')	SELECT SOUNDEX (' Smith '), SOUNDEX (' Smythe ') from dual

Date function

Serial number	function function	SQL Server usage	Oracle usage
1	System time	Select GETDATE () value	Select Sysdate value from dual
	A few days before and after	Add and subtract directly from integers	Add and subtract directly from integers
	Ask for dates	Select CONVERT (char), GETDATE (), Value	Select Trunc (sysdate) value from dual select To_char (sysdate, ' yyyy-mm-dd ') value from dual
	Ask for Time	Select CONVERT (char (8), GETDATE (), 108) value	Select To_char (sysdate, ' hh24:mm:ss ') value from dual
	Take other parts of the date time	DATEPART and Datename functions (the first parameter is determined)	The second parameter of the TO_CHAR function determines
	Parameter---------------------------------need 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)
	Last day of the month	Didn't find	Select Lsat_day (sysdate) value from dual
	One day of the week (e.g. Sunday)	Didn't find	Select Next_day (sysdate,7) vaule from DUAL;
	String Turn time	can go directly or select cast (' 2004-09-08 ' as datetime) value	Select To_date (' 2004-01-05 22:09:38 ', ' yyyy-mm-dd hh24-mi-ss ') Vaule from DUAL;
	To find the difference between a part of two dates (e.g. seconds)	Select DateDiff (Ss,getdate (), GETDATE () +12.3) value	Subtract directly from two dates (e.g. d1-d2=12.3) SELECT (D1-D2) *24*60*60 vaule from DUAL;
	Date of novelty based on difference (e.g. minutes)	Select DATEADD (Mi,8,getdate ()) value	SELECT sysdate+8/60/24 vaule from DUAL;

MSSQL and Oracle Common function comparison