Copyright Notice: This article can be reproduced, please be sure to indicate the original source and author information in hyperlink when reprint. Original Source: http://www.aiview.com/notes/index_of_chinese.htm Author: Zhang Yang Alex_doesAThotmail.com Last updated: 2003-12-25
table of Contents
Reference resources
demand
Sometimes we will have such a need, requiring letters to index a set of data from A to z, and if the format of the data is very easy to implement, if it is an index of a set of Chinese data, it will cause a little Small trouble, data may not specify an index letter when you enter the database, which requires the application to automatically generate information for indexing.
Generally, for the index of Chinese data, the first letters of the phrase of the phrase of the phrase, for example: phrase index letters ------- Panda x white cub Polfall B Tibetan zone does not provide the corresponding function in DB2 Take the first letter of Chinese characters, we can use the database to create such a function for the sorting function of the Chinese character set.
working principle
We know that in the database using the Chinese character set, when you use the ORDER BY sort by the Chinese data, the result is in accordance with the first letter of the first Chinese character, then we need to find a way to get the way. This letter is. But how do the database do this? Take the Chinese character set GBK as an example, let's take a look at the internal code table of the GBK character set, we only take a section: 0 1 2 3 4 5 6 7 8 9 ABCDEF B040 Epilepsy 癈 瘅 癊 癋 癎癏 癐 癑 癓 癓 癓癕癗 疠 B050 癙癚 癛癝 癠 癠 癠 疖 疖 癦疬 癨 癞 癪 癪 癪 癳 癳 癳 癴 癵癶癷 癵癶癷 癵癶癷 発 癳 癳 癳 癳 癳 癴 癳 癳 癳 癳 癳 癳 癳 癳 癳 癳 癳 癳 癳 癳 癳 癳 皃皅 癳 皃皅 癳 皂皘 皛 B080 皞 皟 皠 皡 皢 皣 皥 皦 皧 皨 皩 皪 皫皬 B090 皭皯 皳皵 皳皵 皶 皲 皲 皹 皻皼 皽 皾 盀盁 B0A0 Cup Aue 挨唉 Sick Corporation Ai Ai Ai-Angi B0B0 saddle ammonia 俺 Press the mine oxamine case 盎 熬 熬 熬 熬 B0C0 proud 懊 捌 捌 捌 吧 八 巴 跋 跋 B0D0 Target 把 耙 霸 爸 爸 白 柏 百 摆 摆 摆败 稗 稗 稗 板 版 搬 扳 版 版 版 帮 帮 帮 帮 帮 帮 帮 帮 褒 褒 褒 褒 褒 褒 褒 褒 褒 褒 褒 褒 褒 褒 褒 褒 褒 褒 褒 褒 褒 褒 褒5. It is the Chinese character starting in pinyin A. It happens in the order of the pinyin letters, and the factors of the tone are also taken into account, whereby it can be inferred that the database is sorted in the database of GBK encoded, ie According to the encoding of the character internal code table. We recorded the Chinese characters of the B0C0-5 position, ie "Australia" word, this is the final Chinese character in the internal code table with "a" pinyin, using the same method, we find all the pinyin from b to The beginning of Z, in the internal code table in the final Chinese character, the correspondence between 26 letters is as follows: '' a 'horce' b 'wrong' c '' D '' E '' f ' G 'curse' hi 'Jun' J 'Broad' K '' L 'Mo' M 'No' N '沤' O 'Waterfall' P 'Group' Q 'Weak' R '' s' Saka 'TUV' Mistake 'W' X 'X' Pregnancy 'Y' Block Z Note: There is no Chinese pinyin starting with 'i', 'u', 'V'. Now, if we take any of the Chinese characters, put it in the middle of these Chinese characters we pick, use the database to use the GBK character set, we can get its pinyin in the relative position of this Chinese character.
Use the SQL statement to generate a set of the result set of the above Chinese characters, we will replenish the word 'i', 'u', 'v' three vacant Chinese characters, SELECT T1.STRCHN FROM (SELECT 'Australian) strChn from sysibm.sysdummy1 union all select 'terror' strChn from sysibm.sysdummy1 union all select 'wrong' strChn from sysibm.sysdummy1 union all select 'fall' strChn from sysibm.sysdummy1 union all select 'II' strChn from sysibm.sysdummy1 union all select 'commanded' strChn from sysibm.sysdummy1 union all select 'through' strChn from sysibm.sysdummy1 union all select 'disaster' strChn from sysibm.sysdummy1 union all select 'disaster' strChn from sysibm.sysdummy1 union all select 'Jun' strChn from sysibm.sysdummy1 union all select 'wide' strChn from sysibm.sysdummy1 union all select 'envelope' strChn from sysibm.sysdummy1 union all select 'Mu' strChn from sysibm.sysdummy1 union all select 'Novo' strChn from sysibm.sysdummy1 union all select 'ret' strChn from sysibm.sysdummy1 union all select 'waterfall' strChn from sysibm.sysdummy1 union all select 'group' strChn from sysibm.sysdummy1 union all select 'weak' strChn from sysibm.sys dummy1 union all select 'the' strChn from sysibm.sysdummy1 union all select 'saliva' strChn from sysibm.sysdummy1 union all select 'saliva' strChn from sysibm.sysdummy1 union all select 'saliva' strChn from sysibm.sysdummy1 union all select 'bad 'strChn from sysibm.sysdummy1 union all select' fast 'strChn from sysibm.sysdummy1 union all select' pregnant 'strChn from sysibm.sysdummy1 union all select' seat 'strChn from sysibm.sysdummy1) as t1 implemented
Next, you can write the specific implementation of this function. In the implemented code, we added the processing of English letters. After the function is compiled, it can be called as follows: SELECT getIndex ('cable') Index from Dual; Index ------ f Download Code In active use, pay attention to the setting of the character set parameter when establishing the database, should use the GBK character set. Apply the following command to view the character set of established databases: DB2 Connect to DB_NAME User_name Using Password DB2 Get DB CFG | Grep -i 'Code Set' This parameter cannot be modified after the database is established.
Reference resource
Download Function Source Code GBK Character Set Code Table: http://www.khngai.com/chinese/charmap/tblgbk.php?page=0
