Comparison of a character field to a range of values

In SQL, you use the BETWEEN keyword to apply comparison predicates to an inclusive range of column values in the WHERE clause. For example, the following SQL statement applies the predicate BETWEEN "Bank" AND "Business" to values in the name column of the companies table. The query returns all companies with names between the values “Bank” and “Business,” including any company that has the name “Bank” or the name “Business.”

SELECT * from companies
  WHERE name BETWEEN "Bank" AND "Business";

With the query builder APIs, you use the between method to apply a range predicate to entity fields. The following Gosu code applies the between method to values in the Name field of Company entity instances to select companies with names between the values “Bank” and “Business.”

uses gw.api.database.Query 

// Query the Company instances for a range of company names. 
var query = Query.make(Company)
query.between(Company#Name, "Bank", "Business")

// Fetch the data and print it.
var result = query.select()
for (company in result) {
  print (company.Name)
}

The between method performs a case-sensitive comparison of the values in the character field to the comparison values. For example, the preceding query builder code selects only “Building Renovators,” but not “building renovators.”

Note: In configurations that use primary or secondary linguistic sort strength, the preceding query builder code does not perform a case-sensitive comparison. If either of your PolicyCenter or the relational database that your PolicyCenter uses have these configurations, the result includes both “Building Renovators” and “building renovators.”

To perform a case-insensitive comparison on a range of values for a character field, use the compareIgnoreCase method, as the following Gosu code shows.

uses gw.api.database.Query 

// Query the Company instances for a range of company names. 
var query = Query.make(Company)
query.compareIgnoreCase(Company#Name, GreaterThanOrEquals, "bank")
query.compareIgnoreCase(Company#Name, LessThanOrEquals, "business")

// Fetch the data and print it.
var result = query.select()
for (company in result) {
  print (company.Name)
}