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