Operators and Literals

Numerical Operators

(op_plus)=

+

Addition

> SELECT 1 + 2;
+---------------------+
| Int64(1) + Int64(2) |
+---------------------+
| 3                   |
+---------------------+

(op_minus)=

-

Subtraction

> SELECT 4 - 3;
+---------------------+
| Int64(4) - Int64(3) |
+---------------------+
| 1                   |
+---------------------+

(op_multiply)=

*

Multiplication

> SELECT 2 * 3;
+---------------------+
| Int64(2) * Int64(3) |
+---------------------+
| 6                   |
+---------------------+

(op_divide)=

/

Division (integer division truncates toward zero)

> SELECT 8 / 4;
+---------------------+
| Int64(8) / Int64(4) |
+---------------------+
| 2                   |
+---------------------+

(op_modulo)=

%

Modulo (remainder)

> SELECT 7 % 3;
+---------------------+
| Int64(7) % Int64(3) |
+---------------------+
| 1                   |
+---------------------+

Comparison Operators

(op_eq)=

=

Equal

> SELECT 1 = 1;
+---------------------+
| Int64(1) = Int64(1) |
+---------------------+
| true                |
+---------------------+

(op_neq)=

!=

Not Equal

> SELECT 1 != 2;
+----------------------+
| Int64(1) != Int64(2) |
+----------------------+
| true                 |
+----------------------+

(op_lt)=

<

Less Than

> SELECT 3 < 4;
+---------------------+
| Int64(3) < Int64(4) |
+---------------------+
| true                |
+---------------------+

(op_le)=

<=

Less Than or Equal To

> SELECT 3 <= 3;
+----------------------+
| Int64(3) <= Int64(3) |
+----------------------+
| true                 |
+----------------------+

(op_gt)=

>

Greater Than

> SELECT 6 > 5;
+---------------------+
| Int64(6) > Int64(5) |
+---------------------+
| true                |
+---------------------+

(op_ge)=

>=

Greater Than or Equal To

> SELECT 5 >= 5;
+----------------------+
| Int64(5) >= Int64(5) |
+----------------------+
| true                 |
+----------------------+

(op_spaceship)=

<=>

Three-way comparison operator. A NULL-safe operator that returns true if both operands are equal or both are NULL, false otherwise.

> SELECT NULL <=> NULL;
+--------------------------------+
| NULL IS NOT DISTINCT FROM NULL |
+--------------------------------+
| true                           |
+--------------------------------+

> SELECT 1 <=> NULL;
+------------------------------------+
| Int64(1) IS NOT DISTINCT FROM NULL |
+------------------------------------+
| false                              |
+------------------------------------+

> SELECT 1 <=> 2;
+----------------------------------------+
| Int64(1) IS NOT DISTINCT FROM Int64(2) |
+----------------------------------------+
| false                                  |
+----------------------------------------+

> SELECT 1 <=> 1;
+----------------------------------------+
| Int64(1) IS NOT DISTINCT FROM Int64(1) |
+----------------------------------------+
| true                                   |
+----------------------------------------+

IS DISTINCT FROM

Guarantees the result of a comparison is true or false and not an empty set

> SELECT 0 IS DISTINCT FROM NULL;
+--------------------------------+
| Int64(0) IS DISTINCT FROM NULL |
+--------------------------------+
| true                           |
+--------------------------------+

IS NOT DISTINCT FROM

The negation of IS DISTINCT FROM

> SELECT NULL IS NOT DISTINCT FROM NULL;
+--------------------------------+
| NULL IS NOT DISTINCT FROM NULL |
+--------------------------------+
| true                           |
+--------------------------------+

(op_re_match)=

~

Regex Match

> SELECT 'tap' ~ '^tap(-cli)*';
+-------------------------------------------------+
| Utf8("tap") ~ Utf8("^tap(-cli)*") |
+-------------------------------------------------+
| true                                            |
+-------------------------------------------------+

(op_re_match_i)=

~*

Regex Case-Insensitive Match

> SELECT 'tap' ~* '^TAP(-cli)*';
+--------------------------------------------------+
| Utf8("tap") ~* Utf8("^TAP(-cli)*") |
+--------------------------------------------------+
| true                                             |
+--------------------------------------------------+

(op_re_not_match)=

!~

Not Regex Match

> SELECT 'tap' !~ '^TAP(-cli)*';
+--------------------------------------------------+
| Utf8("tap") !~ Utf8("^TAP(-cli)*") |
+--------------------------------------------------+
| true                                             |
+--------------------------------------------------+

(op_re_not_match_i)=

!~*

Not Regex Case-Insensitive Match

> SELECT 'tap' !~* '^TAP(-cli)+';
+---------------------------------------------------+
| Utf8("tap") !~* Utf8("^TAP(-cli)+") |
+---------------------------------------------------+
| true                                              |
+---------------------------------------------------+

~~

Like Match

SELECT 'tap' ~~ 't_%p';
+---------------------------------------+
| Utf8("tap") ~~ Utf8("t_%p") |
+---------------------------------------+
| true                                  |
+---------------------------------------+

~~*

Case-Insensitive Like Match

SELECT 'tap' ~~* 'T_%p';
+----------------------------------------+
| Utf8("tap") ~~* Utf8("T_%p") |
+----------------------------------------+
| true                                   |
+----------------------------------------+

!~~

Not Like Match

SELECT 'tap' !~~ 'T_%p';
+----------------------------------------+
| Utf8("tap") !~~ Utf8("T_%p") |
+----------------------------------------+
| true                                   |
+----------------------------------------+

!~~*

Not Case-Insensitive Like Match

SELECT 'tap' !~~* 'T_%p';
+-----------------------------------------+
| Utf8("tap") !~~* Utf8("T_%p") |
+-----------------------------------------+
| true                                    |
+-----------------------------------------+

Logical Operators

AND

Logical And

> SELECT true AND true;
+---------------------------------+
| Boolean(true) AND Boolean(true) |
+---------------------------------+
| true                            |
+---------------------------------+

OR

Logical Or

> SELECT false OR true;
+---------------------------------+
| Boolean(false) OR Boolean(true) |
+---------------------------------+
| true                            |
+---------------------------------+

Bitwise Operators

(op_bit_and)=

&

Bitwise And

> SELECT 5 & 3;
+---------------------+
| Int64(5) & Int64(3) |
+---------------------+
| 1                   |
+---------------------+

(op_bit_or)=

|

Bitwise Or

> SELECT 5 | 3;
+---------------------+
| Int64(5) | Int64(3) |
+---------------------+
| 7                   |
+---------------------+

(op_bit_xor)=

#

Bitwise Xor (interchangeable with ^)

> SELECT 5 # 3;
+---------------------+
| Int64(5) # Int64(3) |
+---------------------+
| 6                   |
+---------------------+

(op_shift_r)=

>>

Bitwise Shift Right

> SELECT 5 >> 3;
+----------------------+
| Int64(5) >> Int64(3) |
+----------------------+
| 0                    |
+----------------------+

(op_shift_l)=

<<

Bitwise Shift Left

> SELECT 5 << 3;
+----------------------+
| Int64(5) << Int64(3) |
+----------------------+
| 40                   |
+----------------------+

Other Operators

(op_str_cat)=

||

String Concatenation

> SELECT 'Hello, ' || 'tap!';
+---------------------------------+
| Utf8("Hello, ") || Utf8("tap!") |
+---------------------------------+
| Hello, tap!                     |
+---------------------------------+

(op_arr_contains)=

@>

Array Contains

> SELECT make_array(1,2,3) @> make_array(1,3);
+-------------------------------------------------------------------------+
| make_array(Int64(1),Int64(2),Int64(3)) @> make_array(Int64(1),Int64(3)) |
+-------------------------------------------------------------------------+
| true                                                                    |
+-------------------------------------------------------------------------+

(op_arr_contained_by)=

<@

Array Is Contained By

> SELECT make_array(1,3) <@ make_array(1,2,3);
+-------------------------------------------------------------------------+
| make_array(Int64(1),Int64(3)) <@ make_array(Int64(1),Int64(2),Int64(3)) |
+-------------------------------------------------------------------------+
| true                                                                    |
+-------------------------------------------------------------------------+

Literals

Use single quotes for literal values. For example, the string foo bar is referred to using 'foo bar'

select 'foo';

Escaping

Unlike many other languages, SQL literals do not by default support C-style escape sequences such as \n for newline. Instead all characters in a ' string are treated literally.

To escape ' in SQL literals, use '':

> select 'it''s escaped';
+----------------------+
| Utf8("it's escaped") |
+----------------------+
| it's escaped         |
+----------------------+
1 row(s) fetched.

Strings such as foo\nbar mean \ followed by n (not newline):

> select 'foo\nbar';
+------------------+
| Utf8("foo\nbar") |
+------------------+
| foo\nbar         |
+------------------+
1 row(s) fetched.
Elapsed 0.005 seconds.

To add escaped characters such as newline or tab, instead of \n you use the E style strings. For example, to add the text with a newline

foo
bar

You can use E'foo\nbar'

> select E'foo\nbar';
+-----------------+
| Utf8("foo
bar") |
+-----------------+
| foo
bar         |
+-----------------+

Looks good? Missing something you need? We need your feedback.

Let us know if you need to get your data and applications talking, or you want to save money on complex, inefficient data platforms and integration tooling. We'd love to discuss and see how tap could help.

tap is built by DigitalSociety – we build bespoke digital tools for complex requirements in web applications, data engineering and cloud.