DB2中创建一个获取汉字拼音首字母的SQL函数

核心提示： 实现 接下来很方便的就可以写出这个函数的具体实现，在实现的代码中，DB2中创建一个获取汉字拼音首字母的SQL函数(4)，我们又加入了针对英文字母的处理，函数编译后，可通过如下方式调用： select getIndex( ’索’ ) index from dual;i

实现

接下来很方便的就可以写出这个函数的具体实现，在实现的代码中，我们又加入了针对英文字母的处理，函数编译后，可通过如下方式调用：

select getIndex( ’索’ ) index from dual;
index
------
f

原代码如下：

create function getIndex (
in_strChn varchar(2)
) returns char(1)
language sql
external action
reads sql data
begin atomic
declare chresult char(1);
declare n integer default 0;
if( in_strChn = ’’ or in_strChn is null or lengthb( in_strChn ) > 2 ) then
return null;
end if;
if(( ascii( in_strChn ) >= ascii(’A’) and ascii( in_strChn ) <= ascii(’Z’) )
or ( ascii( in_strChn ) >= ascii(’a’) and ascii( in_strChn ) <= ascii(’z’)) ) then
return lcase( substr( in_strChn, 1, 1 ) );
end if;
for myloop as
select t2.strChn
from ( select t1.strChn
from ( select ’澳’ strChn from sysibm.sysdummy1
union all
select ’怖’ strChn from sysibm.sysdummy1
union all
select ’错’ strChn from sysibm.sysdummy1
union all
select ’堕’ strChn from sysibm.sysdummy1
union all
select ’贰’ strChn from sysibm.sysdummy1
union all
select ’咐’ strChn from sysibm.sysdummy1
union all
select ’过’ strChn from sysibm.sysdummy1
union all
select ’祸’ strChn from sysibm.sysdummy1
union all
select ’祸’ strChn from sysibm.sysdummy1
union all
select ’骏’ strChn from sysibm.sysdummy1
union all
select ’阔’ strChn from sysibm.sysdummy1
union all
select ’络’ strChn from sysibm.sysdummy1
union all
select ’穆’ strChn from sysibm.sysdummy1
union all
select ’诺’ strChn from sysibm.sysdummy1
union all
select ’沤’ strChn from sysibm.sysdummy1
union all
select ’瀑’ strChn from sysibm.sysdummy1
union all
select ’群’ strChn from sysibm.sysdummy1
union all
select ’弱’ strChn from sysibm.sysdummy1
union all
select ’所’ strChn from sysibm.sysdummy1
union all
select ’唾’ strChn from sysibm.sysdummy1
union all
select ’唾’ strChn from sysibm.sysdummy1
union all
select ’唾’ strChn from sysibm.sysdummy1
union all
select ’误’ strChn from sysibm.sysdummy1
union all
select ’迅’ strChn from sysibm.sysdummy1
union all
select ’孕’ strChn from sysibm.sysdummy1
union all
select ’座’ strChn from sysibm.sysdummy1
union all
select in_strChn strChn from sysibm.sysdummy1
) as t1
order by t1.strChn
) as t2
do
if ( strChn = in_strChn ) then
set chresult = chr( ascii(’a’) + ( case n when 26 then n-1 else n end ) );
return chresult;
end if;
set n = n + 1;
end for;
return chresult;
end@

实际使用中，应注意建立数据库时字符集参数的设置，应使用GBK字符集。

应用以下命令查看已建立数据库的字符集：

db2 connect to db_name user user_name using password

db2 get db cfg | grep -i ’code set’

此参数在数据库建立之后不能修改。