Oracle 提取汉子去除非汉子数据(不保留标点符号) 

 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

 

posted @ 2018-08-11 00:30  Jarman  阅读(1061)  评论(0)    收藏  举报