create table FirstSpell ( Spell nvarchar(1) collate Chinese_PRC_CI_AS, Word nvarchar(1) collate Chinese_PRC_CS_AS_KS_WS, constraint PK_FirstSpell primary key clustered(Spell,Word) ) goinsert into FirstSpell select 'A', N'驁' union all select 'B', N'簿' union all select 'C', N'錯' union all select 'D', N'鵽' union all select 'E', N'樲' union all select 'F', N'鰒' union all select 'G', N'腂' union all select 'H', N'夻' union all select 'J', N'攈' union all select 'K', N'穒' union all select 'L', N'鱳' union all select 'M', N'旀' union all select 'N', N'桛' union all select 'O', N'漚' union all select 'P', N'曝' union all select 'Q', N'囕' union all select 'R', N'鶸' union all select 'S', N'蜶' union all select 'T', N'籜' union all select 'W', N'鶩' union all select 'X', N'鑂' union all select 'Y', N'韻' union all select 'Z', N'咗' gocreate function fn_Spell(@Input nvarchar(4000)) returns nvarchar(4000) as begin declare @Spell nvarchar(4000), @Word nchar(1) set @Spell='' while len(@Input)>0 begin set @Word=left(@Input,1) set @Spell=@Spell+(case when unicode(@Word) between 19968 and 40869 then (select top 1 Spell from FirstSpell where Word>=@Word order by Spell) else @Word end) set @Input=right(@Input,len(@Input)-1) end return @Spell end /* 函数调用: select dbo.fn_Spell('中华民族'),dbo.fn_Spell('中華民族') */ 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) endgo --测试 select dbo.fgetpy('魏保光') as 姓名拼音,dbo.fgetpy('ab中c国人') as 中国人 select dbo.fgetpy('刘子良') as 姓名拼音,dbo.fgetpy('ab中c国人') as 中国人 select dbo.fgetpy('吴过') as 姓名拼音,dbo.fgetpy('ab中c国人') as 中国人 select dbo.fgetpy('东北') as 姓名拼音,dbo.fgetpy('ab中c国人') as 中国人 --删除拼音函数 drop function fgetpy
--创建汉字拼音库 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'
go --获取汉字拼音的函数--返回所有的拼音 create function f_getpy_tb(@str varchar(100)) returns @tb table(re varchar(8000)) as begin declare @re table(id int,re varchar(8000)) --数据处理中间表
insert into @re select @i,py from YingShe where chr=substring(@str,@i,1) while @i<@ilen begin set @i=@i+1 insert into @re select @i,re+@splitchr+py from @re a,YingShe b where a.id=@i-1 and b.chr=substring(@str,@i,1) end
insert into @tb select re from @re where id=@i return end go
--获取汉字拼音的函数--返回汉字的某一个拼音 create function f_getpy(@str varchar(100)) returns varchar(8000) as begin declare @re varchar(8000)
select @re=py from YingShe where chr=substring(@str,@i,1) while @i<@ilen begin set @i=@i+1 select top 1 @re=@re+@splitchr+py from YingShe where chr=substring(@str,@i,1) end
return(@re) end go
--测试 --返回'长城'的所有可能拼音 select * from dbo.f_getpy_tb('长城')
--应用案例:
create table MyTable(Name varchar(10),ID int) insert MyTable select '长城科技',1 union all select '金长城科技',2 union all select '立章城公司',3
go
--查询包含与'长城'拼音一样的记录: select * from mytable where exists(select 1 from dbo.f_getpy_tb('长城') where dbo.f_getpy(name) like '%'+re+'%')
--删除表 drop table YingShe,mytable --删除拼音函数 drop function f_getpy,f_getpy_tb
Create function fun_getPY ( @str nvarchar(4000) ) returns nvarchar(4000) as begin declare @word nchar(1),@PY nvarchar(4000) set @PY='' while len(@str)>0 begin set @word=left(@str,1) --假如非汉字字符,返回原字符 set @PY=@PY+(case when unicode(@word) between 19968 and 19968+20901 then ( select top 1 PY from ( select 'A' as PY,N'驁' as word union all select 'B',N'簿' union all select 'C',N'錯' union all select 'D',N'鵽' union all select 'E',N'樲' union all select 'F',N'鰒' union all select 'G',N'腂' union all select 'H',N'夻' union all select 'J',N'攈' union all select 'K',N'穒' union all select 'L',N'鱳' union all select 'M',N'旀' union all select 'N',N'桛' union all select 'O',N'漚' union all select 'P',N'曝' union all select 'Q',N'囕' union all select 'R',N'鶸' union all select 'S',N'蜶' union all select 'T',N'籜' union all select 'W',N'鶩' union all select 'X',N'鑂' union all select 'Y',N'韻' union all select 'Z',N'咗' ) T where word>=@word collate Chinese_PRC_CS_AS_KS_WS order by PY ASC ) else @word end) set @str=right(@str,len(@str)-1) end return @PY end
Create function fun_getPY ( @str nvarchar(4000) ) returns nvarchar(4000) as begin declare @word nchar(1),@PY nvarchar(4000) set @PY='' while len(@str)>0 begin set @word=left(@str,1) --假如非汉字字符,返回原字符 set @PY=@PY+(case when unicode(@word) between 19968 and 19968+20901 then ( select top 1 PY from ( select 'A' as PY,N'驁' as word union all select 'B',N'簿' union all select 'C',N'錯' union all select 'D',N'鵽' union all select 'E',N'樲' union all select 'F',N'鰒' union all select 'G',N'腂' union all select 'H',N'夻' union all select 'J',N'攈' union all select 'K',N'穒' union all select 'L',N'鱳' union all select 'M',N'旀' union all select 'N',N'桛' union all select 'O',N'漚' union all select 'P',N'曝' union all select 'Q',N'囕' union all select 'R',N'鶸' union all select 'S',N'蜶' union all select 'T',N'籜' union all select 'W',N'鶩' union all select 'X',N'鑂' union all select 'Y',N'韻' union all select 'Z',N'咗' ) T where word>=@word collate Chinese_PRC_CS_AS_KS_WS order by PY ASC ) else @word end) set @str=right(@str,len(@str)-1) end return @PY end goSelect dbo.fun_getPY('福田区第五医院') drop function fun_getPY /*---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- FTQDWYY(1 行受影响) */
(
Spell nvarchar(1) collate Chinese_PRC_CI_AS,
Word nvarchar(1) collate Chinese_PRC_CS_AS_KS_WS,
constraint PK_FirstSpell primary key clustered(Spell,Word)
)
goinsert into FirstSpell
select 'A', N'驁' union all
select 'B', N'簿' union all
select 'C', N'錯' union all
select 'D', N'鵽' union all
select 'E', N'樲' union all
select 'F', N'鰒' union all
select 'G', N'腂' union all
select 'H', N'夻' union all
select 'J', N'攈' union all
select 'K', N'穒' union all
select 'L', N'鱳' union all
select 'M', N'旀' union all
select 'N', N'桛' union all
select 'O', N'漚' union all
select 'P', N'曝' union all
select 'Q', N'囕' union all
select 'R', N'鶸' union all
select 'S', N'蜶' union all
select 'T', N'籜' union all
select 'W', N'鶩' union all
select 'X', N'鑂' union all
select 'Y', N'韻' union all
select 'Z', N'咗'
gocreate function fn_Spell(@Input nvarchar(4000))
returns nvarchar(4000)
as
begin
declare @Spell nvarchar(4000), @Word nchar(1)
set @Spell=''
while len(@Input)>0
begin
set @Word=left(@Input,1)
set @Spell=@Spell+(case when unicode(@Word) between 19968 and 40869 then (select top 1 Spell from FirstSpell where Word>=@Word order by Spell) else @Word end)
set @Input=right(@Input,len(@Input)-1)
end
return @Spell
end
/*
函数调用:
select dbo.fn_Spell('中华民族'),dbo.fn_Spell('中華民族')
*/
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)
endgo
--测试
select dbo.fgetpy('魏保光') as 姓名拼音,dbo.fgetpy('ab中c国人') as 中国人
select dbo.fgetpy('刘子良') as 姓名拼音,dbo.fgetpy('ab中c国人') as 中国人
select dbo.fgetpy('吴过') as 姓名拼音,dbo.fgetpy('ab中c国人') as 中国人
select dbo.fgetpy('东北') as 姓名拼音,dbo.fgetpy('ab中c国人') as 中国人
--删除拼音函数
drop function fgetpy
2 楼zjcxc(邹建)回复于 2006-06-01 19:54:20 得分 95/*--从全拼音中得到汉字拼音
首先,用输入法生成器(Imegen.exe)的逆转换功能
将全拼的码表文件 WINPY.MB 转换成文本文件 c:\winpy.txt
然后用下面的语句导入到数据库中
下面只是显示了读取并显示的过程,并没有存储到具体的表中
读取语句中的:
with(datafiletype='widechar') 的作用是处理unicode数据
我是用win2000测试的,转换的文本文件编码是unicode
如果是编码是ansi,则不要这句
查看文本文件编码,可以用记事本打开文本文件,另存为,就可以看到当前编码
--*/
--创建临时表
create table #t(a varchar(500))
--导入数据
bulk insert #t from 'c:\winpy.txt'
with(datafiletype='widechar')
--删除表头
set rowcount 12
delete from #t
set rowcount 0
--分拆处理
select 汉字=left(a,patindex('%[a-z]%',a)-1)
,拼音=stuff(a,1,patindex('%[a-z]%',a)-1,'')
from #t
--where patindex('%[a-z]%',a)=2 --如果是获得单字的读音
--删除测试
drop table #tTop
3 楼zjcxc(邹建)回复于 2006-06-01 19:56:16 得分 0
-- 使用拼音库处理拼音的示例
/*--获得汉字拼音的函数
需要创建一个拼音表,包含所有汉字的发音,这个可以通过转换全拼输入法的编码库得到,这里仅举了一个简单的例子.
--邹建 2003.10--*/
--创建汉字拼音库
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'
/*--下面是两个函数,一个以表的形式返回某个字符串的全部拼音,一个返回某某个字符串的其中一个拼音
--*/
go
--获取汉字拼音的函数--返回所有的拼音
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 @i=@i+1
insert into @re select @i,re+@splitchr+py from @re a,YingShe b
where a.id=@i-1 and b.chr=substring(@str,@i,1)
end
insert into @tb select re from @re where id=@i
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 @i=@i+1
select top 1 @re=@re+@splitchr+py
from YingShe where chr=substring(@str,@i,1)
end
return(@re)
end
go
--测试
--返回'长城'的所有可能拼音
select * from dbo.f_getpy_tb('长城')
--应用案例:
create table MyTable(Name varchar(10),ID int)
insert MyTable select '长城科技',1
union all select '金长城科技',2
union all select '立章城公司',3
go
--查询包含与'长城'拼音一样的记录:
select * from mytable
where exists(select 1 from dbo.f_getpy_tb('长城') where dbo.f_getpy(name) like '%'+re+'%')
--删除表
drop table YingShe,mytable
--删除拼音函数
drop function f_getpy,f_getpy_tb
(
@str nvarchar(4000)
)
returns nvarchar(4000)
as
begin
declare @word nchar(1),@PY nvarchar(4000) set @PY='' while len(@str)>0
begin
set @word=left(@str,1) --假如非汉字字符,返回原字符
set @PY=@PY+(case when unicode(@word) between 19968 and 19968+20901
then (
select top 1 PY
from
(
select 'A' as PY,N'驁' as word
union all select 'B',N'簿'
union all select 'C',N'錯'
union all select 'D',N'鵽'
union all select 'E',N'樲'
union all select 'F',N'鰒'
union all select 'G',N'腂'
union all select 'H',N'夻'
union all select 'J',N'攈'
union all select 'K',N'穒'
union all select 'L',N'鱳'
union all select 'M',N'旀'
union all select 'N',N'桛'
union all select 'O',N'漚'
union all select 'P',N'曝'
union all select 'Q',N'囕'
union all select 'R',N'鶸'
union all select 'S',N'蜶'
union all select 'T',N'籜'
union all select 'W',N'鶩'
union all select 'X',N'鑂'
union all select 'Y',N'韻'
union all select 'Z',N'咗'
) T
where word>=@word collate Chinese_PRC_CS_AS_KS_WS
order by PY ASC
)
else @word
end)
set @str=right(@str,len(@str)-1)
end return @PY end
(
@str nvarchar(4000)
)
returns nvarchar(4000)
as
begin
declare @word nchar(1),@PY nvarchar(4000) set @PY='' while len(@str)>0
begin
set @word=left(@str,1) --假如非汉字字符,返回原字符
set @PY=@PY+(case when unicode(@word) between 19968 and 19968+20901
then (
select top 1 PY
from
(
select 'A' as PY,N'驁' as word
union all select 'B',N'簿'
union all select 'C',N'錯'
union all select 'D',N'鵽'
union all select 'E',N'樲'
union all select 'F',N'鰒'
union all select 'G',N'腂'
union all select 'H',N'夻'
union all select 'J',N'攈'
union all select 'K',N'穒'
union all select 'L',N'鱳'
union all select 'M',N'旀'
union all select 'N',N'桛'
union all select 'O',N'漚'
union all select 'P',N'曝'
union all select 'Q',N'囕'
union all select 'R',N'鶸'
union all select 'S',N'蜶'
union all select 'T',N'籜'
union all select 'W',N'鶩'
union all select 'X',N'鑂'
union all select 'Y',N'韻'
union all select 'Z',N'咗'
) T
where word>=@word collate Chinese_PRC_CS_AS_KS_WS
order by PY ASC
)
else @word
end)
set @str=right(@str,len(@str)-1)
end return @PY end
goSelect dbo.fun_getPY('福田区第五医院')
drop function fun_getPY /*----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
FTQDWYY(1 行受影响)
*/