步骤:
1.下载 中文 拼音对照表 下载地址:http://download.csdn.net/source/326464
2.将下载的T-SQL语句放入需要功能的数据库中运行. 将生成 pycode 这张表
3.在需要实现的表中新建字段,命名为Namepy
4.运行以下存储过程和触发器:
<1>存储过程的用处是将字段中的中文每个读出查询到它的拼音码(无需更改)
<2>触发器的作用是:当中文字段被Insert或者Update后,触发器会自动运行插入或改变拼音码
![ContractedBlock.gif](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
--
存储过程 将汉字转化成拼音输出
create proc Hz2Py
@hz varchar ( 200 ),
@py varchar ( 200 ) output
AS
declare @i int
declare @chr varchar ( 2 )
declare @pyc varchar ( 1 )
set @i = 1
while ( @i <= len ( @hz )) begin
set @chr = substring ( @hz , @i , 1 )
set @pyc = ( select py from pycode where hz = @chr )
if ( @pyc is null )
set @py = @py + @chr
else
set @py = @py + @pyc
Set @i = @i + 1
end
-- 触发器 当插入,更新时 更改拼音码
create proc Hz2Py
@hz varchar ( 200 ),
@py varchar ( 200 ) output
AS
declare @i int
declare @chr varchar ( 2 )
declare @pyc varchar ( 1 )
set @i = 1
while ( @i <= len ( @hz )) begin
set @chr = substring ( @hz , @i , 1 )
set @pyc = ( select py from pycode where hz = @chr )
if ( @pyc is null )
set @py = @py + @chr
else
set @py = @py + @pyc
Set @i = @i + 1
end
-- 触发器 当插入,更新时 更改拼音码
--Namepy 为拼音字段
--bank 为需要实现表的表名
--customername 为汉字字段
--cardid 为bank表主键
create trigger Namepy on bank for insert , update
AS
declare @name varchar ( 100 ), @npy varchar ( 100 ), @cid char ( 10 )
if ( update (customername)) begin
set @npy = ''
select @name = customername, @cid = cardid from inserted
create trigger Namepy on bank for insert , update
AS
declare @name varchar ( 100 ), @npy varchar ( 100 ), @cid char ( 10 )
if ( update (customername)) begin
set @npy = ''
select @name = customername, @cid = cardid from inserted
--调用存储过程
exec Hz2Py @name , @npy output
update bank set namepy = @npy where cardid = @cid
end
GO
exec Hz2Py @name , @npy output
update bank set namepy = @npy where cardid = @cid
end
GO
嗯.. 如果还有什么问题.可以留言给我..