Comparison Operators

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 LIKE Keyword

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.

String Concatenation

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