mysql按首字母查询汉字,mysql 根据英文首字母来查询汉字函数

gbk编码：

mysql> SELECT *,

->  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>

utf8编码:

SELECT filename ,

ELT(INTERVAL(CONV(HEX(left(CONVERT(filename USING gbk),1)),16,10),

0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,

0xBFA6,0xC0AC,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,0xC8F6,

0xCBFA,0xCDDA,0xCEF4,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 youtable