SQL Syntax differences between Oracle and MS-SQL

http://www.dba-oracle.com/oracle_news/2005_12_16_sql_syntax_differences.htm

 

SQL Syntax differences between Oracle and MS-SQL

Here is a great reference page that shows the syntax differences between Oracle SQL and Microsoft SQL Server SQL syntax:

http://www.bristle.com/Tips/SQL.htm#Differences%20Between%20Oracle%20and%20MS%20SQL%20Server

There is also a great comparison page on SQL built-in functions in Oracle and SQL Server:

http://www.bristle.com/Tips/SQL.htm#Differences%20in%20Built-In%20Functions

Description	Oracle	MS SQL Server
Smallest integer >= n	CEIL	CEILING
Modulus	MOD	%
Truncate number	TRUNC	<none>
Max or min number or string in list	GREATEST, LEAST	<none>
Translate NULL to n	NVL	ISNULL
Return NULL if two values are equal	DECODE	NULLIF
String concatenation	CONCAT(str1,str2)	str1 + str2
Convert ASCII to char	CHR	CHAR
Capitalize first letters of words	INITCAP	<none>
Find string in string	INSTR	CHARINDEX
Find pattern in string	INSTR	PATINDEX
String length	LENGTH	DATALENGTH
Pad string with blanks	LPAD, RPAD	<none>
Trim leading or trailing chars other than blanks	LTRIM(str,chars), RTRIM(str,chars)	<none>
Replace chars in string	REPLACE	STUFF
Convert number to string	TO_CHAR	STR, CAST
Convert string to number	TO_NUMBER	CAST
Get substring from string	SUBSTR	SUBSTRING
Char for char translation in string	TRANSLATE	<none>
Date addition	ADD_MONTH or +	DATEADD
Date subtraction	MONTHS_BETWEEN or -	DATEDIFF
Last day of month	LAST_DAY	<none>
Time zone conversion	NEW_TIME	<none>
Next specified weekday after date	NEXT_DAY	<none>
Convert date to string	TO_CHAR	DATENAME, CONVERT
Convert string to date	TO_DATE	CAST
Convert date to number	TO_NUMBER(TO_CHAR(d))	DATEPART
Date round	ROUND	CONVERT
Date truncate	TRUNC	CONVERT
Current date	SYSDATE	GETDATE
Convert hex to binary	HEXTORAW	CAST
Convert binary to hex	RAWTOHEX	CONVERT
If statement in an expression	DECODE	CASE ... WHEN or COALESCE
User's login id number or name	UID, USER	SUSER_ID, SUSER_NAME
User's database id number or name	UID, USER	USER_ID, USR_NAME
Current user	USER	USER