DB2 to create a SQL function to get the first letter of Chinese pinyin

There are times when we need to index a set of data using letters from A to Z. If the data format is all half-width English is easy to achieve, but if the index of a group of Chinese data will cause a little trouble, data input database may not have specified an index letter, This requires the application to automatically generate information for indexing.

In general, the index of Chinese data uses the first letter of the Chinese pinyin of the phrase, for example:

Phrase Index letter

－－－ －－－－－

Panda x

Dolphin b

Tibetan Wild Donkey Z

The DB2 does not provide a function to get the first letter of Chinese pinyin, and we can use the database to create a function for the sorting function of the Chinese character set.

Working principle

We know that in a database using the Chinese character set, when you use order by to sort a list of Chinese data, the results of the order are arranged according to the phonetic initials of the first Chinese character in each line, so we need to find a way to get the letter.

But how does this happen inside the database? Take the Chinese character set GBK For example, let's take a look at the Inner Code table of the GBK character set, and we'll just pick a paragraph:

0 1 2 3 4 5 6 7 8 A B C D E F

B040 癅 tuberculosis, 癈 瘅 癊 Sham eclampsia Wilson 癐 癑 Healing 癓 癕 癗 Pandora

B050 癙 癚 癛 癝 The itch, 癠, 癦

B060 Gall Addiction carbuncle paralysis 癳 癴 癵 癶 癷 癹 発 hair saponin Cloaks

B070 皃 皅 皉 皊 drives 皍 皏 An inbound I 皔 皕 皗 皘 ai 皛

B080 皜 皝 皞 皟 皠 皡 皢 皣 皥 皦 皧 皨 皩 皪 皫 皬

B090 皭 皯 疱 皳 皵 皶 皷 皲 皹 皱 隳 皼 皽 皾 盀 盁

B0A0 Cup Ah Ae, alas ai cancer io ai

B0b0 I press the dark shore amine case to be an Anglo-concave Alang

b0c0 ao regretful ao ba ba ba ba scar ba

B0d0 the target to rake the dam PA strike the Father Baiber hundred throw Bai to worship the barnyard spot

The B0e0 class moves the plate to play the board version to mix with the flap half to run trip the state to help

B0f0, bang bang, pound clam pound, and the shell of the slander.