Differences in syntax between Oracle and MS SQL

15 Jan

 

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

 

Read more below

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

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

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: