SQLServer中实现汉字⾸字母和把汉字转成拼⾳的⽅法。
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数据进⾏导⼊
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();
unicode汉字
}
}
}
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 ('我爱你哟')

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。