handle incorrect values in snowflake

Question

HI I have one doubt in snow flake server. how to handle non ascii value in snowflake

table : emp 

Empno|Empname
1    |ravÉi
2    |banu raju
3    |raḠu kumar

based on above data i want output like below

Empno|Empname
1    |ravEi
2    |banu raju
3    |raGu kumar

I have tried like below

select empno,uncode(empname,ecoding='utf-8')lname from emp

but above query throwing error: sql compilation erro: error line 1 at postition 27 invalid identifier encoding

can you please tell me how to write query to achive this task in snow flake server .

Does this answer your question? [Removing accents from string in Snowflake](https://stackoverflow.com/questions/66606576/removing-accents-from-string-in-snowflake) — Felipe Hoffa, Feb 01 '23 at 02:35

Himanshu Kandpal · Answer 1 · 2023-01-31T15:44:21.817

0

did you try, it should work , the function Unicode returns the Unicode code point for the first Unicode character in a string.

select empno,empname from emp; 

select unicode('ravÉi')lname from dual;  will return the value

edited Jan 31 '23 at 15:44

answered Jan 31 '23 at 12:59

Himanshu Kandpal

1,261
8
11

It's not working .it's showing results 76 – jaiparkumar Jan 31 '23 at 13:51
you mean this query is returning you 76 , "select 'ravÉi' from dual;" – Himanshu Kandpal Jan 31 '23 at 15:44
yes this query select unicode('ravÉi')lname from dual; returning numeric values – jaiparkumar Jan 31 '23 at 17:09
The UNICODE function of Snowflake will return Unicode code point of the first Unicode character in a string this is different than what you want, I don't think the Unicode function will do that. – Himanshu Kandpal Feb 01 '23 at 01:06

score 0 · Answer 2 · answered Feb 01 '23 at 02:36

Given your sample input and output what you really want is to replace Unicode characters with their closest ASCII equivalent.

A JS UDF in Snowflake can help you do that:

You can solve this with a JS UDF in Snowflake:

CREATE OR REPLACE FUNCTION normalize_js(S string)
  RETURNS string
  LANGUAGE JAVASCRIPT
  AS 'return S.normalize("NFD").replace(/\p{Diacritic}/gu, "")'
;

select normalize_js('áéÉña');

-- 'aeEna'

See https://stackoverflow.com/a/66606937/132438.

handle incorrect values in snowflake

2 Answers2