SQL汉字首字母查询处理用户定义函数

最新推荐文章于 2024-03-11 11:18:36 发布

DillonHunter

最新推荐文章于 2024-03-11 11:18:36 发布

阅读量1.2k

点赞数

分类专栏：资料库文章标签： sql insert function table go c

本文链接：https://blog.csdn.net/DillonHunter/article/details/711661

版权

资料库专栏收录该内容

14 篇文章

订阅专栏

本文介绍了一个SQL Server中自定义的函数f_GetPY，该函数能够将输入的汉字字符串转换为对应的拼音首字母缩写形式。通过预先定义好的拼音与汉字对应表实现转换，并使用PATINDEX与SUBSTRING进行匹配和替换。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

CREATE FUNCTION 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',N'吖',N'鏊'
UNION ALL SELECT 'B',N'八',N'簿'
UNION ALL SELECT 'C',N'嚓',N'错'
UNION ALL SELECT 'D',N'哒',N'跺'
UNION ALL SELECT 'E',N'屙',N'贰'
UNION ALL SELECT 'F',N'发',N'馥'
UNION ALL SELECT 'G',N'旮',N'过'
UNION ALL SELECT 'H',N'铪',N'蠖'
UNION ALL SELECT 'J',N'丌',N'竣'
UNION ALL SELECT 'K',N'咔',N'廓'
UNION ALL SELECT 'L',N'垃',N'雒'
UNION ALL SELECT 'M',N'妈',N'穆'
UNION ALL SELECT 'N',N'拿',N'糯'
UNION ALL SELECT 'O',N'噢',N'沤'
UNION ALL SELECT 'P',N'趴',N'曝'
UNION ALL SELECT 'Q',N'七',N'群'
UNION ALL SELECT 'R',N'蚺',N'箬'
UNION ALL SELECT 'S',N'仨',N'锁'
UNION ALL SELECT 'T',N'他',N'箨'
UNION ALL SELECT 'W',N'哇',N'鋈'
UNION ALL SELECT 'X',N'夕',N'蕈'
UNION ALL SELECT 'Y',N'丫',N'蕴'
UNION ALL SELECT 'Z',N'匝',N'做'
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
GO