Search with a Select Statement

You can find data in any field with a Select Statement, including those unavailable in the Search Fields section of Advanced Search.

Overview

A Select Statement allows you to search fields that are not indexed. If you can't find the field you want to search in the list of available fields in Advanced Search, then it is a non-indexed field. You can still search this field, you just have to use a Select Statement.

Examples of non-indexed fields in the Object file:

  • Registrar Notes*

  • Treatment Description*

  • Researcher Notes*

  • Deaccession Method

  • Acquisition Funder type

You can also search this way on indexed fields. It could be that you need a bit more of a complex search, for example you want to search for an exact phrase used in a text field.

* These fields are indexed in the All Text index, but it is simpler to search them directly using a Select Statement.

Steps

  1. Click Advanced on the toolbar to open Advanced Search.

  2. Choose Select Statement as the search method.

  3. Click into the Select Statement field, enter your Select Statement, and press Enter. See information about format, components, and examples further down on this page.

  4. Click Next and then click Finish to see your results.

Video

Select Statement format

Select Statements have up to 5 parts.

Example: SELECT OBJECT WITH PROD_PRI_PERSON = ”Jane Smith”

Part Example Notes
File to select SELECT OBJECT You don’t need to enter this part. It’s automatically entered by the system when you press Enter after typing the other parts of the statement.
Keyword WITH Other keywords are listed below.
Internal field name PROD_PRI_PERSON

For more information, see Get an internal field name or Field Mark Count (FMC).

You can also click Options on the toolbar to select from a list of internal field names.
Comparison operator = Other comparison operators are listed below.
Data to search for "Jane Smith"  

Select Statement components

Keywords

Keyword Function
WITH Finds records containing the specified data.
WITHOUT Finds records that don’t contain the specified data.
CASESENS Forces a case-sensitive search of the specified data.

Comparison operators

Comparison operator Function Notes
= Equal to  
< Less than  
<> Not equal to  
> Greater than  
] Starts with  
[ Ends with  
[] Containing You can write CONTAINING instead of [].

Example 1: Search for a presence or absence of data

Find objects with data in a field

SELECT OBJECT WITH DEPARTMENT

This statement will find Object records that contain data in the Department field.

Find objects without data in a field

SELECT OBJECT WITHOUT DEPARTMENT

This statement will find Object records that don’t contain data in the Department field.

Example 2: Search for data within a field

Find objects that contain a word

SELECT OBJECT WITH MATERIAL_DESC [] "SILVER"

or:

SELECT OBJECT WITH MATERIAL_DESC CONTAINING "SILVER"

Either of these statements will find Object records that contain the word silver in the Medium/Material Description field.

Find objects that contain a phrase in a text field

SELECT OBJECT WITH BRIEF_DESC [] "WOODEN FIGURE"

or:

SELECT OBJECT WITH BRIEF_DESC CONTAINING "WOODEN FIGURE"

Either of these statements will find Object records that contain the phrase wooden figure in the Brief Description field, and will exclude records that simply contain both the words wooden and figure.

Example 3: Search on date fields

Find objects classified between certain dates

SELECT OBJECT WITH CLASSIFICATION_DATE FROM "01 Jun 2015" TO "30 Jun 2015"

This statement will find Object records where the Classification Date is between 1 and 30 June 2015.

Find objects with a current location date

SELECT OBJECT WITH CURR_LOC_DATE = "6/12/21"

This statement will find Object records with a Current Location Date of 6/12/21.

Find objects with an earlier current location date

SELECT OBJECT WITH CURR_LOC_DATE < "6/12/21"

This statement will find Object records with a Current Location Date earlier than 6/12/21.

Find objects with any current location date other than that specified

SELECT OBJECT WITH CURR_LOC_DATE <> "6/12/21"

This statement will find Object records with any Current Location Date other than 6/12/21.

Example 4: Search with case sensitivity

Find data with any case (sentence case, lower case, and all caps)

SELECT PERSON WITH LAST_NAME = "Smith"

This statement will find Person records with Smith, SMITH or smith in the Last Name field.

Find data using a specific case (for example, sentence case)

SELECT PERSON WITH CASESENS LAST_NAME = "Smith"

This statement will find Person records with Smith in the Last Name field. SMITH and smith will not be included.