/*-1.-獲得漢字字符串的首字母
根據大力的貼子改成.將大力的兩個函數合并成了一個函數.
可以應用于助記碼的查詢
--*/
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 @[email protected]+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 @[email protected][email protected]
end
return(@return)
end
go
--測試
select dbo.fgetpy('東莞市') as 東莞市,dbo.fgetpy('ab中c國人') as 中國人
--刪除拼音函數
drop function fgetpy
/*2.--獲得漢字拼音的函數
需要創建一個拼音表,包含所有漢字的發音,這個可以通過轉換全拼輸入法的編碼庫得到,這里僅舉了一個簡單的例子.
--*/
--創建漢字拼音庫
create table yingshe(chr char(2),py varchar(10))
insert yingshe
select '長','chang'
union all select '長','zhang'
union all select '城','cheng'
union all select '科','kel'
union all select '技','ji'
union all select '金','jin'
union all select '立','li'
union all select '章','zhang'
union all select '公','gong'
union all select '司','si'
/*--下面是兩個函數,一個以表的形式返回某個字符串的全部拼音,一個返回某某個字符串的其中一個拼音
--*/
--獲取漢字拼音的函數--返回所有的拼音
create function f_getpy_tb(@str varchar(100))
returns @tb table(re varchar(8000))
as
begin
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 @[email protected]+1
insert into @re select @i,[email protected]+py from @re a,yingshe b
where [email protected] and b.chr=substring(@str,@i,1)
end
insert into @tb select re from @re where [email protected]
return
end
go
--獲取漢字拼音的函數--返回漢字的某一個拼音
create function f_getpy(@str varchar(100))
returns varchar(8000)
as
begin
declare @re varchar(8000)
declare @i int,@ilen int,@splitchr varchar(1)
select @splitchr=' ' --兩個拼音之間的分隔符(目的是為了通用性考慮)
,@i=1,@ilen=len(@str)
select @re=py from yingshe where chr=substring(@str,@i,1)
while @i<@ilen
begin
set @[email protected]+1
select top 1 @[email protected][email protected]+py
from yingshe where chr=substring(@str,@i,1)
end
return(@re)
end
go
--測試
--返回'長城'的所有可能拼音
select * from dbo.f_getpy_tb('長城')
--返回'長城'的拼音
select dbo.f_getpy('長城')
--刪除拼音函數
drop function f_getpy,f_getpy_tb
