String comparison, case and trailing spaces

It depends on the collation. A collation is the rule set for comparing text, and it decides if 'delhi' equals 'Delhi'. It does not decide for 'Delhi ' at all, because the trailing space is a real character in most engines.

What different engines do

• Postgres, BigQuery and Snowflake compare text case-sensitively by default. 'delhi' = 'Delhi' is false.
• SQL Server and MySQL usually ship with case-insensitive default collations, so the same query matches. Which is why a query that "worked on the dev database" can break after moving to a warehouse.
• Trailing spaces: most engines treat 'Delhi ' and 'Delhi' as different strings. SQL Server follows the padding rule and ignores trailing spaces in =. Fixed-length CHAR(n) columns pad values with spaces, which causes more surprises. Test it on your own engine.

Matching reliably

For a one-off query, normalise both sides:

WHERE LOWER(TRIM(city)) = 'delhi'

But think twice before doing this in every query. A function wrapped around a column can stop an index or partition from being used (see the question on sargability), and every analyst will write it slightly differently.

The better place to fix it

Clean the data once, when it arrives. In the staging layer, add a clean column:

SELECT
  city AS city_raw,
  LOWER(TRIM(city)) AS city
FROM raw.customers;

Then every downstream query filters on city and means the same thing. For values that have spelling variants too ("New Delhi", "Delhi NCR"), a small mapping table is better than ever-longer CASE statements.

A last point on LIKE. In Postgres, LIKE is case-sensitive and ILIKE is not. Other engines differ again, so look it up for the one you use.