Postgresql query to lookup emojis

Question

I'm trying to lookup emojis on a table on postgresql.

Sometimes I've got this on my results:

# SELECT id, title FROM my_texts WHERE title ILIKE '[%';
  id   |                             title
-------+---------------------------------------------------------------

85981  | [<U+1F6AB>] forbidden sample text

I've tried to lookup which titles have this emoji using the following queries:

SELECT id, title FROM my_texts WHERE title ILIKE '%<U+1F6AB>';
SELECT id, title FROM my_texts WHERE title ILIKE '%\U+1F6AB%'
SELECT id, title FROM my_texts WHERE title ILIKE '%\U1F6AB%'

But none of these gave me the results that I want.

How to properly write this unicode character?

Answer 1

Using the Postgres escape sequence might be what you're looking for: WHERE title LIKE E'%\u1F6AB%'.