Case-insensitive partial comparisons of a character field

Sometimes you need to make partial comparisons in a case-insensitive way. In SQL, you apply case-insensitive partial comparison predicates with functions that convert the column values to upper or lower case before making the comparison. The following SQL statement converts the values in Subject to lower case before comparing them to review.

SELECT * FROM pc_activity
  INNER JOIN pc_user 
    ON pc_user.ID = pc_activity.AssignedUserID 
  WHERE LOWER(pc_activity.Subject) LIKE "%review%"
    AND pc_user.PublicID = "pc:8";

With the query builder APIs, you use the third parameter of the startsWith and contains methods to specify whether to make the comparison in a case-insensitive way.

true – Case-insensitive comparisons
false – Case-sensitive comparisons

Important: In configurations using primary or secondary linguistic sort strength, query builder code does not perform a case-sensitive comparison even if you use a value of false for the third parameter.

The following Gosu code applies the contains predicate method to values in the Subject field on the Activity instance for which a User instance is the assigned user. The code requests a case-insensitive comparison with the value true in the third parameter.

uses gw.api.database.Query

// Query the Activity instances for a particular user based on subject line. 
var queryActivity = Query.make(Activity)

// Join User as the dependent entity to the pimary entity Activity.
queryActivity.join(Activity#AssignedUser).compare(User#PublicID, Equals, "pc:105")

// Add a predicate on the primary entity.
// The value "true" means "case-insenstive."
queryActivity.contains(Activity#Subject, "Review", true)

// Fetch the data with a for loop.
for (activity in queryActivity.select()) {
  print("Assigned to " + activity.AssignedUser + ": " + activity.Subject)
}

If you choose case-insensitive partial comparisons, Gosu generates an SQL function that depends on your PolicyCenter and database configuration to implement the comparison predicate. If the data model definition of the column sets the supportsLinguisticSearch attribute to true, Gosu uses the denormalized version of the column instead.