Accurately determines whether a field contains Chinese characters or extracts Chinese Characters

Transferred from: atgc blog

Http://atgc.itpub.net/category/22412/38862

 

Write a function to accurately determine whether a field contains Chinese characters or extract Chinese characters.
When extracting Chinese characters from the table, you need to consider character sets. Different character sets have different Chinese characters encoded.
Taking gb2312 as an example, write a function to accurately extract simplified Chinese characters from the table.

Assume that the database character set encoding is gb2312, and the environment variable (registry or other) Character Set is also gb2312 encoding.
The Chinese characters saved to the table are also gb2312 encoded.

That is, the Chinese character is dubyte, And the encoding range of simplified Chinese characters is
B0a1-f7fe
Convert to the 10th hexadecimal format
B0 A1 F7 fe
176,161-247,254

Let's take a look at the definition of the asciistr function.
Non-ASCII characters are converted to the form XXXX, where XXXX represents a UTF-16 code unit.
However, this does not mean that the character starting with "" is a Chinese character.

Example:

SQL> select * from test; <br/> name <br/> ------------------ <br/>, ah oo10 ha <br/> Hello AA/<br/> ☆sea 123 <br/>★ABC
 
Create or replace function get_chinese (p_name in varchar2, <br/> p_chinese in varchar2) <br/> return varchar2 as <br/> v_code varchar2 (30000): = ''; <br/> v_chinese varchar2 (4000): = ''; <br/> v_non_chinese varchar2 (4000): =''; <br/> v_comma pls_integer; <br/> v_code_q pls_integer; <br/> v_code_w pls_integer; <br/> begin <br/> If p_name is not null then <br/> select Replace (substrb (dump (p_name, 1010 ), <br/> listen B (dump (p_name, 1010), 'zhs16gbk: '), <br/> 'zhs16gbk:', <br/> '') <br/> into v_code <br/> from dual <br/> where rownum = 1; <br/> for I in 1 .. length (p_name) loop <br/> If lengthb (substr (p_name, I, 1) = 2 then <br/> v_comma: = 1_ B (v_code ,','); <br/> v_code_q: = to_number (substrb (v_code, 1, v_comma-1); <br/> v_code_w: = to_number (substrb (v_code, <br/> v_comma + 1, <br/> ABS (using B (v_code, ',', 1, 2)-<br/> v_comma-1 ))); <br/> If v_code_q> = 176 and v_code_q <= 247 and v_code_w> = 161 and <br/> v_code_w <= 254 then <br/> v_chinese: = v_chinese | substr (p_name, I, 1); <br/> else <br/> v_non_chinese: = v_non_chinese | substr (p_name, I, 1 ); <br/> end if; <br/> v_code: = ltrim (v_code, '000000'); <br/> v_code: = ltrim (v_code ,','); <br/> else <br/> v_non_chinese: = v_non_chinese | substr (p_name, I, 1); <br/> end if; <br/> v_code: = ltrim (v_code, '000000'); <br/> v_code: = ltrim (v_code, ','); <br/> end loop; <br/> If p_chinese = '1' then <br/> return v_chinese; <br/> else <br/> return v_non_chinese; <br/> end if; <br/> else <br/> return ''; <br/> end if; <br/> end;

 

The first record contains a solid pentagram.
Use the asciistr function to convert the data.

SQL> select name, asciistr (name) from test; <br/> name asciistr (name) <br/> ------------------ ---------------------- <br/>, Ah oo10, 554aoo1054c8 <br/> Hello AA 4f60597daa <br/> Hello everyone AA/59275bb6597daa/<br/> ☆sea 123 260659276d77123 <br/>★ABC 2605abc
 

 

We can see that the solid pentagram in the last record starts ""
In this case, we cannot use asciistr (field) to determine whether it contains Chinese characters.

My function is as follows. The basic idea is to determine whether the character encoding is within the Chinese character encoding range specified by gb2312.

Create or replace function get_chinese (p_name in varchar2) return varchar2 as <br/> v_code varchar2 (30000): = ''; <br/> v_chinese varchar2 (4000 ): = ''; <br/> v_comma pls_integer; <br/> v_code_q pls_integer; <br/> v_code_w pls_integer; <br/> begin <br/> If p_name is not null then <br/> select Replace (substrb (dump (p_name, 1010 ), <br/> listen B (dump (p_name, 1010), 'zhs16gbk: '), <br/> 'zhs16gbk:', <br/> '') <br/> into v_code <br/> from dual <br/> where rownum = 1; <br/> for I in 1 .. length (p_name) loop <br/> If lengthb (substr (p_name, I, 1) = 2 then <br/> v_comma: = 1_ B (v_code ,','); <br/> v_code_q: = to_number (substrb (v_code, 1, v_comma-1); <br/> v_code_w: = to_number (substrb (v_code, <br/> v_comma + 1, <br/> ABS (using B (v_code, ',', 1, 2)-<br/> v_comma-1 ))); <br/> If v_code_q> = 176 and v_code_q <= 247 and v_code_w> = 161 and <br/> v_code_w <= 254 then <br/> v_chinese: = v_chinese | substr (p_name, I, 1); <br/> end if; <br/> v_code: = ltrim (v_code, '123 '); <br/> v_code: = ltrim (v_code, ','); <br/> end if; <br/> v_code: = ltrim (v_code, '123 '); <br/> v_code: = ltrim (v_code, ','); <br/> end loop; <br/> return v_chinese; <br/> else <br/> return ''; <br/> end if; <br/> end;

Okay. Run some statements now.

SQL> select * from test; <br/> name <br/> ------------------ <br/>, ah oo10 ha <br/> Hello AA/<br/> ☆sea 123 <br/>★ABC
 

 

5 rows selected.

1. List records with Chinese characters

SQL> select name from test where length (get_chinese (name)> 0; <br/> name <br/> ------------------ <br/>, ah oo10 ha <br/> Hello AA/<br/> ☆sea 123 <br/> 4 rows selected.
 

 

2. List records with Chinese characters and only list Chinese Characters

 

SQL> select get_chinese (name) from test where length (get_chinese (name)> 0; <br/> get_chinese (name) <br/> summary <br/> aha <br/> Hello <br/> hello, everyone <br/> Sea <br/> 4 rows selected.
 

 

It must be noted that gb2312 has a total of 6763 Chinese characters, I .e. 72*94-5 = 6763
Here I calculate 72*94, without subtracting the five, and the five are empty. And then subtract
================

Rewrite this function to extract non-Chinese or Chinese Characters
This function has two parameters. The first parameter indicates the string to be extracted, the second parameter is 1, indicating that the Chinese character is extracted, and the non-1 parameter indicates that the non-Chinese character is extracted.

Create or replace function get_chinese <br/> (<br/> p_name in varchar2, <br/> p_chinese in varchar2 <br/>) return varchar2 <br/> as <br/> v_code varchar2 (30000): = ''; <br/> v_chinese varchar2 (4000): = ''; <br/> v_non_chinese varchar2 (4000): = ''; <br/> v_comma pls_integer; <br/> v_code_q pls_integer; <br/> v_code_w pls_integer; <br/> begin <br/> If p_name is not null then <br/> select Replace (substrb (dump (p_name, 1010), substring B (dump (p_name, 1010 ), 'zhs16gbk: '), 'zhs16gbk:', '') into v_code from dual where rownum = 1; <br/> for I in 1 .. length (p_name) loop <br/> If lengthb (substr (p_name, I, 1) = 2 then <br/> v_comma: = 1_ B (v_code ,','); <br/> v_code_q: = to_number (substrb (v_code, 1, v_comma-1); <br/> v_code_w: = to_number (substrb (v_code, v_comma + 1, ABS (distinct B (v_code, ',', 1, 2)-v_comma-1 ))); <br/> If v_code_q> = 176 and v_code_q <= 247 and v_code_w> = 161 and v_code_w <= 254 then <br/> v_chinese: = v_chinese | substr (p_name, i, 1); <br/> else <br/> v_non_chinese: = v_non_chinese | substr (p_name, I, 1); <br/> end if; <br/> v_code: = ltrim (v_code, '000000'); <br/> v_code: = ltrim (v_code ,','); <br/> else <br/> v_non_chinese: = v_non_chinese | substr (p_name, I, 1); <br/> end if; <br/> v_code: = ltrim (v_code, '000000'); <br/> v_code: = ltrim (v_code, ','); <br/> end loop; <br/> If p_chinese = '1' then <br/> return v_chinese; <br/> else <br/> return v_non_chinese; <br/> end if; <br/> else <br/> return ''; <br/> end if; <br/> end; <br/>/
 

.--------------------------------------------------------------------------------

SQL> select * From A; <br/> name <br/> ------------------ <br/> WE, <br/> He (AI) is★<Br/> their ah @ <br/> SQL> select get_chinese (name, 1) from a; <br/> get_chinese (name, 1) <br/> ------------------------------------- <br/> Let's <br/> He Aiah <br/> his ah <br/> SQL> select get_chinese (name, 0) from a; <br/> get_chinese (name, 0) <br/> ----------------------------------------- <br/>, <br/> ()★<Br/> @ <br/> SQL>