Built-in SQL Functions - Oracle to Microsoft SQL Server Migration

The reference and articles provide detailed technical information on migrating built-in SQL functions from Oracle to Microsoft SQL Server and SQL Azure.

Last Update: Oracle 11g Release 2 and Microsoft SQL Server 2012

All Functions

Functions in alphabetical order:

Oracle SQL Server
1 ADD_MONTHS Add specified number of months DATEADD
2 CAST Convert one built-in data type into another
3 DECODE Evaluate a list of conditions CASE Expression
4 EMPTY_BLOB Create an empty BLOB value 0x Constant (Empty binary string)
5 EMPTY_CLOB Create an empty CLOB or NCLOB value '' (Empty string)
6 EXTRACT for Datetime Extract day, month, year etc from datetime
7 INITCAP Capitalize the first letter of each word User-defined function
8 INSTR Find position of substring in string CHARINDEX First occurrence only, different parameter order
9 LAST_DAY Get last date of month EOMONTH Since SQL Server 2012
10 LENGTH Get string length in characters LEN CHAR handled differently, excludes trailing spaces
11 LOWER Convert string to lowercase LOWER
12 LPAD Left-pad string to the specified length Expression using REPLICATE, RIGHT and LEFT
13 MOD Get the remainder of division of one number by another % Operator
14 MONTHS_BETWEEN Get number of months between two dates
15 NVL Replace NULL with expression ISNULL
16 REPLACE Replaces all occurrences of string with another string REPLACE
17 SIGN If value is positive return 1, if negative then -1, if zero then 0 SIGN
18 SUBSTR Return a substring from string SUBSTRING Negative start position is not allowed, length must be specified
19 TO_CHAR for Datetime Convert datetime to string CONVERT
20 TO_DATE Convert string to datetime CONVERT
21 TRANSLATE One-to-one single-character substitution Expressions using REPLACE or User-defined function
22 TRIM Trim leading or trailing characters LTRIM and RTRIM
23 TRUNC for Datetime Truncate datetime Expressions using CONVERT
24 UNISTR Convert Unicode code points to characters Expressions using NCHAR

Arithmetic Functions

Arithmetic functions:

Oracle SQL Server
1 MOD Get the remainder of division of one number by another % Operator
2 SIGN If value is positive return 1, if negative then -1, if zero then 0 SIGN

String Functions

String functions conversion from Oracle to SQL Server:

Oracle SQL Server
1 INITCAP Capitalize the first letter of each word User-defined function
2 INSTR Find position of substring in string CHARINDEX First occurrence only, different parameter order
3 LENGTH Get string length in characters LEN CHAR handled differently, excludes trailing spaces
4 LOWER Convert string to lowercase LOWER
5 LPAD Left-pad string to the specified length Expression using REPLICATE, RIGHT and LEFT
6 REPLACE Replaces all occurrences of string with another string REPLACE
7 SUBSTR Return a substring from string SUBSTRING Negative start position is not allowed, length must be specified
8 TO_CHAR for Datetime Convert datetime to string CONVERT
9 TRANSLATE One-to-one single-character substitution Expressions using REPLACE or User-defined function
10 TRIM Trim leading or trailing characters LTRIM and RTRIM
11 UNISTR Convert Unicode code points to characters Expressions using NCHAR

Datetime Functions

Date and time functions:

Oracle SQL Server
1 ADD_MONTHS Add specified number of months DATEADD
2 EXTRACT for Datetime Extract day, month, year etc from datetime
3 LAST_DAY Get last date of month EOMONTH
4 MONTHS_BETWEEN Get number of months between two dates
5 TO_CHAR for Datetime Convert datetime to string CONVERT
6 TO_DATE Convert string to datetime CONVERT
7 TRUNC for Datetime Truncate datetime Expressions using CONVERT

Conversion and Format Functions

Conversion and format functions:

Oracle SQL Server
1 CAST Convert one built-in data type into another
2 TO_CHAR for Datetime Convert datetime to string CONVERT
3 TO_DATE Convert string to datetime CONVERT
4 TRANSLATE One-to-one single-character substitution Expressions using REPLACE or User-defined function
5 UNISTR Convert Unicode code points to characters Expressions using NCHAR

Case and Decode Functions

Case and decode functions:

Oracle SQL Server
1 DECODE Evaluate a list of conditions CASE Expression
2 NVL Replace NULL with expression ISNULL
3 SIGN If value is positive return 1, if negative then -1, if zero then 0 SIGN

NULL Functions

Functions handling NULL values:

Oracle SQL Server
1 NVL Replace NULL with expression ISNULL

LOB Functions

Functions handling LOB (large object) values:

Oracle SQL Server
1 EMPTY_BLOB Create an empty BLOB value 0x Constant (Empty binary string)
2 EMPTY_CLOB Create an empty CLOB or NCLOB value '' (Empty string)