1 --提取汉字,去掉非汉子数据 2 3 --创建函数 4 CREATE OR REPLACE Function F_GetCHT(Str Varchar2) Return Varchar2 Is 5 I Int; 6 Sub_Str Varchar2(500); 7 Str2 Varchar2(500); 8 Str1 Varchar2(500); 9 Str3 Varchar2(500); 10 Len Int; 11 Begin 12 Str2 := Str; 13 Select Length(Str2) Into I From Dual; 14 While I > 0 Loop 15 select substr(str2, 1, 1) into Sub_Str from dual; 16 Select asciistr(Sub_Str) Into str3 From Dual; 17 Select substr(str3, 1, 3) Into str3 From Dual; 18 select lengthb(sub_str) into len from dual; 19 If str3 != '\FF' and len = 3 Then 20 str1 := str1 || Sub_Str; 21 End If; 22 Select Substr(Str2, 2, Length(Str2)) Into Str2 From Dual; 23 Select Length(Str2) Into I From Dual; 24 End Loop; 25 if Str1 is null then 26 Return substr(Str, 10); 27 else 28 Return Str1; 29 end if; 30 31 End; 32 33 34 35 36 --创建测试数据 37 create table tests( 38 name varchar2(200) 39 ); 40 41 insert into tests (name) 42 values('你a好,你1吃D饭S了s吗'); 43 44 45 --测试函数 46 select F_GetCHT(name) from tests