sql问题，高手门来看看
我现在数据库有如：李云飞 Li Yunfei张飞虎 Zhang Feihu 等
我现在想通过SQL或者别的方法，来取得拼音中的首个字母。
要要的结果是：LYF
ZFH
这杂写呀？高手帮忙看看，谢谢！
有很多名字，我只举了两个，谁知道用那种方法？ SQL> create or replace function f_hztopy(p_hz varchar2) return varchar2 is
2 lv_temp varchar2(2000);
3 lv_bytes Varchar2(50);
4 lv_char Varchar2(10);
5 li_bytes Integer;
6 li_pos Integer;
7 Begin
8 If p_hz Is Null Then
9 Return '' ;
10 End If;
11 For i In 1..length(p_hz) Loop
12 lv_char := substr(p_hz,i,1);
13 If lengthb(lv_char) = 1 Then
14 lv_temp:=lv_temp||lv_char;
15 Else
16 Select dump(lv_char) Into lv_bytes from dual;
17 lv_bytes:=substr(lv_bytes,instr(lv_bytes,': ')+2);
18 li_pos:=instr(lv_bytes,',');
19 li_bytes:=substr(lv_bytes,1,li_pos-1)*256+substr(lv_bytes,li_pos+1);
20 if ( li_bytes < to_number('B0A1','xxxx')) Then
21 lv_temp:=lv_temp||'*';
22 Elsif ( li_bytes < to_number('B0C5','xxxx')) Then
23 lv_temp:=lv_temp||'a';
24 Elsif ( li_bytes < to_number('B2C1','xxxx')) Then
25 lv_temp:=lv_temp||'b';
26 Elsif ( li_bytes < to_number('B4EE','xxxx')) Then
27 lv_temp:=lv_temp||'c';
28 Elsif ( li_bytes < to_number('B6EA','xxxx')) Then
29 lv_temp:=lv_temp||'d';
30 Elsif ( li_bytes < to_number('B7A2','xxxx')) Then
31 lv_temp:=lv_temp||'e';
32 Elsif ( li_bytes < to_number('B8C1','xxxx')) Then
33 lv_temp:=lv_temp||'f';
34 Elsif ( li_bytes < to_number('B9FE','xxxx')) Then
35 lv_temp:=lv_temp||'g';
36 Elsif ( li_bytes < to_number('BBF7','xxxx')) Then
37 lv_temp:=lv_temp||'h';
38 Elsif ( li_bytes < to_number('BFA6','xxxx')) Then
39 lv_temp:=lv_temp||'j';
40 Elsif ( li_bytes < to_number('C0AC','xxxx')) Then
41 lv_temp:=lv_temp||'k';
42 Elsif ( li_bytes < to_number('C2E8','xxxx')) Then
43 lv_temp:=lv_temp||'l';
44 Elsif ( li_bytes < to_number('C4C3','xxxx')) Then
45 lv_temp:=lv_temp||'m';
46 Elsif ( li_bytes < to_number('C5B6','xxxx')) Then
47 lv_temp:=lv_temp||'n';
48 Elsif ( li_bytes < to_number('C5BE','xxxx')) Then
49 lv_temp:=lv_temp||'o';
50 Elsif ( li_bytes < to_number('C6DA','xxxx')) Then
51 lv_temp:=lv_temp||'p';
52 Elsif ( li_bytes < to_number('C8BB','xxxx')) Then
53 lv_temp:=lv_temp||'q';
54 Elsif ( li_bytes < to_number('C8F6','xxxx')) Then
55 lv_temp:=lv_temp||'r';
56 Elsif ( li_bytes < to_number('CBFA','xxxx')) Then
57 lv_temp:=lv_temp||'s';
58 Elsif ( li_bytes < to_number('CDDA','xxxx')) Then
59 lv_temp:=lv_temp||'t';
60 Elsif ( li_bytes < to_number('CEF4','xxxx')) Then
61 lv_temp:=lv_temp||'w';
62 Elsif ( li_bytes < to_number('D1B9','xxxx')) Then
63 lv_temp:=lv_temp||'x';
64 Elsif ( li_bytes < to_number('D4D1','xxxx')) Then
65 lv_temp:=lv_temp||'y';
66 Elsif ( li_bytes < to_number('D7FA','xxxx')) Then
67 lv_temp:=lv_temp||'z';
68 Else
69 lv_temp:=lv_temp||'*';
70 End If;
71 End If;
72 End Loop;
73 Return upper(lv_temp);
74 end;
75 --hanjs,07-10-20,将汉字首子母进行返回。
76 /
Function created
SQL>
SQL> With x As (
2 Select '李云飞' Name, 'Li Yunfei' pi From dual Union All
3 Select '张飞虎', 'Zhang Feihu' From dual)
4 Select Name,f_hztopy(Name) From x
5 /
NAME F_HZTOPY(NAME)
------ --------------------------------------------------------------------------------
李云飞 LYF
张飞虎 ZFH
页:
[1]