SQL operators

This list includes operators currently supported by Network Reporting.

and

Logical operator; conjunction.

expression AND expression

SELECT vid__v FROM hcp WHERE primary_country__v = 'US' AND hcp_type__v = 'P'

between

Test expressions for inclusion in a range of values, using the keywords BETWEEN and AND.

BETWEEN expression AND expression

SELECT vid__v, birth_year__v FROM hcp WHERE birth_year__v BETWEEN '1960' AND '1980'

exists

The argument of EXISTS is an arbitrary SELECT statement, or subquery. The subquery is evaluated to determine whether it returns any rows. If it returns at least one row, the result of EXISTS is True; if the subquery returns no rows, the result of EXISTS is False.

[ NOT ] EXISTS (table_subquery)

SELECT formatted_name__v FROM hcp WHERE EXISTS (SELECT 1 FROM parenthco WHERE parenthco.entity_vid__v = hcp.vid__v)

explode

Performs an aggregate function on groupings of numbered fields (for example, 

specialty_1__v
, 
specialty_2__v
, through 
specialty_10__v
).

For more information, see Groups of fields.

ilike

Performs a case-insensitive pattern match for single-byte or multibyte characters. 

expression [NOT] ILIKE pattern

SELECT formatted_name__v, hcp_type__v FROM hcp WHERE formatted_name__v ILIKE 'doctor%'

Input

Doctor Bob Smith

Result

Doctor Bob Smith

in

IN: True if the expression on the left is found in the expression list or subquery. NOT IN: True if the expression on the left is not found in the expression list or subquery.

expression [NOT] IN (expression_list | table_subquery)

SELECT vid__v FROM hcp WHERE vid__v IN (SELECT vid__v FROM hcp INNER JOIN address ON hcp.vid__v = address.entity_vid__v WHERE country__v = 'US'); SELECT vid__v FROM hcp WHERE hcp_type__v IN ('D', 'P', 'DT') AND primary_country__v NOT IN ('US', 'CA');

is

Logical operator; logical assertion.

expression IS [NOT] value

SELECT vid__v FROM hco WHERE medical_degree_1__v IS NOT NULL AND medical_degree_2__v IS NOT NULL AND (candidate_record__v <> T OR candidate_record__v IS NULL)

like

LIKE performs a case-sensitive pattern match for single-byte characters. Compares a string expression with a pattern that can use wildcard characters.

expression [NOT] LIKE pattern

SELECT formatted_name__v, hcp_type__v FROM hcp WHERE formatted_name__v LIKE 'doctor%'

Input

doctor Bob Smith

Result

doctor Bob Smith

or

Logical operator; disjunction.

OR

SELECT vid__v FROM hcp WHERE primary_country__v = 'US' OR primary_country__v = 'CA'