Why Did a Pump Search Return the Wrong Equipment?

The maintenance import contains “ PUMP_7 ”, “pump_9” and “pumpX8”. Botie searches for codes beginning with pump_ and unexpectedly includes pumpX8. In a LIKE pattern, the underscore has a special meaning: it matches any single character.

Before searching text, we need to decide how names should be cleaned and which characters in the search are literal. A query can run successfully while still applying the wrong naming policy.

Trim surrounding ordinary spaces, lowercase ASCII letters, and retain codes beginning with the literal pump_ prefix.

[
  {
    "record_id": 1,
    "normalised": "pump_7"
  },
  {
    "record_id": 3,
    "normalised": "pump_9"
  }
]

Keep the original imported code if it matters for audit or display. Add a derived normalised value for the specific matching policy. Silently overwriting an external identifier can make it difficult to reconcile later records.

Our rule removes ordinary spaces from both ends and lowercases ASCII letters. It leaves internal spaces and non-ASCII characters alone. Empty text remains different from NULL.

A cleanup rule should describe what changes and what remains meaningful.

SQLite trim(code) removes ordinary space characters at the ends by default. It does not promise to remove every Unicode whitespace character or internal spacing. Its default lower function converts ASCII letters.

A multilingual name-matching system needs a deliberate Unicode normalisation and case-folding policy, often with additional library support. Test that policy before using it in a unique key or deduplicating people.

A simple identifier lesson should not imply that all visually similar names are interchangeable.

CREATE TABLE codes(record_id INTEGER PRIMARY KEY, code TEXT);
INSERT INTO codes VALUES (1,' PUMP_7 '),(2,'pumpX8'),(3,'pump_9'),(4,NULL),(5,'PÜMP_2');

SELECT record_id, lower(trim(code)) AS normalised FROM codes ORDER BY record_id;

LIKE uses percent for any sequence of characters and underscore for one character. Our pattern must escape the underscore but retain the final percent wildcard. ESCAPE declares which character introduces that literal interpretation.

A literal prefix can also be expressed with an appropriate substring comparison. Parameter binding protects query structure, but it does not automatically turn pattern wildcards in a parameter into literal characters. Escape them according to the search contract.

SQL injection safety and wildcard interpretation are separate responsibilities.

SELECT 'pumpX8' LIKE 'pump_%',
       'pumpX8' LIKE 'pump\_%' ESCAPE '\',
       'pump_8' LIKE 'pump\_%' ESCAPE '\';

A collation determines how text is compared for operations such as ordering and equality. SQLite BINARY compares its encoded text representation; built-in NOCASE folds ASCII letter case rather than providing universal linguistic equality.

LIKE has its own documented matching behaviour, so attaching a collation is not a general way to rewrite its wildcard or Unicode rules. If case sensitivity matters, verify the exact operator and database configuration used by the query.

Do not assume one comparison rule governs every text operation in every database.

The || operator concatenates text in SQLite. A NULL operand normally makes that concatenation NULL. Use COALESCE only when you have chosen a real fallback meaning, such as a displayed “unknown” marker.

substr can select a segment and instr can locate a substring. SQLite positions in these functions are one-based; instr returns zero when the substring is absent. Explicit examples help avoid importing Python’s zero-based indexing assumptions.

A display label is not a reliable replacement for separate structured identifier fields.

SELECT instr('pump_7', '_'), substr('pump_7', 6), 'pump_' || NULL;

Two different raw codes can become equal after cleanup. That is useful only if the domain says those forms represent the same equipment. Preserve a stable internal key and review collisions before enforcing uniqueness on the derived value.

The challenge deliberately returns record IDs beside cleaned text. If two matching records clean to the same code, both records remain. It is a selection exercise, not an unrequested deduplication policy.

Use examples with literal underscores, missing text and unfamiliar characters to test the policy you actually promised.