SQL生成助记码的方法

--01--首先创建一个函数，其功能就是功能是得到汉字拼音首字母 
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