Friday, November 28, 2008

Oracle <-> SQL Server Equivalents

A growing list of comparitive SQL functions for transition from Oracle to SQL Server:

Replacing Null Values
Oracle: NVL(field1, 'THIS IS NULL')
SQL Server: ISNULL(field1, 'THIS IS NULL')

Oracle: rownum
SQL Server: row_number() over(ORDER BY field1)

Replace Characters in String
Oracle: REPLACE(field1, ';', ',')
SQL Server: REPLACE(field1, ';', ',')

Replace New Line Characters in String (CHR vs. CHAR)
Oracle: REPLACE(REPLACE(REPLACE(field1, CHR(10), ' '), CHR(13), ' '), CHR(9), ' ')
SQL Server: REPLACE(REPLACE(REPLACE(field1, CHAR(10), ' '), CHAR(13), ' '), CHAR(9), ' ')

ToChar for DD/MM/YYYY HH24:MM:SS
Oracle: TO_CHAR(datefield1,'dd/mm/yyyy hh24:mm:ss')
SQL Server: convert(varchar,datefield1,103) + ' ' + convert(varchar,datefield1,108)

Outer Join
Oracle: =+
SQL Server: =*

'IN' List
Oracle: IN list limited to 1000 expressions
SQL Server: 18,000 didn't hit any limits!

Oracle: Decode(field1,'Yes','Y','N')
SQL Server: CASE field1 WHEN 'Yes' THEN 'Y' ELSE 'N' END AS "Field 1"

No comments: