SQLSERVER中将人命解析成拼音的函数

最新推荐文章于 2024-05-15 15:45:50 发布

最新推荐文章于 2024-05-15 15:45:50 发布 · 208 阅读

文章标签：

#F# #C #C++ #C# #Go

SQLSERVER技术专栏收录该内容

31 篇文章

订阅专栏

本文介绍两个SQL函数：一是获取中文姓名首字母，适用于姓名拼音缩写的快速生成；二是实现中文姓名到完整拼音的转换，方便数据库中中文字段的拼音检索。

1.第一个函数是取得姓名每个中文汉字的第一个字母

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

--用法
select dbo.f_GetPY('吴蔚玲') as 东莞市,dbo.f_GetPY('ab中c国人') as 中国人

--将已有表中的某个字段更新成拼音字段

select name,pinyin from person_main_test

update person_main_test set PinYin=dbo.f_GetPY(Name)

2.获得整个中文名字的拼音

create   function   f_getpyALL(@str varchar(100))
returns   varchar(8000)
as
begin
declare @returnValue varchar(8000)
declare   @re   table(id   int,re   varchar(8000))

declare   @i   int,@ilen   int,@splitchr   varchar(1)
select   @splitchr='' ,@i=1,@ilen=len(@str)
insert   into   @re   select   @i,py   from   YingShe   where   chr=substring(@str,@i,1)
while   @i<@ilen
   begin
   set   @i=@i+1
   insert   into   @re   select   @i,re+@splitchr+py   from   @re   a,YingShe   b
   where   a.id=@i-1   and   b.chr=substring(@str,@i,1)
   end
    select @returnValue= re   from   @re   where   id=@i
    return (@returnValue)
end