How do I detect an emoji in a Snowflake VARCHAR?

Question

I have a 1B row table of chat messages with a max-sized VARCHAR column for the raw message text. How do I -

1. Select just the rows that contain 1 or more emoji characters
2. Efficiently filter out rows without emoji (if needed to improve performance)

Answer 1

Combining the knowledge shared by Lukasz and Greg, plus discovering an additional hidden character, I can present this solution:

• 1 JS UDF that detects the presence of emojis.
• 1 JS UDF that detects if the string is only emojis.

create or replace function has_emoji(X text)
returns boolean
language javascript
as $$
return /\p{Extended_Pictographic}/u.test(X);
$$;

create or replace function only_emoji(X text)
returns boolean
language javascript
as $$
return /^[\p{Extended_Pictographic}\p{Emoji_Component}]+$/u.test(X);
$$;

Sample use:

with data as (
  select $1 t
  from values('❄️'),('❄️ is the data ☁️'),('no emoji')
)

select *, has_emoji(t), only_emoji(t)
from data
;

--

Some research:

• '❄️'.replace(/\p{Extended_Pictographic}/ug, '') returns ''
• but '❄️'.replace(/\p{Extended_Pictographic}/ug, '') is not equal to ''
• Turns out there are hidden characters that still need to be replaced, and matched by Emoji_Component

Answer 2

Using Snowflake's JavaScript User Defined Function:

CREATE OR REPLACE FUNCTION EMOJI_TEST (STR VARCHAR)
  RETURNS VARCHAR
  LANGUAGE JAVASCRIPT
  AS $$
  return (/\p{Emoji}/u.test(STR));
  $$
  ;

Query:

WITH cte(col) AS (
    SELECT ''   UNION ALL
    SELECT 'text' UNION ALL
    SELECT 'text '
)
SELECT col, EMOJI_TEST(col)
FROM cte
-- WHERE EMOJI_TEST(col)::BOOLEAN

Output:

Related: How to detect emoji using javascript