使用sql函数，如何生成助记码呢？下面就将为您介绍sql函数生成助记码的方法实例，供您参考，希望对您学习sql函数的使用能有所启示 。
--助记码匹配库
IF EXISTS(SELECT name
FROM sysobjects
WHERE name = Nhzpy
AND type = U)
DROP TABLE hzpy
GO
CREATE TABLE [hzpy] ( [Hz] [char] (2) NOT NULL , [Py] [char] (1) NULL )
CREATE CLUSTERED INDEX [IX_hzpy] ON [hzpy] ( Hz )
INSERT [hzpy] ( [Hz] , [Py] ) VALUES ( 吖 , A )
INSERT [hzpy] ( [Hz] , [Py] ) VALUES ( 阿 , A )
INSERT [hzpy] ( [Hz] , [Py] ) VALUES ( 啊 , A )
INSERT [hzpy] ( [Hz] , [Py] ) VALUES ( 锕 , A )
INSERT [hzpy] ( [Hz] , [Py] ) VALUES ( 嗄 , S )
INSERT [hzpy] ( [Hz] , [Py] ) VALUES ( 哎 , A )
INSERT [hzpy] ( [Hz] , [Py] ) VALUES ( 哀 , A )
INSERT [hzpy] ( [Hz] , [Py] ) VALUES ( 唉 , A )
INSERT [hzpy] ( [Hz] , [Py] ) VALUES ( 埃 , A )
INSERT [hzpy] ( [Hz] , [Py] ) VALUES ( 挨 , A )
INSERT [hzpy] ( [Hz] , [Py] ) VALUES ( 锿 , A )
INSERT [hzpy] ( [Hz] , [Py] ) VALUES ( 捱 , A )
INSERT [hzpy] ( [Hz] , [Py] ) VALUES ( 皑 , A )
INSERT [hzpy] ( [Hz] , [Py] ) VALUES ( 癌 , A )
INSERT [hzpy] ( [Hz] , [Py] ) VALUES ( 嗳 , A )
--定义dbo.getpy函数
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = Ngetpy)
DROP FUNCTION getpy
GO
CREATE FUNCTION getpy(@hz varchar(30))
RETURNS varchar(30)
AS
BEGIN
declare @py varchar(30)
declare @num int
declare @i int
set @py=
set @num=len(@hz)
set @i=1
while @i<=@num
begin
select @py=@py+py from hzpy where hz = substring(@hz,@i,1)
if @@rowcount=0
select @py=@py+substring(@hz,@i,1)
select @i=@i+1
end
return @py
END
GO
--例子
select dbo.getpy(爱老虎油)
select dbo.getpy(shengccj),shengccj,* from docdsp
