本人在毕业设计的时候,用二个字段来实现了这种功能,老师当时认为是近音搜索,给我打了优秀,激发了我了解近音搜索的相关资料,特将此代码共享。
using System;
using System.Text;
using System.IO;
namespace ts{
class test{
private static string[] startChars = {"啊", "芭", "擦","搭","蛾","发","噶","哈","击","击","喀","垃","妈","拿","哦","啪",
"期","然", "撒","塌","挖","挖","挖","昔","压","匝"};
private static string[] endChars = {"澳", "怖", "错","堕","贰","咐","过","祸","啊","骏","阔","络","穆","诺","沤","瀑",
"群","弱", "所","唾","啊","啊","误","迅","孕","座"};
/// <summary>
/// 根据字符和对应的中文字符,转成SQL查询条件
/// </summary>
/// <param name="cChar">要转化的字符,[A-Z]</param>
/// <param name="strFieldName">条件左值</param>
/// <returns>SQL条件</returns>
/// <remarks> Sxf 2001-1-4 ***** JY 2002-1-4 </remarks>
public static string GetCharCondition(char cChar, string strFieldName)
{
string strWord;
int Index = (int)(char.ToUpper(cChar)) - (int)'A';
if (Index >= 0 && Index < 26)
strWord = startChars[Index];
else
strWord = startChars[0];
// return string.Format("(({0}>='{1}' AND {0}<'[') OR ({0} >= '{3}' AND {0} < '{{') OR {0}>='{2}')",
// strFieldName, char.ToUpper(cChar), strWord, char.ToLower(cChar));
return string.Format("(({0} >= '{3}' AND {0} <= 'zzzzzzzz') OR {0}>='{2}')",
strFieldName, char.ToUpper(cChar), strWord, char.ToLower(cChar));
}
/// <summary>
/// 将指定字段值的每个字符分割,这样可以生成同音查询的SQL
/// </summary>
/// <param name="fieldName">字段名</param>
/// <param name="fieldValue">字段值</param>
/// <returns>生成的可以进行同音查询的SQL</returns>
public static string GetCharFullCondition(string fieldName, string fieldValue)
{
StringBuilder sql = new StringBuilder(1024);
int i = 1;
foreach (char c in fieldValue)
{
if (i > 1)
sql.Append(" AND ");
int index = (int)(char.ToUpper(c)) - (int)'A';
string startWord, endWord;
if (index >= 0 && index < 26)
{
startWord = startChars[index];
endWord = endChars[index];
}
else
{
startWord = startChars[0];
endWord = endChars[0];
}
string subStr = String.Format("SUBSTRING({0}, {1}, {2})", fieldName, i, 1);
sql.AppendFormat("({0} BETWEEN '{1}' AND '{2}')", subStr, startWord, endWord);
++i;
}
return sql.ToString();
}
}
}
using System;
using System.Text;
using System.IO;
namespace ts{
class test{
private static string[] startChars = {"啊", "芭", "擦","搭","蛾","发","噶","哈","击","击","喀","垃","妈","拿","哦","啪",
"期","然", "撒","塌","挖","挖","挖","昔","压","匝"};
private static string[] endChars = {"澳", "怖", "错","堕","贰","咐","过","祸","啊","骏","阔","络","穆","诺","沤","瀑",
"群","弱", "所","唾","啊","啊","误","迅","孕","座"};
/// <summary>
/// 根据字符和对应的中文字符,转成SQL查询条件
/// </summary>
/// <param name="cChar">要转化的字符,[A-Z]</param>
/// <param name="strFieldName">条件左值</param>
/// <returns>SQL条件</returns>
/// <remarks> Sxf 2001-1-4 ***** JY 2002-1-4 </remarks>
public static string GetCharCondition(char cChar, string strFieldName)
{
string strWord;
int Index = (int)(char.ToUpper(cChar)) - (int)'A';
if (Index >= 0 && Index < 26)
strWord = startChars[Index];
else
strWord = startChars[0];
// return string.Format("(({0}>='{1}' AND {0}<'[') OR ({0} >= '{3}' AND {0} < '{{') OR {0}>='{2}')",
// strFieldName, char.ToUpper(cChar), strWord, char.ToLower(cChar));
return string.Format("(({0} >= '{3}' AND {0} <= 'zzzzzzzz') OR {0}>='{2}')",
strFieldName, char.ToUpper(cChar), strWord, char.ToLower(cChar));
}
/// <summary>
/// 将指定字段值的每个字符分割,这样可以生成同音查询的SQL
/// </summary>
/// <param name="fieldName">字段名</param>
/// <param name="fieldValue">字段值</param>
/// <returns>生成的可以进行同音查询的SQL</returns>
public static string GetCharFullCondition(string fieldName, string fieldValue)
{
StringBuilder sql = new StringBuilder(1024);
int i = 1;
foreach (char c in fieldValue)
{
if (i > 1)
sql.Append(" AND ");
int index = (int)(char.ToUpper(c)) - (int)'A';
string startWord, endWord;
if (index >= 0 && index < 26)
{
startWord = startChars[index];
endWord = endChars[index];
}
else
{
startWord = startChars[0];
endWord = endChars[0];
}
string subStr = String.Format("SUBSTRING({0}, {1}, {2})", fieldName, i, 1);
sql.AppendFormat("({0} BETWEEN '{1}' AND '{2}')", subStr, startWord, endWord);
++i;
}
return sql.ToString();
}
}
}