go
if exists (select * from sysobjects where name='fun_getPY' and xtype='Fn')
begin
drop function fun_getPY
end
go
create function fun_getPY(@str nvarchar(4000))
returns nvarchar(4000)
as
begin
declare @word nchar(1),@PY nvarchar(4000)
set @PY=''
while len(@str)>0
begin
set @word=left(@str,1)
--如果非汉字字符,返回原字符
set @PY=@PY+(case when unicode(@word) between 19968 and 19968+20901
then (select top 1 PY from (
select 'A' as PY,N'驁' as word
union all select 'B',N'簿'
union all select 'C',N'錯'
union all select 'D',N'鵽'
union all select 'E',N'樲'
union all select 'F',N'鰒'
union all select 'G',N'腂'
union all select 'H',N'夻'
union all select 'J',N'攈'
union all select 'K',N'穒'
union all select 'L',N'鱳'
union all select 'M',N'旀'
union all select 'N',N'桛'
union all select 'O',N'漚'
union all select 'P',N'曝'
union all select 'Q',N'囕'
union all select 'R',N'鶸'
union all select 'S',N'蜶'
union all select 'T',N'籜'
union all select 'W',N'鶩'
union all select 'X',N'鑂'
union all select 'Y',N'韻'
union all select 'Z',N'咗'
) T
where word>=@word collate Chinese_PRC_CS_AS_KS_WS
order by PY ASC) else @word end)
set @str=right(@str,len(@str)-1)
end
return @PY
end
go
--02--函数调用实例
--函数调用实例1:生成指定字符的助记码
select dbo.fun_getPY('中华人民共和国') FhelperCode
--结果为:ZHRMGHG
[url=http://community.kingdee.com/Images/www_mykingdee_com/lz_xiaohe/image_00F4E059.png][/url]
--函数调用实例2:生成指定字段的助记码
--以下结果得到根据第一个汉字字母组合成的记助码
select dbo.fun_getPY(Fname) FhelperCode,* from t_account
order by fnumber
由于银行的行中是多音字,因此把在得到的结果中,按Xing音来形成首字母了。
对这种情况,需要征集更好的智能判断多音字的方法。
----函数调用实例3:更新指字段的助记码
update t_account set FhelperCode=dbo.fun_getPY(Fname)
where FhelperCode is null
在使用时,dbo.这几个字每可千万别忘掉了。
---------------------------------------------------------------------------------方法二
USE ZYSCM
GO
/****** 对象: UserDefinedFunction [dbo].[GetZjm] 脚本日期: 01/10/2014 19:45:13 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
create FUNCTION [dbo].[GetZjm] (@text varchar(100))
RETURNS varchar(100) AS
BEGIN
declare @zjm varchar(100), @tmp_char varchar(2),@tmp_zjm varchar(2), @i int,@length int
set @zjm =''
set @length = len(@text)
set @i = 1
while @i<=@length
begin
set @tmp_char = substring(@text,@i,1)
select @tmp_zjm = zjm from hz_zjm where hanzi=@tmp_char
if @@rowcount=1
set @zjm = @zjm +Rtrim(@tmp_zjm)
set @i = @i + 1
end
return (@zjm)
END
-------------------zjm表
GO
/****** 对象: Table [dbo].[hz_zjm] 脚本日期: 01/10/2014 19:53:09 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[hz_zjm](
[hanzi] [char](2) NOT NULL,
[quanpin] [char](10) NULL
) ON [PRIMARY]
SET ANSI_PADDING ON
ALTER TABLE [dbo].[hz_zjm] ADD [zjm] [varchar](80) NULL
ALTER TABLE [dbo].[hz_zjm] ADD [wbzjm] [char](1) NULL DEFAULT ('')
/****** 对象: Index [PK__hz_zjm__703483B9] 脚本日期: 01/10/2014 19:53:09 ******/
ALTER TABLE [dbo].[hz_zjm] ADD CONSTRAINT [PK__hz_zjm__703483B9] PRIMARY KEY CLUSTERED
(
[hanzi] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING OFF