sqlLikeCaseInsensitive lets you search string values or parts of string values without matching letter case. Use it in OCL and OCL-PS expressions when a user-entered search value should find values regardless of uppercase or lowercase characters.
Use sqlLikeCaseInsensitive
Call sqlLikeCaseInsensitive on the string value that you want to test. The operator returns true when the value matches the supplied SQL LIKE pattern without regard to case.
stringValue.sqlLikeCaseInsensitive(pattern)
For example, search Person objects by name using the value in vSeekParam:
Person.allinstances->select(a|a.Name.sqlLikeCaseInsensitive('%'+vSeekParam+'%'))
This expression finds names that contain the search text. If vSeekParam is ann, names such as Ann, ANNA, and Joanne match.
Build the match pattern
Use % in the pattern to mean that any text can occur at that position.
| Pattern | Matches | Example |
|---|---|---|
'%'+vSeekParam+'%'
|
The search text occurs anywhere in the value. | '%ann%' matches Ann and Joanne.
|
vSeekParam+'%'
|
The value starts with the search text. | 'ann%' matches Ann and Anna.
|
'%'+vSeekParam
|
The value ends with the search text. | '%ann' matches Ann and Joann.
|
Choose this operator for case-insensitive search
MDriven data is case sensitive. Use this operator when your search must handle case explicitly.
sqlLike provides the same SQL LIKE-style matching, but its case behavior in OCL-PS can depend on the database server and its collation settings. For example, the Turnkey built-in SQL Server Compact database does not ignore case for sqlLike. Use sqlLikeCaseInsensitive when you need the search to ignore case rather than depend on those settings.
For the broader rules for OCL identifiers, data, and database collations, see Documentation:Case sensitive or not.
Example: search and sort
The following expression selects people whose Identity contains the search value, ignoring case, and then orders the result by identity:
Person.allinstances
->select(a|a.Identity.sqlLikeCaseInsensitive('%'+vSeekParam+'%'))
->orderby(identity)