Search code examples
postgresqlsql-like

Why does a query on text column in postgres work having `_` works. But without `_` does not work

This is basically a ends with query on text column in postgres 13.1

SELECT translations.value 
FROM "translations" 
WHERE (VALUE ILIKE '%deg!');

 value
-------
(0 rows)

Whereas

SELECT "translations".* 
FROM "translations" 
WHERE (VALUE ILIKE '%deg!_');

                    value
---------------------------------------------
 --- Gratulerer, du har <br> registrert deg!+
 --- Gratulerer, du har <br> registrert deg!+
 --- Vi gleder oss til å trene med deg!     +
 --- Vi gleder oss til å trene med deg!     +
 --- Vi kommer snart tilbake til deg!       +
Solution

  • That is because there is an additional character after the exclamation mark.

    To find out what character that might be, convert the results to bytea, so that you can see them byte for byte:

    SELECT CAST (translations.value AS bytea)
FROM translations
WHERE (VALUE ILIKE '%deg!_');