You can use string matching to add conditions in conformity metrics and SQL metrics. Two types of pattern matching are supported: SQL patterns and regular expressions.

SQL pattern matching

You can use SQL pattern matching to create conditions in conformity metrics and SQL metrics. You can include specific characters/strings, wildcards, or a combination.

• Use a specific character or character sequence to match it exactly, or in combination with wildcards. For example, 'North%' matches any string that starts with North. Note that for some datasources specific characters are case-sensitive, while in others they are case-insensitive (potentially affecting your matches).
• There are two wildcards: an underscore (_) matches any one character, and a percent sign (%) matches a string of any length. For example, 'North__%' (note the consecutive underscores) matches any string that begins with North and is at least seven characters long, such as North 4th Street.
• Enclose a set or range of characters inside square brackets to match any one of them. For example:
  • '[a,b,c]' evaluates one character and matches if it is a, b, or c.
  • '[a-c]' also evaluates one character and matches if it is a, b, or c.
  • 'D[a,o]%' matches any string that begins with Da or Do.

Some datasources support more varieties of SQL pattern matching, such as including a caret (^) as the first character inside square brackets to exclude rather than match the values inside the brackets. Check your datasource documentation for more information about supported pattern matching options.

SQL patterns in Conformity metrics

When you create a conformity metric, you must add at least one condition to check rows for conformity. If you choose Does not match pattern or Match pattern, the pattern you enter for comparisons is used with the LIKE operator as a single-quote delimited string in the metric query. For example, the following SQL returns the count of rows where the value of Restaurant begins with D:

SUM(
		CASE
			WHEN "Restaurant" LIKE 'D%' THEN 1
			ELSE 0
		END
	) AS "count",

SQL patterns in SQL metrics

You can use SQL patterns with the LIKE operator anywhere in your SQL metric's query as part of a WHERE clause.

Regular expression matching

You can use regular expressions to match strings inside metric queries. You can use regex101.com to generate regular expressions, but not all datasources handle meta sequences the same. For example, if your expression contains the sequence \d (to match any digit), the datasource may require that you add a backslash in front of the sequence for it to work in a SELECT statement: to get \d, you enter \\d.

Regex in Conformity metrics

When you create a conformity metric, you must add at least one condition to check rows for conformity. If you choose Does not match regex or Match regex, the pattern you enter for comparisons becomes a single-quote delimited string in the metric query. Because you cannot change the string delimiters, this can cause SQL errors when your pattern contains meta sequences, depending on the datasource.

Regex in SQL metrics

You can use regular expressions in your SQL metrics as needed, but meta sequences might cause SQL errors depending on how you use them and the metric's datasource. In some cases, you can use specific string delimiters to avoid these errors. Otherwise, you can add escape characters in front of any meta sequences.

Check your datasource's official help

Before you use a regular expression in a metric, consider referring to the official support content for the metric's datasource, in particular content using regular expressions in SELECT statements.