CREATE FUNCTION f_GetPY(@str nvarchar(4000))RETURNS nvarchar(4000)ASBEGIN 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)ENDGO
-----------------------------------------------------------方法二
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fGetPy]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[fGetPy] GO --创建取拼音函数 create function fGetPy(@Str varchar(500)='') returns varchar(500) as begin declare @strlen int,@return varchar(500),@ii int declare @n int,@c char(1),@chn nchar(1) select @strlen=len(@str),@return='',@ii=0 set @ii=0 while @ii<@strlen begin select @ii=@ii+1,@n=63,@chn=substring(@str,@ii,1) if @chn>'z' select @n = @n +1 ,@c = case chn when @chn then char(@n) else @c end from( select top 27 * from ( select chn = '吖' union all select '八' union all select '嚓' union all select '咑' union all select '妸' union all select '发' union all select '旮' union all select '铪' union all select '丌' --because have no 'i' union all select '丌' union all select '咔' union all select '垃' union all select '嘸' union all select '拏' union all select '噢' union all select '妑' union all select '七' union all select '呥' union all select '仨' union all select '他' union all select '屲' --no 'u' union all select '屲' --no 'v' union all select '屲' union all select '夕' union all select '丫' union all select '帀' union all select @chn) as a order by chn COLLATE Chinese_PRC_CI_AS ) as b else set @c='a' set @return=@return+@c end return(@return) end go --测试 select dbo.fgetpy('东莞市') as 东莞市,dbo.fgetpy('ab中c国人') as 中国人 --删除拼音函数 drop function fgetpy
