Cloud Software Group, Inc. EBX®
Documentation > Developer Guide > SQL in EBX®
Navigation mode Documentation > Developer Guide > SQL in EBX®

# Comparison operators

The table below lists all the SQL arithmetic operators and functions supported by EBX®, along with their standard SQL syntax. Some functions may have optional parameters: they are surrounded by square brackets.

Operator syntax | Description and example(s)
--- | ---
value1 = value2 | Equals `SELECT 1 = 0 : false` `SELECT 4 = 4 : true`
value1 <> value2 | Not equals `SELECT 1 <> 0 : true` `SELECT 4 <> 4 : false`
value1 > value2 | Greater than `SELECT 1 > 0 : true` `SELECT 4 > 4 : false`
value1 >= value2 | Greater than or equal `SELECT 1 >= 0 : true` `SELECT 4 >= 4 : true`
value1 < value2 | Lower than `SELECT 1 < 0 : false` `SELECT 4 < 4 : false`
value1 <= value2 | Less than or equal `SELECT 1 <= 0 : false` `SELECT 4 <= 4 : true`
value IS NULL | Whether value is null `SELECT 1 IS NULL : false` `SELECT NULL IS NULL : true`
value IS NOT NULL | Whether value is not null `SELECT 1 IS NOT NULL : true` `SELECT NULL IS NOT NULL : false`
value1 IS DISTINCT FROM value2 | Whether two values are not equal, treating null values as the same `SELECT 1 IS DISTINCT FROM 1 : false` `SELECT 1 IS DISTINCT FROM 4 : true` `SELECT 1 IS DISTINCT FROM NULL : true` `SELECT NULL IS DISTINCT FROM NULL : false`
value1 IS NOT DISTINCT FROM value2 | Whether two values are equal, treating null values as the same `SELECT 1 IS NOT DISTINCT FROM 1 : true` `SELECT 1 IS NOT DISTINCT FROM 4 : false` `SELECT 1 IS NOT DISTINCT FROM NULL : false` `SELECT NULL IS NOT DISTINCT FROM NULL : true`
value1 BETWEEN value2 AND value3 | Whether value1 is greater than or equal to value2 and less than or equal to value3 `SELECT 4 BETWEEN 3 AND 10 : true` `SELECT 1 BETWEEN 3 AND 10 : false`
value1 NOT BETWEEN value2 AND value3 | Whether value1 is greater than or equal to value2 and less than or equal to value3 `SELECT 4 NOT BETWEEN 3 AND 10 : false` `SELECT 1 NOT BETWEEN 3 AND 10 : true`
string1 LIKE string2 | Whether string1 matches pattern string2. The wildcard '%' represents zero, one or multiple characters. The wildcard '_' represents one single character. Use 'ESCAPE' to define a escape character (if you want to escape a wildcard). To find if string1 starts with a sequence, use pattern 'sequence%' (analogously use '%sequence' for ends with). The matching is case sensitive. To perform a case insensitive matching, use UPPER (or LOWER) on string1 and pattern. `SELECT name FROM employee WHERE name LIKE 'S%' : Smith, Saunders` `SELECT name FROM employee WHERE UPPER(name) LIKE 'SM%' : Smith` `SELECT name FROM employee WHERE name LIKE '_m%' : Smith` `SELECT 'abcd%' LIKE '%!%' ESCAPE '!' : true`
string1 NOT LIKE string2 | Whether string1 does not match pattern string2. The wildcard '%' represents zero, one or multiple characters. The wildcard '_' represents one single character. Use 'ESCAPE' to define a escape character (if you want to escape a wildcard). To find if string1 does not start with a sequence, use pattern 'sequence%' (analogously use '%sequence' for ends with). The matching is case sensitive. To perform a case insensitive matching, use UPPER (or LOWER) on string1 and pattern. `SELECT name FROM employee WHERE name NOT LIKE 'S%' : Hamilton` `SELECT name FROM employee WHERE UPPER(name) NOT LIKE 'SM%' : Hamilton, Saunders` `SELECT name FROM employee WHERE name NOT LIKE '_m%' : Hamilton, Saunders` `SELECT 'abcd%' NOT LIKE '%!_' ESCAPE '!' : true`
value IN (value [, value]*) | Whether value is equal to a value in a list `SELECT firstname FROM employee WHERE name IN ('Smith', 'Hamilton') : John, Maria`
value NOT IN (value [, value]*) | Whether value is not equal to every value in a list `SELECT firstname FROM employee WHERE name NOT IN ('Smith', 'Hamilton') : Jennifer`
value IN (sub-query) | Whether value is equal to a row returned by sub-query `SELECT e.firstname FROM employee e WHERE e.department.id IN (SELECT d.id FROM department d WHERE d.name='IT') : John`

Documentation > Developer Guide > SQL in EBX®