Oracle 提取汉子去除非汉子数据(保留标点符号)
1 --提取汉字,去掉非汉子数据 2 3 --创建函数 4 Create Or Replace Function f_Getch(Str Varchar2) Return Varchar2 Is 5 i Int; 6 Sub_Str Varchar2(500); 7 Str2 Varchar2(500); 8 Str1 Varchar2(500); 9 Len Int; 10 Begin 11 Str2 := Str; 12 Select Length(Str2) Into i From Dual; 13 While i > 0 Loop 14 Select Substr(Str2, 1, 1) Into Sub_Str From Dual; 15 Select Lengthb(Sub_Str) Into Len From Dual; 16 If Len = 3 Then 17 Str1 := Str1 || Sub_Str; 18 End If; 19 Select Substr(Str2, 2, Length(Str2)) Into Str2 From Dual; 20 Select Length(Str2) Into i From Dual; 21 End Loop; 22 Return Str1; 23 End; 24 25 26 27 --创建测试数据 28 create table tests( 29 name varchar2(200) 30 ); 31 32 insert into tests (name) 33 values('你a好,你1吃D饭S了s吗'); 34 35 36 --测试函数 37 select f_Getch(name) from tests