How to query only the data that has emojis (postgresql)

Question

I have data that contains emojis within a database column, i.e.

message_text
-------
🙂
😀
Hi 😀

I want to query only the rows that have data containing emojis. Is there a simple way to do this in postgres?

select data from table where data_col contains emoji

Currently I use a simple query

select message_text from cb_messages_v1 cmv
 where message_text IN ('👍🏻','😀','😐','🙂', '😧')

but I want it to be more dynamic, where if future emotions are added it will capture the data.

Answer 1

From your example it seems like you are not only interested in emoticons (U+1F601 - U+1F64F), but also in Miscellaneous Symbols And Pictographs (U+1F300 - U+1F5FF) and Transport And Map Symbols (U+1F680 - U+1F6C5).

You can find values that contain one of these with

WHERE textcol ~ '[\U0001F300-\U0001F6FF]'

~ is the regular expression matching operator, and the pattern is a range of Unicode characters.