贴个函数,貌似可以,.
http://www.cnblogs.com/Ihaveadream/archive/2008/04/24/1169570.html
[code=SQL]use [csdn]
go
/*--获得汉字字符串的首字母--*/
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--while循环开始
select @ii=@ii+1,@n=63,@chn=substring(@str,@ii,1)
if @chn>'z'--if开始
--此处只有一个 select 语句,原理以“字符串排序以及ASCII码表”:
select @n = @n +1,@c = --★★★select★★★
case chn --case开始
when @chn then char(@n) --case分支
else @c --case分支
end --case结束
from( --★★★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 --if对应的else
set @c=@chn
--if结束 set @return=@return+@c
end--while循环结束
return(@return)
end--函数实现结束go
--测试
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 中国人
select dbo.fgetpy('王大海八丫') as 王大海八丫,dbo.fgetpy('服務地圖') as 服務地圖--删除拼音函数
--drop function fgetpy[/code]
http://www.cnblogs.com/Ihaveadream/archive/2008/04/24/1169570.html
[code=SQL]use [csdn]
go
/*--获得汉字字符串的首字母--*/
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--while循环开始
select @ii=@ii+1,@n=63,@chn=substring(@str,@ii,1)
if @chn>'z'--if开始
--此处只有一个 select 语句,原理以“字符串排序以及ASCII码表”:
select @n = @n +1,@c = --★★★select★★★
case chn --case开始
when @chn then char(@n) --case分支
else @c --case分支
end --case结束
from( --★★★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 --if对应的else
set @c=@chn
--if结束 set @return=@return+@c
end--while循环结束
return(@return)
end--函数实现结束go
--测试
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 中国人
select dbo.fgetpy('王大海八丫') as 王大海八丫,dbo.fgetpy('服務地圖') as 服務地圖--删除拼音函数
--drop function fgetpy[/code]
insert into dictionary select N'张','z'
union all select N'三','s'
union all select N'大','d'
union all select N'蛋','d'
union all select N'坏','h'
union all select N'是','s'
union all select N'个','g'
union all select N'也','y'
union all select N'四','s'
union all select N'李','l'
create table tb1(col1 nvarchar(50))
gocreate trigger changtoen on dbo.tb1
after insert,update
as
declare @i int, @cn nvarchar(50),@en varchar(50)
select @cn=col1 from inserted
set @en=''
while len(@cn)>0
begin
select @en=en+@en from dictionary where cn=right(@cn,1)
set @cn=left(@cn,len(@cn)-1)
end
update tb1 set col1=@en where exists (select 1 from inserted where col1=tb1.col1)
go
insert into tb1 select '张三是个大坏蛋'
go
select * from tb1
go
update tb1 set col1='李四也是个大坏蛋' where col1='zssgdhd'
go
select * from tb1
go
drop table tb1,dictionary
/*
col1
--------------------------------------------------
zssgdhd(1 行受影响)col1
--------------------------------------------------
lsysgdhd(1 行受影响)
*/