How to normalize unicode strings in Snowflake?

user_132438 0
Viewed 19

"The Unicode standard defines various normalization forms of a Unicode string, based on the definition of canonical equivalence and compatibility equivalence."

For example, how can I determine in Snowflake that these 2 strings are essentially equivalent?

select '株式会社ｻｲﾊﾞｰｴｰｼﾞｪﾝﾄＤＸ' a , '株式会社サイバーエージェントDX' b , a=b ;

snowflake-cloud-data-platform user-defined-functions unicode python
2 Answers

I wrote a Python UDF in Unicode to normalize using the package unicodedata. You can adapt this to your needs:

create or replace function unicode_normalize(x string)
returns string
language python
runtime_version=3.8
handler = 'x'
as $$

import unicodedata

def x(x):
    return unicodedata.normalize('NFKC', x)
$$;

Now this returns true:

select unicode_normalize('株式会社ｻｲﾊﾞｰｴｰｼﾞｪﾝﾄＤＸ') a
    , unicode_normalize('株式会社サイバーエージェントDX') b
    , a=b
user_1324380

Javascript has a normalize string function too:

create or replace function NORMALIZE_STRING("str" string)
returns string
language javascript
strict immutable
as
$$
    return str.normalize('NFKC');
$$;
user_127563810
Related