拼音处理
来源:互联网 发布:磐石网络真垃圾 编辑:程序博客网 时间:2024/04/26 20:13
/*-1.-获得汉字字符串的首字母
根据大力的贴子改成.将大力的两个函数合并成了一个函数.
可以应用于助记码的查询
--转载(最早见于j9988的发表,具体原作者不明)--*/
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 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 @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('长城')
--返回'长城'的拼音
select dbo.f_getpy('长城')
--删除拼音函数
drop function f_getpy,f_getpy_tb
/*-1.-获得汉字字符串的首字母
根据大力的贴子改成.将大力的两个函数合并成了一个函数.
可以应用于助记码的查询
--转载(最早见于j9988的发表,具体原作者不明)--*/
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 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 @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('长城')
--返回'长城'的拼音
select dbo.f_getpy('长城')
--删除拼音函数
drop function f_getpy,f_getpy_tb
/*-1.-获得汉字字符串的首字母
根据大力的贴子改成.将大力的两个函数合并成了一个函数.
可以应用于助记码的查询
--转载(最早见于j9988的发表,具体原作者不明)--*/
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 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 @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('长城')
--返回'长城'的拼音
select dbo.f_getpy('长城')
--删除拼音函数
drop function f_getpy,f_getpy_tb
/*-1.-获得汉字字符串的首字母
根据大力的贴子改成.将大力的两个函数合并成了一个函数.
可以应用于助记码的查询
--转载(最早见于j9988的发表,具体原作者不明)--*/
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 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 @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('长城')
--返回'长城'的拼音
select dbo.f_getpy('长城')
--删除拼音函数
drop function f_getpy,f_getpy_tb
/*-1.-获得汉字字符串的首字母
根据大力的贴子改成.将大力的两个函数合并成了一个函数.
可以应用于助记码的查询
--转载(最早见于j9988的发表,具体原作者不明)--*/
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 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 @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('长城')
--返回'长城'的拼音
select dbo.f_getpy('长城')
--删除拼音函数
drop function f_getpy,f_getpy_tb
/*-1.-获得汉字字符串的首字母
根据大力的贴子改成.将大力的两个函数合并成了一个函数.
可以应用于助记码的查询
--转载(最早见于j9988的发表,具体原作者不明)--*/
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 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 @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('长城')
--返回'长城'的拼音
select dbo.f_getpy('长城')
--删除拼音函数
drop function f_getpy,f_getpy_tb
/*-1.-获得汉字字符串的首字母
根据大力的贴子改成.将大力的两个函数合并成了一个函数.
可以应用于助记码的查询
--转载(最早见于j9988的发表,具体原作者不明)--*/
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 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 @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('长城')
--返回'长城'的拼音
select dbo.f_getpy('长城')
--删除拼音函数
drop function f_getpy,f_getpy_tb
/*-1.-获得汉字字符串的首字母
根据大力的贴子改成.将大力的两个函数合并成了一个函数.
可以应用于助记码的查询
--转载(最早见于j9988的发表,具体原作者不明)--*/
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 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 @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('长城')
--返回'长城'的拼音
select dbo.f_getpy('长城')
--删除拼音函数
drop function f_getpy,f_getpy_tb
/*-1.-获得汉字字符串的首字母
根据大力的贴子改成.将大力的两个函数合并成了一个函数.
可以应用于助记码的查询
--转载(最早见于j9988的发表,具体原作者不明)--*/
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 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 @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('长城')
--返回'长城'的拼音
select dbo.f_getpy('长城')
--删除拼音函数
drop function f_getpy,f_getpy_tb
/*-1.-获得汉字字符串的首字母
根据大力的贴子改成.将大力的两个函数合并成了一个函数.
可以应用于助记码的查询
--转载(最早见于j9988的发表,具体原作者不明)--*/
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 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 @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('长城')
--返回'长城'的拼音
select dbo.f_getpy('长城')
--删除拼音函数
drop function f_getpy,f_getpy_tb
/*-1.-获得汉字字符串的首字母
根据大力的贴子改成.将大力的两个函数合并成了一个函数.
可以应用于助记码的查询
--转载(最早见于j9988的发表,具体原作者不明)--*/
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 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 @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('长城')
--返回'长城'的拼音
select dbo.f_getpy('长城')
--删除拼音函数
drop function f_getpy,f_getpy_tb
/*-1.-获得汉字字符串的首字母
根据大力的贴子改成.将大力的两个函数合并成了一个函数.
可以应用于助记码的查询
--转载(最早见于j9988的发表,具体原作者不明)--*/
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 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 @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('长城')
--返回'长城'的拼音
select dbo.f_getpy('长城')
--删除拼音函数
drop function f_getpy,f_getpy_tb
/*-1.-获得汉字字符串的首字母
根据大力的贴子改成.将大力的两个函数合并成了一个函数.
可以应用于助记码的查询
--转载(最早见于j9988的发表,具体原作者不明)--*/
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 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 @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('长城')
--返回'长城'的拼音
select dbo.f_getpy('长城')
--删除拼音函数
drop function f_getpy,f_getpy_tb
/*-1.-获得汉字字符串的首字母
根据大力的贴子改成.将大力的两个函数合并成了一个函数.
可以应用于助记码的查询
--转载(最早见于j9988的发表,具体原作者不明)--*/
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 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 @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('长城')
--返回'长城'的拼音
select dbo.f_getpy('长城')
--删除拼音函数
drop function f_getpy,f_getpy_tb
/*-1.-获得汉字字符串的首字母
根据大力的贴子改成.将大力的两个函数合并成了一个函数.
可以应用于助记码的查询
--转载(最早见于j9988的发表,具体原作者不明)--*/
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 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 @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('长城')
--返回'长城'的拼音
select dbo.f_getpy('长城')
--删除拼音函数
drop function f_getpy,f_getpy_tb
/*-1.-获得汉字字符串的首字母
根据大力的贴子改成.将大力的两个函数合并成了一个函数.
可以应用于助记码的查询
--转载(最早见于j9988的发表,具体原作者不明)--*/
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 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 @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('长城')
--返回'长城'的拼音
select dbo.f_getpy('长城')
--删除拼音函数
drop function f_getpy,f_getpy_tb
/*-1.-获得汉字字符串的首字母
根据大力的贴子改成.将大力的两个函数合并成了一个函数.
可以应用于助记码的查询
--转载(最早见于j9988的发表,具体原作者不明)--*/
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 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 @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('长城')
--返回'长城'的拼音
select dbo.f_getpy('长城')
--删除拼音函数
drop function f_getpy,f_getpy_tb
- 拼音处理
- 拼音处理
- 拼音处理
- 拼音处理
- 拼音处理
- 拼音处理
- 拼音处理
- 拼音处理
- 拼音处理
- 拼音处理
- 拼音处理
- 拼音处理
- 拼音处理
- 拼音处理
- 拼音处理
- 拼音处理
- 拼音处理
- javascript处理拼音
- MYSQL 8小时 断开链接问题
- J2EE的四书五经
- 手机同时播放两个音乐 探讨二
- 手机同时播放两个音乐之探讨一[JavaME]
- 我们都有病
- 拼音处理
- [j2me]二级菜单界面演练[四]
- [JavaME]解决来电问题(Incoming Call)
- [旧文]技术的背景音
- 辛劳的程序员一定要注意春补 (转)
- "SymbianOS Error -3606"问题解决了
- 树形数据的处理
- [JavaME]手机申请移动分配的动态IP[3]
- [j2me]二级菜单界面演练[三][0215Update]