Case-insensitive comparison of a character field

The case of letters can cause a problem when you compare character values. You might not know the case of individual characters in the field values that you want to select. For example, you want to select the company named “Acme Rentals.” There is only one instance of the Company entity type with that name. However, you do not know whether the company name in the database is “Acme Rentals,” “ACME RENTALS,” or even “acme rentals.”

SQL WHERE clauses let you apply case-insensitive comparisons with functions that convert values in columns to upper or lower case before making a comparison. The following example SQL statement converts values in the name column to lower case before applying the predicate = "acme rentals".

SELECT * from companies
  WHERE LCASE(name) = "acme rentals";

The query builder APIs provide the compareIgnoreCase method for case-insensitive comparisons, as the following Gosu code shows.

uses gw.api.database.Query 

var query = Query.make(Company)
query.compareIgnoreCase(Company#Name, Equals, "Acme Rentals")

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

See also

• Linguistic search and sort