IFNULL function is the same as COALESCE function, but it accepts only 2 arguments. Notice that this function has to be enclosed into curly brackets. SELECT { fn IFNULL( 77, 82 ) }, { fn IFNULL( null, 82 ) }, { fn IFNULL( null, null ) };
NULLIF function accepts two arguments. If those two arguments are the same, NULL will be returned. If they are different, the first argument will be returned. SELECT NULLIF( 77,77 ), NULLIF( 'xx', 'yy' );
With ISNULL function, we can test whether some value is NULL or not.
SELECT ISNULL( null ), ISNULL( 77 );
BEETWEN Operator and Function
Between Operator
With BETWEEN operator, we can test whether some value belongs to some range. SELECT 2 BETWEEN 1 AND 3, 2 NOT BETWEEN 1 AND 3;
BETWEEN will work with alphabetical ranges, too. SELECT 'bb' BETWEEN 'aa' AND 'cc', 'bb' NOT BETWEEN 'aa' AND 'cc';
The BETWEEN operator is inclusive. The logic behind BETWEEN is 1 >= 1 AND 1 <= 2. Therefore, the first expression below will return TRUE: SELECT 1 BETWEEN 1 AND 2, 'bb' BETWEEN 'cc' AND 'aa'; In the second expression, we should have written " aa AND cc", so we reversed our boundaries. Therefore, we will get FALSE as a result. The smaller value should go first, then the larger one.
If we don't know in advanced which boundary is smaller and which is larger, then we should sort our boundaries before creating our range. BETWEEN operator can help us with that. We just have to enhance BETWEEN operator with the SYMMETRIC keyword.
SELECT 'bb' BETWEEN SYMMETRIC 'cc' AND 'aa', 'bb' NOT BETWEEN SYMMETRIC 'cc' AND 'aa';
Between Function
Between is a function, which gives us more control than the BETWEEN operator. This function has 8 arguments. On the MonetDB website, this function is poorly explained. I will explain all of its arguments except the boolean1 argument. I don't know what this argument does. "between"(arg_1 any, arg_2 any, arg_3 any, boolean1, boolean2, boolean3, boolean4, boolean5)
In its basic form, this function will check if argument1 is between arguments 2 and 3. Boundaries are not inclusive. SELECT "between"( 1, 1, 3, false, false, false, false, false );
We can make this function inclusive if we change the last argument (boolean5) to TRUE. SELECT "between"( 1, 1, 3, false, false, false, false, true );
We can also make this function partially inclusive. Argument "boolean2" can turn on inclusivity, but only for the lower boundary.
If the first argument is "null", the result of the BETWEEN function will be "null". If we change the fourth argument to TRUE, then the result will be FALSE. SELECT "between"( null, 1, 3, false, false, false, false, false ), "between"( null, 1, 3, false, false, false, true, false );
Functions LIKE and NOT_LIKE
LIKE function is matching a string with a pattern. If they do match, TRUE will be returned. Wildcard % will replace 0 or many characters. "_" will replace only one character. SELECT "like"( 'abc', 'ab%', '#', false), "like"( 'abc', 'a__', '#', false)
If we need a pattern that contains % and _ characters, then we have to escape them. Third argument is used as an escape sign. SELECT "like"( 'ab%', 'ab#%', '#', false), "like"( 'abc', 'ABC', '#', true); If we want to make our pattern case insensitive, then we have to change the fourth argument to true.
The opposite function of "like" function is not_like. This example would return FALSE. SELECT not_like('abc', 'ab%', '#', false);
Function IfThenElse
IfThenElse function will give us ability to conduct ternary logic. If the first argument expression is TRUE, we will return the second argument. Otherwise, we will return the third argument.
SELECT IfThenElse( 2 > 1, 'Two is larger than 1.', 'Two is smaller than 1.');
