How do I detect an emoji in a Snowflake VARCHAR?

user_3987264 0
Viewed 471

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)
emoji snowflake-cloud-data-platform sql javascript regex
2 Answers

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
;

enter image description here

--

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

enter image description here

user_1324380

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:

enter image description here

Related: How to detect emoji using javascript

user_50708790
Related