All functions in alphabetical order:
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:
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 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 |
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:
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:
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 |
Functions handling NULL values:
Oracle | SQL Server | |||
1 | NVL | Replace NULL with expression | ISNULL |
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) |