Regexes (Regular Expressions) in Queries

Regular Expressions (Regexes)

A Regex is simply sequence of characters that define a search pattern. This pattern is then compared with the target test string and will produce a match if the pattern allows.

Cradle Queries

When retrieving information from the database, Cradle uses a query to filter the data returned. It looks for many matches against different aspects  of the item.

Ending Wildcards and Contains

Wildcard matches are available on the Identity and the Key fields The ones most likely to be identifying a hierarchy.

In the example below a query has been run with three values in the identity field.

  1. REQ-1
  2. REQ-1.
  3. REQ-1*

The results show

  1. Only REQ-1 returned  and exact match
  2. REQ-10, REQ-11, REQ-12 …. onwards a text match bringing back the matches greater than the stem. This is really useful for hierarchical numbers, bringing back the children of 1.1 (using 1.1.) say.
  3. REQ-1, REQ-10, REQ-11 a text match where the stem is followed by zero or more characters. This brings back the stem value too.
Wildcard values in a query
Wildcard Query

Contains matches are available on Name and Comment fields. They are case insensitive and look for the string of characters anywhere in the component.

In the example below

  1. regulation

Results in

  • Regulations
  • CAA Regulations
  • Airworthiness Regulations
  • Noise Regulations

This can be a little too wide.

Regular expression (regex) in a query
Regex Query

Regex Query

choosing a regex component in a query
Choose Regex

In order to provide more control it is now (Cradle 7.4) possible to switch to a Regex match. Choose the dropdown at the end of the field and select Regex Match. This will change how the search text is applied.

In the example above

  1. ^Regulation

Brings back

  1. “Regulations” only because the “^” at the beginning is a regex anchor to  the start of the line.

Expressions

If we were searching for part numbers we might use ^Pt:[AB]+.* to bring back all parts starting Pt:A and Pt:B followed by any other characters.

There are many flavours of Regex, Cradle is based on the PCRE flavour. Instead of documenting the whole set of patterns, it is usually more appropriate for users to use their favourite documentation. We like www.regular-expressions.info/ and www.regex101.com/

Pros / Cons

Databases have a number of keys and indexes. Cradle is designed to work with these to produce results most effectively. Wildcard endings are more efficient as they match the way the data is sorted internally. Regex expressions have to match against all database entries. therefore there is a performance implication to using this more flexible matching. This is why both methods are provided and users can switch whichever they want on.