Oracle and SQL Server comparison operators are nearly identical.
Operator | Oracle | SQL Server |
Equal to | = | = |
Greater than | > | > |
Less than | < | < |
Greater than or equal to | >= | >= |
Less than or equal to | <= | <= |
Not equal to | !=, ^=, < > | !=, <> |
Not greater than | N/A | !> |
Not less than | N/A | !< |
In any member in set | IN | IN |
Not in any member in set | NOT IN | NOT IN |
Any value in set | ANY, SOME | ANY, SOME |
All values in set | ALL | ALL |
Like pattern | LIKE | LIKE |
Not like pattern | NOT LIKE | NOT LIKE |
Value between | BETWEEN | BETWEEN |
Value not between | NOT BETWEEN | NOT BETWEEN |
Value exists | EXISTS | EXISTS |
Value does not exist | NOT EXISTS | NOT EXISTS |
Value {is | is not} NULL | IS NULL, IS NOT NULL | IS NULL, IS NOT NULL |
The SQL Server LIKE keyword offers some useful wildcard search options that are not supported by Oracle. In addition to supporting the % and _ wildcard characters, the [ ] and [^] characters are also supported.
If you are familiar with the UNIX operating system, you recognize these characters and their capability. The [ ] character set is used to search for any single character within a specified range. For example, if you are searching for the characters a through f in a single character position, you can specify this with '[a-f]' or '[abcdef]'.
The [^] wildcard character is used to specify those characters NOT in the specified range. For example, if any character except for a through f is acceptable, you use '[^a - f]' or '[^abcdef]'. The usefulness of these additional wildcard characters is shown in the table.
Oracle | SQL Server |
SELECT * FROM STUDENT_ADMIN.STUDENT WHERE LNAME LIKE 'A%' OR LNAME LIKE 'B%' OR LNAME LIKE 'C%' |
SELECT * FROM STUDENT_ADMIN.STUDENT WHERE LNAME LIKE '[ABC]%' |
For more information about the LIKE keyword, see the Microsoft SQL Server Database Developer's Companion.
Oracle uses two pipe symbols (||) as the string concatenation operator, while SQL Server uses the plus sign (+). This difference requires minor revision in your application program code.
Oracle | SQL Server |
SELECT FNAME||' '||LNAME AS NAME FROM STUDENT_ADMIN.STUDENT |
SELECT FNAME +' '+ LNAME AS NAME FROM STUDENT_ADMIN.STUDENT |