sql实现取汉字大写首字母

最新推荐文章于 2024-07-30 12:38:29 发布

weixin_30312557

最新推荐文章于 2024-07-30 12:38:29 发布

阅读量235

点赞数

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/SoftWareIe/p/6709188.html

本文介绍了一个SQL Server中自定义的函数f_GetPY，该函数能够将输入的汉字字符串转换为对应的拼音首字母缩写形式。通过预先定义好的拼音与汉字对应表，此函数实现了对特定范围内的汉字进行拼音首字母转换的功能。

create   FUNCTION   [dbo].[f_GetPY](@str nvarchar(4000)) 
RETURNS   nvarchar(4000) 
AS 
BEGIN 
DECLARE   @py   TABLE( 
ch   char(1), 
hz1   nchar(1)   COLLATE   Chinese_PRC_CS_AS_KS_WS, 
hz2   nchar(1)   COLLATE   Chinese_PRC_CS_AS_KS_WS) 
INSERT   @py   SELECT   'A','吖','鏊' 
UNION     ALL   SELECT  'B','八','簿' 
UNION     ALL   SELECT  'C','嚓','错' 
UNION     ALL   SELECT  'D','哒','跺' 
UNION     ALL   SELECT  'E','屙','贰' 
UNION     ALL   SELECT  'F','发','馥' 
UNION     ALL   SELECT  'G','旮','过' 
UNION     ALL   SELECT  'H','铪','蠖' 
UNION     ALL   SELECT  'J','丌','竣' 
UNION     ALL   SELECT  'K','咔','廓' 
UNION     ALL   SELECT  'L','垃','雒' 
UNION     ALL   SELECT  'M','妈','穆' 
UNION     ALL   SELECT  'N','拿','糯' 
UNION     ALL   SELECT  'O','噢','沤' 
UNION     ALL   SELECT  'P','趴','曝' 
UNION     ALL   SELECT  'Q','七','群' 
UNION     ALL   SELECT  'R','蚺','箬' 
UNION     ALL   SELECT  'S','仨','锁' 
UNION     ALL   SELECT  'T','他','箨' 
UNION     ALL   SELECT  'W','哇','鋈' 
UNION     ALL   SELECT  'X','夕','蕈' 
UNION     ALL   SELECT  'Y','丫','蕴' 
UNION     ALL   SELECT  'Z','匝','做' 
DECLARE   @i   int 
SET   @i=PATINDEX('%[吖-做]%'   COLLATE   Chinese_PRC_CS_AS_KS_WS,@str) 
WHILE   @i> 0 
SELECT   @str=REPLACE(@str,SUBSTRING(@str,@i,1),ch) 
,@i=PATINDEX('%[吖-做]%'   COLLATE   Chinese_PRC_CS_AS_KS_WS,@str) 
FROM   @py 
WHERE   SUBSTRING(@str,@i,1)   BETWEEN   hz1   AND   hz2 
RETURN(@str) 
END