How to extract Chinese character strings from Oracle

How to extract Chinese character strings from Oracle

In actual work, we often need to use the function of removing special characters, especially the data inserted into the database. To ensure the data quality, we often need to clean the data, that is, remove special characters, the solution is to determine whether each character in the string is Chinese. If it is Chinese, connect them in order. If not, do not.

Based on actual business needs, there are two solutions:

Solution 1:

Use the database's built-in function length and lengthb.

In Chinese, length returns the number of characters, Chinese occupies 1 character, lengthb returns the number of bytes, and Chinese occupies 2 bytes. This can be solved based on Chinese features, but the actual situation is often not ideal, there are often some special characters, which are consistent with the number of Chinese characters and the number of Chinese characters, so we cannot accurately judge the Chinese characters in the database. To solve this problem, solution 2 is recommended.

Solution 2:

Use ASCII codes to distinguish Chinese characters from other characters. The value range of Chinese ASCII codes is 45217 ~ 63486. Based on this implementation, we need to use the Oracle function ASCII to return the ASCII value.

This method perfectly distinguishes Chinese characters from other characters.

Create or replace function getCustText (custName varchar2) return varchar2 is
Result varchar2 (100 );
Tmp_custName varchar2 (100 );
Count_str number;
I number: = 1;
Str_ascii number;
Current_char varchar2 (10 );
Begin
Select length (custName) into count_str from dual;
While I <count_str loop
Current_char: = substr (custName, I, 1 );
Select ASCII (current_char) into str_ascii from dual;
If str_ascii> 45216 then
Tmp_custName: = tmp_custName | current_char;
End if;
I: = I + 1;
End loop;
Result: = tmp_custName;
Return (Result );
End getCustText;

The above is an implementation method in Oracle, which can quickly and accurately recognize Chinese characters.

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

Install Oracle 11.2.0.4 x64 in Oracle Linux 6.5