转载大力的: ---在你的查询分析器里执行: --建立函数: create function f_ch2py(@chn nchar(1)) returns char(1) as begin declare @n int declare @c char(1) set @n = 63select @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 '丌' 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 @chn) as a order by chn COLLATE Chinese_PRC_CI_AS ) as b return(@c) end go---再建立函数: CREATE FUNCTION F_GetHelpCode ( @cName VARCHAR(20) ) RETURNS VARCHAR(12) AS BEGIN DECLARE @i SMALLINT, @L SMALLINT , @cHelpCode VARCHAR(12), @e VARCHAR(12), @iAscii SMALLINT SELECT @i=1, @L=0 , @cHelpCode='' while @L<=12 AND @i<=LEN(@cName) BEGIN SELECT @e=LOWER(SUBSTRING(@cname,@i,1)) SELECT @iAscii=ASCII(@e) IF @iAscii>=48 AND @iAscii <=57 OR @iAscii>=97 AND @iAscii <=122 or @iAscii=95 SELECT @cHelpCode=@cHelpCode +@e ELSE IF @iAscii>=176 AND @iAscii <=247 SELECT @cHelpCode=@cHelpCode + dbo.f_ch2py(@e) ELSE SELECT @L=@L-1 SELECT @i=@i+1, @L=@L+1 END RETURN @cHelpCode END GO --完成了,现在你可以用下面的调用: select dbo.F_GetHelpCode('大力') --或 select dbo.F_GetHelpCode(你的列名) from 表
类似这样处理. 假设你的表中已经有一个字段,叫拼音.并在数据库中创建了上面的自定义函数.就可以写这样的触发器: CREATE TRIGGER trigger_name ON 你的表 FOR INSERT,UPDATE AS IF UPDATE (姓名列名) update 你的表 set 拼音=dbo.F_GetHelpCode(b.姓名列名) from 你的表 a,inserted b where a.id=b.id
这样的话,输入和修改数据的时候完全不用理会拼音. 查询的时候直接可以用: select * from 你的表 where 拼音 like 'L'+'%'
---在你的查询分析器里执行:
--建立函数:
create function f_ch2py(@chn nchar(1))
returns char(1)
as
begin
declare @n int
declare @c char(1)
set @n = 63select @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
'丌' 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 @chn) as a
order by chn COLLATE Chinese_PRC_CI_AS
) as b
return(@c)
end
go---再建立函数:
CREATE FUNCTION F_GetHelpCode (
@cName VARCHAR(20) )
RETURNS VARCHAR(12)
AS
BEGIN
DECLARE @i SMALLINT, @L SMALLINT , @cHelpCode VARCHAR(12), @e VARCHAR(12), @iAscii SMALLINT
SELECT @i=1, @L=0 , @cHelpCode=''
while @L<=12 AND @i<=LEN(@cName) BEGIN
SELECT @e=LOWER(SUBSTRING(@cname,@i,1))
SELECT @iAscii=ASCII(@e)
IF @iAscii>=48 AND @iAscii <=57 OR @iAscii>=97 AND @iAscii <=122 or @iAscii=95
SELECT @cHelpCode=@cHelpCode +@e
ELSE
IF @iAscii>=176 AND @iAscii <=247
SELECT @cHelpCode=@cHelpCode + dbo.f_ch2py(@e)
ELSE SELECT @L=@L-1
SELECT @i=@i+1, @L=@L+1 END
RETURN @cHelpCode
END
GO
--完成了,现在你可以用下面的调用:
select dbo.F_GetHelpCode('大力')
--或
select dbo.F_GetHelpCode(你的列名) from 表
假设你的表中已经有一个字段,叫拼音.并在数据库中创建了上面的自定义函数.就可以写这样的触发器:
CREATE TRIGGER trigger_name
ON 你的表
FOR INSERT,UPDATE
AS
IF UPDATE (姓名列名)
update 你的表 set 拼音=dbo.F_GetHelpCode(b.姓名列名)
from 你的表 a,inserted b
where a.id=b.id
查询的时候直接可以用:
select * from 你的表 where 拼音 like 'L'+'%'