Could not remove unicode character

user_5676412 0
Viewed 1282

I have a text string in Oracle table ("NGUYỄN NGỌC HOÀNG"). I have tried to replace the Unicode string (my expected result is "NGUYEN NGOC HOANG") but it didn't work.

Then I tried to convert it to HEX as below: select rawtohex(UnicodeColumn) from TestTable; Here is the result: 4E 47 55 59 C3 8A CC 83 4E 20 4E 47 4F CC A3 43 20 48 4F 41 CC 80 4E 47

Following the Unicode table: The corresponding characters are: NGUYỄN NGỌC HOÀNG

Could anyone help me this case (Oracle script), I don't have experience about the encoding.

Update 1:

I have tried the solution from Barbaros Özhan but it didn't work

SQL> with tab as ( select '&i_str1' str1 from dual ) 2 select str1, utl_raw.cast_to_varchar2((nlssort(str1,'nls_sort=binary_ai'))) str2 from tab SQL> / Enter value for i_str1: NGUYỄN NGỌC HOÀNG

enter image description here

hex encoding unicode oracle
3 Answers
SQL> with tab as ( select '&i_str1' str1 from dual )
  2  select str1, utl_raw.cast_to_varchar2((nlssort(str1,'nls_sort=binary_ai'))) str2 from tab
SQL> /
Enter value for i_str1: NGUYỄN NGỌC HOÀNG

and you get the desired result.

user_58413060

If you are using Java for connecting to Oracle DBMS, try the Java code below to convert to unaccent character, after that update the row in DBMS.

public static String unAccent(String s) {
    String temp = Normalizer.normalize(s, Normalizer.Form.NFD);
    Pattern pattern = Pattern.compile("\\p{InCombiningDiacriticalMarks}+");
    return pattern.matcher(temp).replaceAll("").replaceAll("Đ", "D").replace("đ", "");
}
user_67074300

You can try this function to convert to non-accent character:

CREATE OR REPLACE FUNCTION FN_CONVERT_TO_VN
(
    STRINPUT IN NVARCHAR2
)
RETURN NVARCHAR2
IS
    STRCONVERT NVARCHAR2(32527);
BEGIN
STRCONVERT := TRANSLATE(
    STRINPUT, 'áàảãạăắằẳẵặâấầẩẫậđéèẻẽẹêếềểễệíìỉĩịóòỏõọôốồổỗộơớờởỡợúùủũụưứừửữựýỳỷỹỵÁÀẢÃẠĂẮẰẲẴẶÂẤẦẨẪẬĐÉÈẺẼẸÊẾỀỂỄỆÍÌỈĨỊÓÒỎÕỌÔỐỒỔỖỘƠỚỜỞỠỢÚÙỦŨỤƯỨỪỬỮỰÝỲỶỸỴ', 'aaaaaaaaaaaaaaaaadeeeeeeeeeeeiiiiiooooooooooooooooouuuuuuuuuuuyyyyyAAAAAAAAAAAAAAAAADEEEEEEEEEEEIIIIIOOOOOOOOOOOOOOOOOUUUUUUUUUUUYYYYY'
);
RETURN STRCONVERT;
END;
user_67074300
Related