Search patterns with LIKE: % and _

What this is about

In a search (POST /query) you write filters like this one:

{ "key": "name", "value": "Muster%", "param": "LIKE" }

LIKE compares a text with a search pattern. The pattern has two placeholders:

Character	stands for	Example	matches
`%`	any number of characters, including none	`Mus%`	`Mus`, `Muster`, `Muster GmbH`
`_`	exactly one character	`M_ier`	`Maier`, `Meier`, but not `Mayer`

Do not confuse them with the wildcards + and * in the field selection: those decide which fields come back. % and _ decide which rows are found.

Which pattern matches what?

Example data: five customers with these names.

Five patterns against five names

Muster GmbH	Mustermann	Alt-Muster AG	muster	Meister	Pattern (value)
no	no	no	depends on DB	no	`Muster` – without placeholders: exactly this text only
yes	yes	no	depends on DB	no	`Muster%` – starts with “Muster”
no	no	no	depends on DB	no	`%Muster` – ends with “Muster”
yes	yes	yes	depends on DB	no	`%Muster%` – contains “Muster”
no	no	no	no	yes	`M__ster` – M, any two characters, then “ster”

“depends on DB” means: whether muster (lower case) is matched by Muster (upper case) is decided by the database, not by CDMS. More on this further below.

What happens along the way

1

User→Client
types “Muster” into the search box
2

Client
builds the filter. This is where it is decided whether Muster, Muster% or %Muster% is sent
3

Client→CDMS
sends { "key": "name", "value": "%Muster%", "param": "LIKE" }
4

CDMS
translates the filter 1:1 into name LIKE '%Muster%'. No rewriting, no additions, no escaping
5

CDMS→Database
runs the query, together with the filters that always run along
6

Database
compares by its own rules (collation), which includes with or without case sensitivity

Result: Matches come back as a list

All variants

LIKE in all its forms

containsstarts withends withexactly one characterwithout placeholderssearching for % or _param left out

When: A search box where the text may appear anywhere.

The client sends %text%. This is the usual form for a free search.

Result: Finds “Muster GmbH”, “Mustermann” and “Alt-Muster AG”.

When: Autocomplete, number ranges, prefixes.

The client sends text%. On large tables this form is usually much faster than “contains”, because the database can use an index.

Result: Finds “Muster GmbH” and “Mustermann”.

When: File extensions, domains in email addresses.

The client sends %text, for example %@codamic.com.

Result: All values with exactly this ending.

When: Spelling variants with a fixed length, for example Meier/Maier.

_ stands for exactly one character. M_ier matches Maier and Meier, M__er matches all five-letter names with M at the start and er at the end.

Result: Only values with exactly the matching length.

When: usually by mistake.

The client sends text without %. This is an equality check, except that, depending on the database, upper and lower case may be ignored.

Result: Only exactly “text”. If you mean equality, use EQ instead.

When: The text you are looking for itself contains a % or _, for example 10% or max_wert.

CDMS has no escaping. max_wert also finds maxXwert, because _ counts as a placeholder. You cannot search for a real % or _ with LIKE.

Result: Too many matches. Workaround: EQ for exact values, or filter again in the client.

When: The filter has no param.

LIKE is the default operator. So { "key": "name", "value": "Muster" } is a LIKE without placeholders, and therefore an exact comparison.

Result: Same behavior as “without placeholders”.

Upper and lower case

CDMS itself does nothing with the spelling. Whether muster and Muster are equal is decided by the collation of the database column:

MySQL 8 (production)

Default collation utf8mb4_0900_ai_ci

Upper and lower case are ignored
Accents are mostly ignored: “Muller” finds “Müller”
applies as long as the column was not created differently

H2 (tests)

Upper and lower case matter
so a test can have a different result than production

What LIKE is not meant for

Only for text fields. For numbers, dates, yes/no and IDs there are EQ, IN, BEFORE, AFTER and the other operators.
No regular expressions. [A-Z], .* or ^ have no special meaning.
No full-text search. If you search across several fields, combine several LIKE filters in an OR group.

Search across three fields

Request

"query": {
  "type": "OR",
  "filter": [
    { "key": "name",           "value": "%muster%", "param": "LIKE" },
    { "key": "email",          "value": "%muster%", "param": "LIKE" },
    { "key": "customerNumber", "value": "%muster%", "param": "LIKE" }
  ]
}

Response

Finds every customer where “muster” appears in the name,
email OR customer number.