Advanced Expressions

The Advanced Expression window allows you to use SQL code to create a search expression to extract specific respondents. Note that you should have some knowledge and experience of SQL to use this window. To create an advanced expression:

1. In the Edit window, click Advanced Expression....
2. Click into the text field and type the SQL code for the expression.
3. On completion, click OK.

The following are some examples of the SQL code you could use to achieve the stated results. The syntax used is SQL. SQL reserved keywords are written in upper case, and % = wildcard.

Desired Result	Example of Code
Display all respids with a blank email address	email IS NULL
Display all respids that don’t have a blank email address	email IS NOT NULL
Display all respids with an email address that starts with ‘as’	email LIKE 'as%'
Display all respids with an email address that doesn’t end with somecompany.com	email NOT LIKE '%somecompany.com'
Display all respids where noOfEmailsSent is 0	noOfEmailsSent = 0 or noOfEmailsSent LIKE 0
Display all respids where noOfEmailsSent is not 0	noOfEmailsSent != 0 or noOfEmailsSent NOT LIKE 0
Display all respids where noOfEmailsSent greater than 0	noOfEmailsSent > 0
Display all respids where noOfEmailsSent less than 2	noOfEmailsSent < 2
Display all respids where noOfEmailsSent is greater than 0 and less than 5 (1,2,3,4)	noOfEmailsSent > 0 AND noOfEmailsSent < 5
Display all respids where noOfEmailsSent between 1 and 4 (1,2,3,4)	noOfEmailsSent BETWEEN 1 AND 4
Display all respids where noOfEmailsSent is 0 or 1	noOfEmailsSent IN (0,1)

It is also possible to combine several statements, for example:

Desired Result	Example of Code
Displays all respids where email does not end with somecompany.com or email2 is blank and where noOfEmailsSent is 1,2 or 3.	((email NOT LIKE '% somecompany.com') OR (email2 IS NULL)) AND (noOfEmailsSent BETWEEN 0 AND 3)

The system field Status is not visible in the Respondents Edit view, but it is still possible to reference it in an Advanced Expression. The appropriate expression is dependent on whether the survey is hosted on a SaaS or Cloud site:

Status	SaaS Site Expression	Cloud Site Expression
Not Answered	respid NOT IN (SELECT respid FROM response_control)	respid NOT IN (SELECT respid FROM survey_pNNNNNNNN.response_control)
Completes	respid IN (SELECT respid FROM response_control WHERE status = 'complete')	respid IN (SELECT respid FROM survey_pNNNNNNNN.response_control WHERE status = 'complete')
Screened	respid IN (SELECT respid FROM response_control WHERE status = 'screened')	respid IN (SELECT respid FROM survey_pNNNNNNNN.response_control WHERE status = 'screened')
Quotafull	respid IN (SELECT respid FROM response_control WHERE status = 'quotafull')	respid IN (SELECT respid FROM survey_pNNNNNNNN.response_control WHERE status = 'quotafull')
Incomplete	respid IN (SELECT respid FROM response_control WHERE status IS NULL)	respid IN (SELECT respid FROM survey_pNNNNNNNN.response_control WHERE status IS NULL)