mysql汉字首字母_mysql汉字首字母

系统：win 2003  mysql版本：mysql-5.6.17-win32   数据库编码：UTF8

首先在数据库中创建汉字字母对照表

drop table if exists pyk;

/*==============================================================*/

/* Table: pyk */

/*==============================================================*/

create table pyk

(

PY varchar(1) not null,

HZ1 int not null,

HZ2 int not null

);

INSERT INTO `pyk` (`PY`,`HZ1`,`HZ2`) VALUES

('A',-20319,-20284),

('B',-20283,-19776),

('C',-19775,-19219),

('D',-19218,-18711),

('E',-18710,-18527),

('F',-18526,-18240),

('G',-18239,-17923),

('H',-17922,-17418),

('J',-17417,-16475),

('K',-16474,-16213),

('L',-16212,-15641),

('M',-15640,-15166),

('N',-15165,-14923),

('O',-14922,-14915),

('P',-14914,-14631),

('Q',-14630,-14150),

('R',-14149,-14091),

('S',-14090,-13319),

('T',-13318,-12839),

('W',-12838,-12557),

('X',-12556,-11848),

('Y',-11847,-11056),

('Z',-11055,-10247);

然后，创建mysql函数

drop function if exists FGetPY;

create function FGetPY

(

s CHAR(255)

)

RETURNS varchar(255)

begin

declare m_len int;

declare i int;

declare m_return varchar(50);

DECLARE hz_code int;

DECLARE hz_py char;

declare str varchar(400);

set i=1;

set m_len=char_length(s);

set m_return='';

while i <= m_len do

set hz_py = '';

SET hz_code = ord(convert(substring(MID(s,i,1),1,1) using gbk))-65536 ;

select py into hz_py from pyk where hz_code>=pyk.hz1 and hz_code<=pyk.hz2;

if hz_py <> '' then

set m_return = concat(m_return,hz_py);

ELSE

set m_return = concat(m_return,substring(MID(s,i,1),1,1));

end if;

set i=i+1;

end while;

return m_return;

end;

测试

select FGetPY('好土8a')

结果：

HT8a