<script type="text/javascript">
</script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
/*-1.-获得汉字字符串的首字母
根据大力的贴子改成.将大力的两个函数合并成了一个函数.
可以应用于助记码的查询
--*/
ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[fGetPy]')andxtypein(N'FN',N'IF',N'TF'))
dropfunction[dbo].[fGetPy]
GO
--创建取拼音函数
createfunctionfGetPy(@Strvarchar(500)='')
returnsvarchar(500)
as
begin
declare@strlenint,@returnvarchar(500),@iiint
declare@nint,@cchar(1),@chnnchar(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=casechnwhen@chnthenchar(@n)else@cend
from(
selecttop27*from(
selectchn='吖'
unionallselect'八'
unionallselect'嚓'
unionallselect'咑'
unionallselect'妸'
unionallselect'发'
unionallselect'旮'
unionallselect'铪'
unionallselect'丌' --becausehaveno'i'
unionallselect'丌'
unionallselect'咔'
unionallselect'垃'
unionallselect'呒'
unionallselect'拏'
unionallselect'噢'
unionallselect'妑'
unionallselect'七'
unionallselect'呥'
unionallselect'仨'
unionallselect'他'
unionallselect'屲' --no'u'
unionallselect'屲' --no'v'
unionallselect'屲'
unionallselect'夕'
unionallselect'丫'
unionallselect'帀'
unionallselect@chn)asa
orderbychnCOLLATEChinese_PRC_CI_AS
)asb
elseset@c='a'
set@return=@return+@c
end
return(@return)
end
go
--测试
selectdbo.fgetpy('东莞市')as东莞市,dbo.fgetpy('ab中c国人')as中国人
--删除拼音函数
dropfunctionfgetpy
/*2.--获得汉字拼音的函数
需要创建一个拼音表,包含所有汉字的发音,这个可以通过转换全拼输入法的编码库得到,这里仅举了一个简单的例子.
--*/
--创建汉字拼音库
createtableYingShe(CHR char(2),PYvarchar(10))
insertYingShe
select'长','chang'
unionallselect'长','zhang'
unionallselect'城','cheng'
unionallselect'科','kel'
unionallselect'技','ji'
unionallselect'金','jin'
unionallselect'立','li' 1 <script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
<script type="text/javascript">
</script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
/*-1.-获得汉字字符串的首字母
根据大力的贴子改成.将大力的两个函数合并成了一个函数.
可以应用于助记码的查询
--*/
ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[fGetPy]')andxtypein(N'FN',N'IF',N'TF'))
dropfunction[dbo].[fGetPy]
GO
--创建取拼音函数
createfunctionfGetPy(@Strvarchar(500)='')
returnsvarchar(500)
as
begin
declare@strlenint,@returnvarchar(500),@iiint
declare@nint,@cchar(1),@chnnchar(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=casechnwhen@chnthenchar(@n)else@cend
from(
selecttop27*from(
selectchn='吖'
unionallselect'八'
unionallselect'嚓'
unionallselect'咑'
unionallselect'妸'
unionallselect'发'
unionallselect'旮'
unionallselect'铪'
unionallselect'丌' --becausehaveno'i'
unionallselect'丌'
unionallselect'咔'
unionallselect'垃'
unionallselect'呒'
unionallselect'拏'
unionallselect'噢'
unionallselect'妑'
unionallselect'七'
unionallselect'呥'
unionallselect'仨'
unionallselect'他'
unionallselect'屲' --no'u'
unionallselect'屲' --no'v'
unionallselect'屲'
unionallselect'夕'
unionallselect'丫'
unionallselect'帀'
unionallselect@chn)asa
orderbychnCOLLATEChinese_PRC_CI_AS
)asb
elseset@c='a'
set@return=@return+@c
end
return(@return)
end
go
--测试
selectdbo.fgetpy('东莞市')as东莞市,dbo.fgetpy('ab中c国人')as中国人
--删除拼音函数
dropfunctionfgetpy
/*2.--获得汉字拼音的函数
需要创建一个拼音表,包含所有汉字的发音,这个可以通过转换全拼输入法的编码库得到,这里仅举了一个简单的例子.
--*/
--创建汉字拼音库
createtableYingShe(CHR char(2),PYvarchar(10))
insertYingShe
select'长','chang'
unionallselect'长','zhang'
unionallselect'城','cheng'
unionallselect'科','kel'
unionallselect'技','ji'
unionallselect'金','jin'
unionallselect'立','li' 1 <script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
<script type="text/javascript">
</script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>