SQLServer实现快速进行简繁体的翻译功能

1  创建简繁体对照表

脚本如下：

CREATE TABLE [dbo].[SYS_BGBIG](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[gb] [nvarchar](1) NULL,
	[big] [nvarchar](1) NULL,
 CONSTRAINT [PK_SYS_BGBIG] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

可以从这里下载常用简繁体对照表1500多个简繁体对照(仅有繁体字的汉字，简繁体字一样的不需要对照)

https://download.csdn.net/download/postfxj/10563082

2 也可以自行收集简繁体字对照，方法如下(c#方法）：

2.1  创建简繁转换的类，不需要使用第三方dll，直接使用windows的API

using System;
using System.Collections.Generic;
using System.Text;
using System.Runtime.InteropServices;
using System.Windows.Forms;
 
namespace POS
{
    class ConvertSCTC
    {
        #region 简体繁体转换
        //public enum ConvertType
        //{
        //    Simplified,
        //    Traditional
        //}
 
        [DllImport("kernel32.dll", EntryPoint = "LCMapStringA")]
        public static extern int LCMapString(int Locale, int dwMapFlags, byte[] lpSrcStr, int cchSrc, byte[] lpDestStr, int cchDest);
 
        public  const int LCMAP_SIMPLIFIED_CHINESE = 0x02000000;
        public  const int LCMAP_TRADITIONAL_CHINESE = 0x04000000;
        public static Encoding gb2312 = Encoding.GetEncoding(936);
 
        public  static string SCTOTCConvert(string TextValue)
        {
            if (TextValue != "")
            {
                String ReturnTextValue = "";
                byte[] source = gb2312.GetBytes(TextValue);
                byte[] dest = new byte[source.Length];
                LCMapString(0x0804, LCMAP_TRADITIONAL_CHINESE, source, -1, dest, source.Length);
                ReturnTextValue = gb2312.GetString(dest);
                return ReturnTextValue;
            }
            else return "";
        }
 
        public static string TCTOSCConvert( string TextValue)
        {
            if (TextValue != "")
            {
                String ReturnTextValue = "";
                byte[] source = gb2312.GetBytes(TextValue);
                byte[] dest = new byte[source.Length];
                LCMapString(0x0804, LCMAP_SIMPLIFIED_CHINESE, source, -1, dest, source.Length);
                ReturnTextValue = gb2312.GetString(dest);
                return ReturnTextValue;
            }
            else
                return "";
        }
 
        public static  void TCTOSCConvert( Control  c)
        {
            c.Text = TCTOSCConvert(c.Text );
            if (c is TextBox ) 
               ((TextBox)c).SelectionStart = ((TextBox)c).Text.Length;
        }
 
        public static void SCTOTCConvert(Control  c)
        {
            c.Text = SCTOTCConvert(c.Text);
            if (c is TextBox) 
                ((TextBox)c).SelectionStart = ((TextBox)c).Text.Length;
        }
        #endregion
    }
}

2.1  收集简繁字库方法如下，以任意表任意字段为例来进行收集。

private void btnSJ_Click(object sender, EventArgs e)
        {
            waiting w=new waiting ();
            try
            {
                w.Show ();
            if(dgvData .Rows .Count >0)
                for (int i = 0; i < dgvData.Rows.Count; i++)
                {
                    string s = dgvData.Rows[i].Cells[txtField.Text].Value.ToString();
                    for(int j=0;j<s.Length ;j++)
                    {
                        string sFT = s.Substring(j, 1);
                        string sJT = ConvertSCTC.TCTOSCConvert(sFT);
                        if (sFT != sJT)
                        {
                            string sql = "Select * FROM SYS_BGBIG WHERE BIG=N'" + sFT + "'";
                            bool isData = WEBPOS.IsExit(BASEINFO.DESEncrypt(sql));
                            if (!WEBPOS.IsExit(BASEINFO.DESEncrypt(sql)))
                            {
                                sql = "insert into SYS_BGBIG(GB,BIG) VALUES(N'" + sJT + "',N'" + sFT + "')";
                                WEBPOS.SQLcmd(BASEINFO.DESEncrypt(sql));
                            }
                        }
                        
                    }
                }
            }
            finally 
            {
                w.Close ();
            }
        }

3  在SQL Server中实现简体和繁体的转换

创建标量值函数，实现简到繁和繁到简的任意翻译。

create FUNCTION [dbo].[f_GB2BIG](
@str nvarchar(4000),    --要转换的字符串
@toBIG bit        --转换标志,为,表示GB-->BIG,否则是BIG-->GB
)RETURNS nvarchar(4000)
AS
BEGIN
    IF @toBIG=1
        SELECT @str=REPLACE(@str,gb,big)
        FROM SYS_BGBIG
        WHERE CHARINDEX(gb,@str)>0
    ELSE
        SELECT @str=replace(@str,big,gb)
        FROM SYS_BGBIG
        WHERE charindex(big,@str)>0
    RETURN(@str)
 
END

使用示例 ：Select dbo.f_GB2BIG(N'国华发展',1)