Solution for extracting "Chinese character" strings from ORACLE strings: oracle string
In actual work, I often use the function of removing special characters, especially the data inserted into the database. To ensure the quality of the data, we usually 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.