Is this the same as empty string vs NULL?

No. '' is a blank field. 'active ' is a real status with a space on the end. IS NULL and = '' both miss it. TRIM(status) = 'active' catches it.

Is this the same as case-sensitive search?

Different. Active vs active is collation. 'active' vs 'active ' is bytes. You can have both on the same column.

Why does CHAR(10) make this worse?

CHAR pads with spaces to the declared length. A CHAR(10) 'active' is stored as 'active '. Some databases pad on compare, some don't — Postgres CHAR comparisons can surprise you. Prefer VARCHAR/TEXT for statuses.

Does TRIM in the WHERE prevent an index?

Often yes — TRIM(status) = 'active' may skip a normal index on status. For a chat query that's fine. For a daily report, fix the data or add a functional index / check constraint.

How do I see the spaces?

SELECT status, LENGTH(status), CONCAT('[', status, ']') FROM customers GROUP BY status. Length 7 instead of 6, or brackets showing [active ], is the tell.

SQL Trailing Space Filter Miss | TRIM

You ask for status = 'active'. Half the active customers are missing. You open a row — it says active. The column is 'active '. Equality does not strip spaces. The admin UI usually does.

The value is not the label

WHERE status = 'active'           -- misses 'active ', ' active', 'active\n'
WHERE TRIM(status) = 'active'     -- catches those

CSV imports, form fields, and Excel "clean" columns do this constantly. CHAR(n) columns pad with spaces to a fixed width — 'active' in CHAR(10) is ten characters. Depending on the database, a comparison to 'active' may or may not pad the literal to match. Postgres character vs text is a common trap after a migration.

This sits next to case-sensitive search and empty string vs NULL. Those under-count too. The fix here is TRIM (or BTRIM), not ILIKE or IS NULL.

How to catch it

SELECT status, COUNT(*), LENGTH(status)
FROM customers
GROUP BY status
ORDER BY 2 DESC;

Two rows that look like active with lengths 6 and 7 is this bug. Wrapping the value in brackets — SELECT '[' || status || ']' — makes the space visible in chat.

The fix

WHERE TRIM(status) = 'active'
-- or normalize once
WHERE TRIM(BOTH FROM status) = 'active'

If you also have mixed case: LOWER(TRIM(status)) = 'active'. Don't stack functions until you've seen LENGTH — maybe it's only case, or only NULL.

The prompt that heads this off

When a text filter misses rows that look like they match, check
LENGTH and distinct values before changing the logic. TRIM both
the column and the literal if a CSV/import or CHAR column is involved.
Don't assume the UI's display is the stored bytes.

The short version

What you see in the admin UI is trimmed. What SQL compares is not. If a status filter is "obviously short," look at LENGTH before you rewrite the join.

Read-only MCP gateway: mcpserver.design.

Related reading: