Skip to main content
ExLibris

Knowledge Assistant

BETA
 
  • Subscribe by RSS
  • Back
    Voyager

     

    Ex Libris Knowledge Center
    1. Search site
      Go back to previous article
      1. Sign in
        • Sign in
        • Forgot password
    1. Home
    2. Voyager
    3. Knowledge Articles
    4. Commonly used SQL operators in Voyager Prepackaged Access Reports

    Commonly used SQL operators in Voyager Prepackaged Access Reports

    1. Last updated
    2. Save as PDF
    3. Share
      1. Share
      2. Tweet
      3. Share
    1. Question
    2. Answer
      1. Like
      2. Is Null
      3. <>
      4. In( ) condition
      5. Mid( ) function
    3. Additional Information
    • Product: Voyager
    • Relevant for Installation Type: Multi-Tenant Direct, Dedicated-Direct, Local, TotalCare

     

    Question

    Some commonly used SQL operators in Voyager Prepackaged Access Reports, with a few usage examples.

    Answer

    An operator is a sign or symbol that specifies the type of calculation to perform within an expression.  Access supports a variety of operators.  The following provides information about some of the most commonly used operators in Prepackaged Access Reports.  For a "Table of operators" see the Microsoft website: https://support.microsoft.com/en-us/office/table-of-operators-e1bc04d5-8b76-429f-a252-e9223117d6bd

    Having trouble seeing a long BLOB function or other lengthy expression?  Put your cursor in the cell you want to view and hit Shift-F2.  This opens the contents of the cell in an Access zoom box.

    Like

    •Wildcard character(s)

    clipboard_e2190a544563aea5b8d77342dfa1d95f0.png

    The above query does pattern matching on the Link field. An asterisk matches any sequence of zero or more characters.

    Is Null

    •Unknown/nonexistent values (NOT zero! NOT blank!)

    •See also: Use of NULL value in Voyager Prepackaged Access Reports

    •Not used for BLOB queries, which return string values; in BLOB queries, use "" to find empty string values

    clipboard_ed5dc53f9569135bf35e37f9644c49c7c.png

    The above query is an example of an LC Class Shelf List.  In addition to showing use of "Is Null" it also shows the use of a subquery and the "Between" operator.

    <>

    Not equal to

    clipboard_eadb648484311f270fbf431c911aa760a.png

    The above query excludes closed patterns (PREDICT field value of "S").

    The Not operator can also be used (e.g. Not "S")

    In( ) condition

    •Reduces need for multiple OR conditions in a SELECT statement

    •Example: In("30","21","6")


    clipboard_e859014d830536e5aad44cd8698365b92.png

    Note that the above query is also an example where we join fields with different names.

    Mid( ) function

    •Extracts a substring from a string

    •When using Mid() add one (1) to the byte position (MARC standard starts counting from zero, but MS Access begins counting from one).

    •See also: Viewing the added and built-in Voyager Prepackaged Access Reports functions

    •Example: Mid(NETWORK_NUMBER,8,Len(NETWORK_NUMBER)-7)

    clipboard_e22d7f4391ff8b1eb9c43ef59763a4b4b.png

    The above query finds records where the 008/23 - Form of Item - has no value specified:

    clipboard_e30c0f27b98600f1eef7d98e424f3dbf9.png

    It also shows using Leader/07 ("BIB_FORMAT")

    clipboard_edf95edf4966c0cb3d2847567335e52a9.png

    Additional Information

    Constructing custom SQL queries by request and troubleshooting unexpected results from customer-created SQL queries falls outside the scope of Support. The above has been posted for informational purposes.  Voyager-L and Developer Network are useful resources for finding helpful custom SQL or obtaining assistance from peers in troubleshooting custom queries.

     


    • Article last edited: 21-Jun-2021
    View article in the Exlibris Knowledge Center
    1. Back to top
      • Commonly used SQL Keywords in Voyager Prepackaged Access Reports
      • Comparison of command-line options for Voyager .msi and .exe "silent" installation
    • Was this article helpful?

    Recommended articles

    1. Article type
      Topic
      Language
      English
      Product
      Voyager
    2. Tags
      This page has no tags.
    1. © Copyright 2025 Ex Libris Knowledge Center
    2. Powered by CXone Expert ®
    • Term of Use
    • Privacy Policy
    • Contact Us
    2025 Ex Libris. All rights reserved