--可用，速度可以
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 * from dbo.T_CITY_INFO
where dbo.fGetPy(cy_name)
='sh'
速度太慢
--2. 汉字首字母查询处理用户定义函数
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
转载于:https://www.cnblogs.com/goto/archive/2012/04/12/2443346.html
相关资源：sql 汉字转拼音首字母代码
