6

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.

cluis92
  • 664
  • 12
  • 35
  • 1
    The same way you would search for filed containing single character "A" or "B" or ... Try this: https://stackoverflow.com/questions/12957993/how-to-use-sql-like-condition-with-multiple-values-in-postgresql – O-9 Feb 11 '21 at 16:19
  • @O-9 that is helpful, but I what if a new emoji character is introduced? i would have to update my code and include it. Is there a way to do this without having to hard code a list of all possible emojis? – cluis92 Feb 11 '21 at 16:27

1 Answers1

17

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263