SQL Server中实现汉字首字母和把汉字转成拼音的方法。

1 建表保存拼音、拼音首字母、和汉字的对照表。

脚本如下：

CREATE TABLE [dbo].[SYS_ChinesePY](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [PYFIRST] [char](1) NULL,
    [PYALL] [varchar](50) NULL,
    [WORD] [nvarchar](1) NULL,
 CONSTRAINT [PK_SYS_ChinesePY] 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]

可以从这里下载很全的拼音对照表的excel数据进行导入

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

2 实现任意文字转拼音和首字母的收集（c#方法,如果不想导入也可以用下面的方法自行收集）。

2.1先下载Mcrosoft visual studio international pack并安装在任意一台电脑上

2.2 进C:\Program Files (x86)\Microsoft Visual Studio International Pack\Simplified Chinese Pin-Yin Conversion Library文件夹复制

chnCharInfo.dll文件到vs开发工具的电脑上，在项目中引用该文件。

2.3 相关取拼音的类文件如下

此类中一定要引用

using Microsoft.International.Converters.PinYinConverter; 

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.International.Converters.PinYinConverter; 

namespace POS
{
    class CS_Chinese
    {
       
        /// 获得单个字符的拼音
        /// </summary>
        /// <param name="c"></param>
        /// <returns></returns>
        public static string GetPinYin(char c)
        {
            ChineseChar cc = new ChineseChar(c);
            string str = cc.Pinyins[0]; //多音字只取第一个
            return str.Substring(0, str.Length - 1); //去掉最后的声调
        }

        /// 获得一个字符串的拼音
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        ///  /// <summary>
        public static string GetPinYin(string str)
        {
            StringBuilder sb = new StringBuilder();
            foreach (char item in str)
            {
                //判断是不是汉字，如果不是原字符返回
                if (ChineseChar.IsValidChar(item))
                {
                    sb.Append(GetPinYin(item));
                }
                else
                {
                    sb.Append(item);
                }
            }


            return sb.ToString();
        }

        

       
    }
}

2.4 用上面的方法可以收集汉字来把数据插入到对照表中

以下是我以任何表中的某个栏位来收集对像进行收集的方法的样例。

private void btnSJPY_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 sWord = s.Substring(j, 1);
                            string sGBWord = ConvertSCTC.TCTOSCConvert(sWord);//簡體字
                            string sBIGWord = ConvertSCTC.SCTOTCConvert(sGBWord);//繁體字
                            string sGBPY = CS_Chinese.GetPinYin(sGBWord);
                            string sBIGPY = CS_Chinese.GetPinYin(sBIGWord);
                            if (sGBWord != sGBPY)
                            {
                                string sql = "Select * FROM SYS_ChinesePY WHERE Word=N'" + sGBWord + "'";
                                bool isData = WEBPOS.IsExit(BASEINFO.DESEncrypt(sql));
                                if (!WEBPOS.IsExit(BASEINFO.DESEncrypt(sql)))
                                {
                                    sql = "insert into SYS_ChinesePY(PYFIRST,PYALL,WORD) VALUES(N'" + sGBPY.Substring (0,1) +"',N'" + sGBPY + "',N'" + sGBWord + "')";
                                    WEBPOS.SQLcmd(BASEINFO.DESEncrypt(sql));
                                }
                            }

                            if (sBIGWord != sBIGPY)
                            {
                                string sql = "Select * FROM SYS_ChinesePY WHERE Word=N'" + sBIGWord + "'";
                                bool isData = WEBPOS.IsExit(BASEINFO.DESEncrypt(sql));
                                if (!WEBPOS.IsExit(BASEINFO.DESEncrypt(sql)))
                                {
                                    sql = "insert into SYS_ChinesePY(PYFIRST,PYALL,WORD) VALUES(N'" + sBIGPY.Substring(0, 1) + "',N'" + sBIGPY + "',N'" + sBIGWord + "')";
                                    WEBPOS.SQLcmd(BASEINFO.DESEncrypt(sql));
                                }
                            }


                        }
                    }
            }
            finally
            {
                w.Close();
            }
        }

3  SQL Server中实现翻译的方法

创建如下标量值函数：

CREATE FUNCTION [dbo].[procGetPY]   
(   
@str NVARCHAR(4000)   
)   
/*  
select dbo. procGetPYFirstLetter ('中國')  
*/  
RETURNS NVARCHAR(4000)   
--WITH ENCRYPTION   
AS  
BEGIN  
DECLARE @WORD NCHAR(1),@PY NVARCHAR(4000)   
   
SET @PY=''  
   
WHILE LEN(@STR)>0   
BEGIN  
SET @WORD=LEFT(@STR,1)   

--如果非漢字字符﹐返回原字符   
SET @PY=@PY+' '+RTRIM((CASE WHEN UNICODE(@WORD) BETWEEN 19968 AND 19968+20901   
THEN (   
SELECT TOP 1 PY  
FROM  
(   
	select PYALL PY,WORD
	from sys_chinesePY 
)  T
WHERE WORD=@WORD 
ORDER BY PY ASC
)
ELSE @WORD   
END) )  
SET @STR=RIGHT(@STR,LEN(@STR)-1)   
END   
RETURN @PY 
   
END  

调用方法：select dbo. procGetPYFirstLetter ('我爱你哟')