今天碰到了取拼音首字母的需求。整理了一下。1、编码表:/*DDL Information For - test.cs_char2letter*/------------------------------------------------------Table Create Table -------------- ---------------------------------------------cs_char2letter CREATE TABLE `cs_char2letter` ( `PY` char(1) character set utf8 NOT NULL, `HZ` char(1) NOT NULL default '', PRIMARY KEY (`PY`) ) ENGINE=InnoDB DEFAULT CHARSET=gbk 2、记录信息:query result(23 records)
PY |
HZ |
A |
骜 |
B |
簿 |
C |
错 |
D |
鵽 |
E |
樲 |
F |
鳆 |
G |
腂 |
H |
夻 |
J |
攈 |
K |
穒 |
L |
鱳 |
M |
旀 |
N |
桛 |
O |
沤 |
P |
曝 |
Q |
囕 |
R |
鶸 |
S |
蜶 |
T |
箨 |
W |
鹜 |
X |
鑂 |
Y |
韵 |
Z |
咗 |
3、取拼音首字母的函数:DELIMITER $$DROP FUNCTION IF EXISTS `test`.`func_get_first_letter`$$CREATE DEFINER=`root`@`localhost` FUNCTION `func_get_first_letter`( words varchar(255)) RETURNS char(1) CHARSET utf8BEGIN declare fpy char(1); declare pc char(1); declare cc char(4); set @fpy = UPPER(left(words,1)); set @pc = (CONVERT(@fpy USING gbk)); set @cc = hex(@pc); if @cc >= "8140" and @cc <="FEA0" then begin select PY from cs_char2letter where hz>=@pc limit 1 into @fpy; end; end if; Return @fpy; END$$DELIMITER ;4、测试结果:
select func_get_first_letter('我是月亮'),func_get_first_letter('月亮是我'),func_get_first_letter('他爸也是我');
|
query result(1 records)
func_get_first_letter('我是月亮') |
func_get_first_letter('月亮是我') |
func_get_first_letter('他爸也是我') |
W |
Y |
T |
5、附上表插表语句:truncate table cs_char2letter;set names utf8;insert into cs_char2letter values('A','骜'),('B','簿'),('C','错'),('D','鵽'),('E','樲'),('F','鳆'),('G','腂'),('H','夻'),('J','攈'),('K','穒'),('L','鱳'),('M','旀'),('N','桛'),('O','沤'),('P','曝'),('Q','囕'),('R','鶸'),('S','蜶'),('T','箨'),('W','鹜'),('X','鑂'),('Y','韵'),('Z','咗');
本文转自 david_yeung 51CTO博客,原文链接:http://blog.51cto.com/yueliangdao0608/81271,如需转载请自行联系原作者