Using Relational Operators

When you create a review file or append records to an existing review file, you use relational operators to specify the search criteria for the records. The following operators are available:

=	equal to
!=	not equal to
>	greater than
<	less than
>=	greater than or equal to
<=	less than or equal to
H	has
A	All fields don't have
O	At least one field doesn't have
W	between
N	not within
I	in
^	starts with
$	ends with
e	exist
n	not exist
t	equals today
y	equals yesterday
v	within last week
m	within last month
a	is this many days ago
b	is this many weeks ago
c	is this many months ago
R	matches (regular expression)
x	has (exact)
s	starts with (exact)
z	ends with (exact)
j	matches (exact)

= (equal to)

The entire normalized field, including all subfields, must exactly match the characters entered.

Use for fixed-length fields - This relational operator is useful for searching in fixed-length fields. However, it is not recommended for searching in variable-length fields, since any difference between the search statement and the data in the field causes the system to find no match. For example, a search for "AUTHOR = Cartland Barbara" does not retrieve a record containing the MARC 100 field "Cartland, Barbara,|d1902-", since the search statement did not include subfield "d". Use the "has" operator for searching variable-length fields.

Use to locate item records that contain a specific course record number - When you choose to search on the /6 COURSE ID field, use this relational operator to designate that items retrieved must contain the course record number you specify.

Use to locate records that do not contain a particular variable-length field - You can use this relational operator to retrieve records that lack a particular variable-length field. Select this operator, and do not specify any characters after the equal sign. For example, the search for "AUTHOR=" retrieves all records that do not contain an author field. Think of this as a search for "author equals nothing", which retrieves all records with no authors.

!= (not equal to)

The normalized field does not exactly match the characters entered, that is, it has characters other than or in addition to the specified characters.

Use for fixed-length fields - This relational operator is useful for searching fixed-length fields. For example, a search for "LOCATION != multi" retrieves all records that do not have multiple locations. Similarly, the search "LOCATION != main" retrieves records that do not have a single location of "main". However, it also retrieves records that have a location of "multi" with "main" being one of the multiple locations. To find all records that do not have "main" as a location, use the All fields do not have operator. You can search for records that have a single location of "main" using the search "LOCATION != multi AND LOCATION = main".

This operator is not recommended for searching variable-length fields. If you use this operator alone to search variable-length fields, the system can retrieve virtually the entire database. For example, a search for "AUTHOR != Cartland Barbara" retrieves a record containing the MARC 100 field "Cartland, Barbara,|d1902-", because subfield 'd' was not included in the search statement and therefore the two do not match exactly. This search also retrieves every other record in the database that does not have a MARC 100 field that exactly matches "Cartland Barbara". Even when you use it in combination with other more restrictive conditions, it is not an effective search tool for variable-length fields, since a single difference between the search statement and the data in the field can cause the system to retrieve an undesired record.

Use to locate records that do contain a particular variable-length field - You can use this relational operator to retrieve records that contain a particular variable-length field. Select this operator, and do not specify any characters after "!=". For example, the search for "AUTHOR !=" retrieves all records that do contain an author field. Think of this as a search for "author not equal to nothing", which retrieves all records with an author.

> (greater than)
< (less than)

These relational operators perform a character-by-character comparison between the search statement and the data in the specified field, starting with the left-most character in each. The system stops the comparison when the first non-matching character is reached. For the "greater than" operator, the system retrieves a record if the ASCII value of the first non-matching character is greater in the record than in the search statement. For the "less than" operator, the opposite is true. If the search statement and the data from the field are exactly equal, the record is not retrieved. For example, if a copy on order has an estimated price of $51, a search for "ESTIMATED PRICE < 50" does not retrieve that record, whereas a search for "ESTIMATED PRICE > 50" does retrieve it. Neither search retrieves a copy on order with an estimated price of $50.

If all characters in a search statement match the data in a record, but the search statement is shorter, the system considers the data in the record to be "greater than" the search statement. Thus, to search for all authors whose name begins with 'a', 'b', or 'c', enter the search statement "AUTHOR < d". Likewise, to find all authors whose name begins with 'm', enter the search "AUTHOR > m AND AUTHOR < n".

>= (greater than or equal to)
<= (less than or equal to)

These are the same as "greater than" and "less than" described previously, except that exact matches also are retrieved. For example, if a copy on order has an estimated price of $50, a search for "ESTIMATED PRICE >= 50" retrieves that record, as does a search for "ESTIMATED PRICE <= 50".

H (has)

The system retrieves a record if the characters entered are contained, as a group, anywhere in any subfield of the specified field in the record. The entire group of characters must match exactly, including spaces, punctuation marks, and subfield delimiters. The field can contain additional characters. The system examines the entire field, that is, you cannot specify a particular subfield.

Use for Variable-length Fields

Use this operator to find the entered characters anywhere in any subfield in the specified field. For example, searching for "AUTHOR has land" retrieves the MARC 100 fields "Landman, Paul", "Hollander, John", "Guildhall School (England)", and so forth. This is the recommended method for searching variable-length fields. Note that the search for "AUTHOR has Cartland Barbara" does not retrieve a record with the MARC 100 field "Cartland, Barbara", because the comma does not appear in the search statement.

Field Normalization

Both the search statement and the data from the record are normalized by making all characters lowercase (using PostgreSQL's lower() function for the "C" locale). This function only makes ASCII characters lowercase. To avoid unexpected results, use the x - has (exact) operator if your search input contains non-ASCII characters (for example, the word "Àsia").

Additionally, the system leaves all punctuation marks, spaces, subfield tags, and subfield delimiters such as '|b', as they are. For example, if you use this operator to search for bibliographic records with the string "blee" in the Title field, the resulting review file can include a record with a Title field like the following:

245 10 Chancellorsville;|bLee's greatest battle

To avoid this situation, use the R (matches - Regular Expressions) operator instead of the "has" operator. See About Subfield Tags.

A (All fields don't have)

This operator retrieves records that do not have the specified character or string of characters in the given field or records that do not contain the given field at all.

O (At least one field does not have)

This operator retrieves records that contain the given field, provided that at least one occurrence of the given field does not have the specified character or string of characters.

W (between)

Use this operator to find a value between two specified values (entered values are included in the range). This operator usually is used for dates or amounts, for example, "ESTIMATED PRICE between 250 & 500" locates records with estimated prices between $250 and $500 (inclusive).

N (not within)

Use this operator to find a value that is not between two specified values (entered values are included in the range). This operator usually is used for dates or amounts, for example, "CATDATE not within 01/01/93 & 12/31/93" excludes all items cataloged during 1993.

^ (starts with)

Use this operator to find variable-length fields in which a given word or phrase appears at the beginning of the field. The comparison between the word or phrase you enter and the beginning of the field is case insensitive, thus "Journal of" and "journal of" produce the same results. This operator is not supported for fixed-length fields.

$ (ends with)

Use this operator to find variable-length fields in which a given word or phrase appears at the end of the field. The comparison is case insensitive. Punctuation is included as part of the comparison, so if you enter "cultural studies" and a field ends with "cultural studies." (note the period), then the field would not match. This operator is not supported for fixed-length fields.

e (exist)

This operator identifies date fields, such as CAT DATE or LCHKIN, that have content (date entries).

n (not exist)

This operator identifies blank (null) date fields.

t (equals today)

Retrieves records whose specified DATE field is equal to the day the search is run.

y (equals yesterday)

Retrieves records with a specified DATE field equal to the day before the search is run.

v (within last week)

Retrieves records whose specified DATE field falls within the previous calendar week (Sunday-Saturday).

m (within last month)

Retrieves records with a specified DATE field that falls within the previous calendar month.

a (is this many days ago)

Retrieves records with a specified DATE field equal to the specified number of days before the date the search is run.

b (is this many weeks ago)

Retrieves records with a specified DATE field equal to the specified number of weeks before the date the search is run.

c (is this many months ago)

Retrieves records with a specified DATE field equal to the specified number of months before the date the search is run.

R (matches) - Regular Expressions

The 'r' operator enables you to enter a regular expression to search variable-length and many fixed-length fields in database records. Regular expressions are a powerful and complex tool that permit you to specify a pattern of characters for which to search. A regular expression can consist of ASCII characters alone or ASCII characters accompanied by special regular expression symbols.

When you search a field, the regular expression you use must appear somewhere in the field exactly as you specify. The regular expression does not need to match the entire field, as required by the = (equal to) operator. For example, the search:

TITLE matches data processing

matches records with TITLE fields containing "Modern data processing", "Data processing for beginners", and "Stretching man's mind :|ba history of data processing".

Syntax

By default, the system uses regular expression syntax compatible with POSIX Basic Regular Expressions (BREs) as implemented in the UNIX grep utility. However, you can also use Extended Regular Expressions (EREs) by preceding your query with "(?e)". For example:

   (?e)20[09]{2]$ 

For complete information, consult a reference on regular expressions.

As a component of regular expressions, the caret symbol '^' does not ignore leading |a subfield delimiters and MARC tags and indicators when checking the beginning of a field. This is in contrast to the caret symbol as a Create Lists operator. (See ^ (starts with), above.)

Normalization and Escaping Characters

When constructing your regular expressions, keep the following in mind:

• Regular expression searches are not case sensitive. Before Sierra compares your search input and the target field for matches, it makes all content lowercase (using PostgreSQL's lower() function for the "C" locale). This function only makes ASCII characters lowercase. To avoid unexpected results, use the j - matches (exact) operator if your search input contains non-ASCII characters (for example, the word "Àsia").
• Punctuation marks, spaces, and subfield delimiters (such as '|b') in the record are left as they are and included in the search unless the punctuation marks are regular expression symbols (such as square brackets [ ], asterisk *, period ., carat ^, dollar sign $, or backslash \). See Special Regular Expression Symbols below for more information. If you want one of these symbols as a regular character in your search statement (that is, not as a regular expression symbol), you must "backslash" or "escape" the symbol. To do so, precede the special symbol with the backslash '\' character, which indicates that the search algorithm should treat the special symbol as a normal punctuation mark. For example, to search for records containing the characters "[by]", use "\[by\]" as your search pattern. A simple character search of "[by]" does not match records containing the string of characters "[by]".
• Some characters are regular expression symbols in EREs but not in BREs. These include the plus symbol +, parentheses (), the pipe character |, and the question mark ?. To search for one of these as a regular character in an ERE (for example, the pipe character in a MARC field), you must escape the symbol with a backslash (\) as described above. Otherwise, the system interprets it as a special symbol.
• You can enter a braced diacritic in a regular expression, and you do not need to escape the curly braces. You only need to escape a curly brace if you are using an ERE and you want to match the literal brace character (that is, the brace character is not part of a regular expression special symbol).

Special Regular Expression Symbols

The following provides information on some of the special regular expression symbols you can use. Symbols that are only available with Extended Regular Expressions (EREs) are flagged below. In the example searches below, the matched string of characters appears in bold text.

+	(EREs only) The plus sign is a special regular expression symbol that allows you to match one or more of the preceding characters. For example, a search for: DESCRIPT matches (?e)x+i+ matches records with DESCRIPT fields containing "xi, 318 p. ;|c18 cm.", "xii, 610, [29] p.", "xix, 374 p.", and "xxii, 314 p."
*	The asterisk allows you to match zero or more of the preceding characters. For example, a search for: TITLE matches dea*f matches records with TITLE fields containing "Eating defensively" and "Deafness in infancy and early childhood".
.	The period is a special symbol that matches any single character, including letters, numbers, spaces, and punctuation marks. For example, a search for: AUTHOR matches j.n matches records with AUTHOR fields containing "Jones, Aubrey B," "Heise, Jon O.," and "Janaro, Richard Paul." You can combine the period with the plus sign or asterisk to search for more than a single character. For example: AUTHOR matches j.*n matches all of the records of the previous search as well as records containing "Couger, J. Daniel," "Diebold, John," and "Rosenberg, Jerry Martin".
[ ]	Square brackets are special regular expression symbols that enable you to define patterns that match one of a group of alternatives (a character class). For example, a search for: AUTHOR matches jo[ah]n matches records with AUTHOR fields containing "Johnson, Stephen M.," "Narciso, John,|d1924-," and "Fromer, Margot Joan,|d1939-". You can use a hyphen within square brackets to specify a range of characters, for example, "[0-9]" matches any digit and "[a-z]" matches any letter. You can also combine the square brackets with the plus sign (or the asterisk) to search for a string of characters of any length: AUTHOR matches [a-z] man matches records with AUTHOR fields containing "Maurer, Hermann A.," "Rothman, Stanley," and "Enger, Norman L". If the first character in the square brackets is a caret "^", then the search matches all characters that are not in the character class. See About Subfield Tags for an example of this.
{ }	(EREs only) Curly braces allow you to specify that a character or pattern must occur a specific number of times. For example: TITLE matches (?e)l{3} matches records with TITLE fields containing the letter "l" appearing three times in succession, such as "illlustrated". This can be useful in catching data entry errors in the database.
( )	(EREs only) Parentheses create groups that can be followed by modifiers. For example: TITLE matches (?e)(the ){2} matches records with TITLE fields containing the word "the" appearing twice in succession, such as "The the book of lists". This can be useful in catching data entry errors in the database.
?	(EREs only) The question mark allows you to match zero or one occurrence of the preceding character. For example, a search for: TITLE matches (?e)magick? for beginners matches records with TITLE fields containing either "magic for beginners" or "magick for beginners".
|	(EREs only) The pipe character allows you to search for alternative strings. For example, a search for: TITLE matches (?e)return of the (native|jedi) matches records with TITLE fields containing either "return of the jedi" or "return of the native".

You can combine these basic regular expression symbols to create very sophisticated search patterns. Consult a book on the UNIX egrep program to learn about other regular expression symbols and search techniques.

x - has (exact)

This operator is a case-sensitive version of the H (has) operator. It functions the same but does not normalize the content of your search string or target field prior to determining matches. Consequently, if you search for "AUTHOR has Patrick", Sierra would retrieve MARC 100 fields with "Patrick, Mary" and "McPatrick, John", but not "Mcpatrick, Frank".

s - starts with (exact)

This operator is a case-sensitive version of the ^ (starts with) operator. It functions the same but does not normalize the content of your search string or target field prior to determining matches. Consequently, if you search for "Journal of", Sierra would not find matches for fields beginning with the string "journal of".

z - ends with (exact)

This operator is a case-sensitive version of the $ (ends with) operator. It functions the same but does not normalize the content of your search string or target field prior to determining matches. Consequently, if you search for "Cultural Studies", Sierra would not find matches for fields ending with the string "cultural studies".

j - matches (exact) - Regular Expressions (Case-sensitive)

This operator is a case-sensitive version of the R (matches) operator. It functions the same but Sierra does not normalize the content of your search string or target field prior to determining matches.