根据英文首字母来查询汉字函数 改成MYSQL的。create function fGetPy(@Str varchar(500)='')returns varchar(500)asbegindeclare @strlen int,@return varchar(500),@ii intdeclare @n int,@c char(1),@chn nchar(1)select @strlen=len(@str),@return='',@ii=0set @ii=0while @ii<@strlenbeginselect @ii=@ii+1,@n=63,@chn=substring(@str,@ii,1)if @chn>'z'select @n = @n +1,@c = case chn when @chn then char(@n) else @c endfrom(select top 27 * from (select chn = '吖'union all select '八'union all select '嚓'union all select '咑'union all select '妸'union all select '发'union all select '旮'union all select '铪'union all select '丌' --because have no 'i'union all select '丌'union all select '咔'union all select '垃'union all select '嘸'union all select '拏'union all select '噢'union all select '妑'union all select '七'union all select '呥'union all select '仨'union all select '他'union all select '屲' --no 'u'union all select '屲' --no 'v'union all select '屲'union all select '夕'union all select '丫'union all select '帀'union all select @chn) as aorder by chn COLLATE Chinese_PRC_CI_AS) as belse set @c='a'set @return=@return+@cendreturn(@return)end
-> ELT(INTERVAL(CONV(HEX(left(A_UserName,1)),16,10),
-> 0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,0
xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,0xC8F6,0xCBFA,0xCDDA,0xCE
F4,0xD1B9,0xD4D1),
-> 'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P','Q',
'R','S','T','W','X','Y','Z') as PY
-> from o_personnel;
+------+------------+------+
| A_Id | A_UserName | PY |
+------+------------+------+
| 1 | 首先 | S |
| 2 | 检查 | J |
| 3 | 我们 | W |
| 4 | 的二 | D |
| 5 | 进制 | J |
| 6 | 是否 | S |
| 7 | 适合 | S |
| 8 | 你的 | N |
| 9 | 平台 | P |
+------+------------+------+
9 rows in set (0.00 sec)mysql>
-> CHAR(INTERVAL(CONV(HEX(left(A_UserName,1)),16,10),
-> 0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,
-> 0xBBF7,0xBBF7,0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,
-> 0xC6DA,0xC8BB,0xC8F6,0xCBFA,0xCDDA,0xCDDA,0xCDDA,0xCEF4,
-> 0xD1B9,0xD4D1)+64) as PY
-> from o_personnel;
+------+------------+------+
| A_Id | A_UserName | PY |
+------+------------+------+
| 1 | 首先 | S |
| 2 | 检查 | J |
| 3 | 我们 | W |
| 4 | 的二 | D |
| 5 | 进制 | J |
| 6 | 是否 | S |
| 7 | 适合 | S |
| 8 | 你的 | N |
| 9 | 平台 | P |
+------+------------+------+
9 rows in set (0.00 sec)mysql>