DB2中創(chuàng)建一個(gè)獲取漢字拼音首字母的SQL函數(shù)
需求
有些時(shí)候我們會(huì)有這樣的需求，要求使用字母從a至z對(duì)一組數(shù)據(jù)進(jìn)行索引，如果數(shù)據(jù)的格式全部是半角的英文則很容易實(shí)現(xiàn)，但若是對(duì)一組中文數(shù)據(jù)進(jìn)行索引則會(huì)引起一點(diǎn)小的麻煩，數(shù)據(jù)在錄入數(shù)據(jù)庫(kù)的時(shí)候可能并沒有指定一個(gè)索引字母，這就要求應(yīng)用程序可以自動(dòng)生成用于索引的信息。
一般對(duì)于中文數(shù)據(jù)的索引，采用詞組的首漢字拼音的首字母，例如：
詞組 索引字母
－－－ －－－－－
熊貓 x
白暨豚 b
藏野驢 z
在DB2中并沒有提供相應(yīng)的函數(shù)可以取得漢字拼音的首字母，我們可以利用數(shù)據(jù)庫(kù)針對(duì)中文字符集的排序功能創(chuàng)建一個(gè)這樣的函數(shù)。
工作原理
我們知道在使用中文字符集的數(shù)據(jù)庫(kù)中，當(dāng)你對(duì)一列中文數(shù)據(jù)使用order by 排序時(shí)，排序的結(jié)果正是按照每行記錄第一個(gè)漢字的拼音首字母進(jìn)行排列的，那么我們需要想辦法取得這個(gè)字母。
但是數(shù)據(jù)庫(kù)內(nèi)部是如何做到這一點(diǎn)的呢？以中文字符集GBK為例，讓我們查看一下GBK字符集的內(nèi)碼表，我們僅摘出一段：
0 1 2 3 4 5 6 7 8 9 A B C D E F
B040 癅 癆 癇 癈 癉 癊 癋 癎 癏 癐 癑 癒 癓 癕 癗 癘
B050 癙 癚 癛 癝 癟 癠 癡 癢 癤 癥 癦 癧 癨 癩 癪 癬
B060 癭 癮 癰 癱 癲 癳 癴 癵 癶 癷 癹 発 發(fā) 癿 皀 皁
B070 皃 皅 皉 皊 皌 皍 皏 皐 皒 皔 皕 皗 皘 皚 皛
B080 皜 皝 皞 皟 皠 皡 皢 皣 皥 皦 皧 皨 皩 皪 皫 皬
B090 皭 皯 皰 皳 皵 皶 皷 皸 皹 皺 皻 皼 皽 皾 盀 盁
B0A0 盃 啊 阿 埃 挨 哎 唉 哀 皚 癌 藹 矮 艾 礙 愛 隘
B0B0 鞍 氨 安 俺 按 暗 岸 胺 案 骯 昂 盎 凹 敖 熬 翱
B0C0 襖 傲 奧 懊 澳 芭 捌 扒 叭 吧 笆 八 疤 巴 拔 跋
B0D0 靶 把 耙 壩 霸 罷 爸 白 柏 百 擺 佰 敗 拜 稗 斑
B0E0 班 搬 扳 般 頒 板 版 扮 拌 伴 瓣 半 辦 絆 邦 幫
B0F0 梆 榜 膀 綁 棒 磅 蚌 鎊 傍 謗 苞 胞 包 褒 剝
可以看到從B0A0-1 開始，至B0C0-5，是拼音A開頭的漢字，恰好是按照拼音字母的先后順序排列，并且把音調(diào)的因素也考慮進(jìn)去了，由此，可以推斷出，數(shù)據(jù)庫(kù)在GBk編碼的數(shù)據(jù)庫(kù)中對(duì)漢字進(jìn)行排序，即是依照字符內(nèi)碼表的編碼進(jìn)行的。
我們把B0C0-5 位置的漢字記錄下來(lái)，即“澳”字，這是以“a”拼音開頭在內(nèi)碼表中排列在最后的漢字，用同樣的方法，我們找出所有以拼音從b至z開頭，在內(nèi)碼表中排列在最后的漢字，與26個(gè)字母的對(duì)應(yīng)關(guān)系如下：
'澳' a
'怖' b
'錯(cuò)' c
'墮' d
'貳' e
'咐' f
'過' g
'禍' h
i
'駿' j
'闊' k
'絡(luò)' l
'穆' m
'諾' n
'漚' o
'瀑' p
'群' q
'弱' r
'所' s
'唾' t
u
v
'誤' w
'迅' x
'孕' y
'座 z
注：沒有以'i','u','v'開頭的漢語(yǔ)拼音。
現(xiàn)在假若我們拿出任何一個(gè)漢字，放在我們挑選出的這些漢字中間，利用數(shù)據(jù)庫(kù)進(jìn)行一次使用GBK字符集的排序，我們便能夠根據(jù)這個(gè)漢字排列的相對(duì)位置得到其拼音首字母。
利用sql語(yǔ)句生成一組上述漢字的結(jié)果集，我們將'i','u','v' 三個(gè)空缺漢字的位置補(bǔ)上了上一個(gè)拼音的漢字，
select t1.strChn
from ( select '澳' strChn from sysibm.sysdummy1
union all
select '怖' strChn from sysibm.sysdummy1
union all
select '錯(cuò)' 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 '絡(luò)' 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
) as t1
實(shí)現(xiàn)
接下來(lái)很方便的就可以寫出這個(gè)函數(shù)的具體實(shí)現(xiàn)，在實(shí)現(xiàn)的代碼中，我們又加入了針對(duì)英文字母的處理，函數(shù)編譯后，可通過如下方式調(diào)用：
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 '錯(cuò)' 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 '絡(luò)' 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@
實(shí)際使用中，應(yīng)注意建立數(shù)據(jù)庫(kù)時(shí)字符集參數(shù)的設(shè)置，應(yīng)使用GBK字符集。
應(yīng)用以下命令查看已建立數(shù)據(jù)庫(kù)的字符集：
db2 connect to db_name user user_name using password
db2 get db cfg | grep -i 'code set'
此參數(shù)在數(shù)據(jù)庫(kù)建立之后不能修改。
網(wǎng)公網(wǎng)安備