sql 助记码

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER FUNCTION [dbo].[uf_GetSpellCode](   
   
                @string nvarchar(2000)   
                )   
   
returns nvarchar(1000)   
AS BEGIN   
   
    declare @substring    nchar(1),   
            @word         nchar(1),   
            @spellcode    nvarchar(1000)   
   
    set @spellcode=''   
   
    while len(@string)>0   
    begin          set @substring=left(@string,1)   
        if (unicode(@substring) between 19968 and 19968+20901)   
        begin   
            set @word=(select top 1 spellcode from    
       (   
        select 'A' as spellcode,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>=@substring collate Chinese_PRC_CS_AS_KS_WS   
         order by spellcode ASC)   
        end else   
        begin   
            set @word=@substring   
        end   
   
        set @spellcode=@spellcode+@word   
        set @string=right(@string,len(@string)-1)   
    end   

select @spellcode=replace(@spellcode,'/','')

    return @spellcode   
END

 

其中 在表的字段里面需要建立触发器

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER TRIGGER [tg_imatgroupMD_u]
   ON  [dbo].[imatgroupMD]
FOR update
AS
BEGIN

 SET NOCOUNT ON;

update imatgroupMD set SpellCode=dbo.uf_GetSpellCode(b.matgroupNameMD)
from imatgroupMD a inner join inserted b on a.matgroupNameMD=b.matgroupNameMD

END