I have a table in SQL that contains text data, and sometimes that text data contains emojis. See below for sample table output:
| CommentID | Comment_Text |
|---|---|
| 1 | A walk in the park. |
| 2 | A lovely day in the park |
| 3 | A sunny day in the park |
What I'd like to do is separate out the emoji as a column. Ultimately what I'd like to end up with is a binary column indicating whether the comment text contains an emoji.
After some research, I was able to find the following solution:
REGEXP_SUBSTR(Comment_Text, '[^\x00-\x7F]+', 1, 1)
Which will separate out the first emoji the code finds. In actuality, this regex doesn't find emojis, it just finds non-ASCII characters - emojis just happen to fall in that category. While this solution does work sometimes, it does not work when there are emojis and non-ASCII characters in the same comment. So, for example, if the comment was 'A lovely walk in the πaÞk ', the code would output both the emojis but also the π and the Þ.
What I need is a way to separate out the emojis from the other non-ASCII characters.