--//下面的stored procedure 是获得汉字字符串的首字母
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[udf_GetFirst_Py]') and xtype in (N'FN', N'IF',N'TF'))
drop function [dbo].[udf_GetFirst_Py]
GO
Create function udf_GetFirst_Py
(@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
--//确认输入不为空
if @strlen=0
begin
select @return='Please input chatacter!'
return(@return)
end
--//循环整个字符串,用拼音的首字母替换汉字
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 '丌'
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 '屲'
union all
select '屲'
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=@chn
set @return=@return+@c
end
return(@return)
end
go
--//测试
select dbo.udf_GetFirst_Py('上海市') ,dbo.udf_GetFirst_Py('ab相同test读音')
,dbo.udf_GetFirst_Py('')
"王挺" wrote in message