按拼音模糊匹配查询条件的生成类

1using System; 
 2using System.Text; 
 3using System.IO; 
 4
 5namespace ts
 6{ 
 7    class test
 8    { 
 9        private static string[] startChars = {"啊", "芭", "擦","搭","蛾","发","噶","哈",
"击","击","喀","垃","妈","拿","哦","啪","期","然", "撒","塌","挖","挖","挖","昔","压","匝"}; 
10        private static string[] endChars = {"澳", "怖", "错","堕","贰","咐","过","祸",
"啊","骏","阔","络","穆","诺","沤","瀑","群","弱", "所","唾","啊","啊","误","迅","孕","座"}; 
11
12        /**//// <summary> 
13        /// 根据字符和对应的中文字符,转成SQL查询条件 
14        /// </summary> 
15        /// <param name="cChar">要转化的字符,[A-Z]</param> 
16        /// <param name="strFieldName">条件左值</param> 
17        /// <returns>SQL条件</returns> 
18        /// <remarks> Sxf 2001-1-4 ***** JY 2002-1-4 </remarks> 
19        public static string GetCharCondition(char cChar, string strFieldName) 
20        { 
21            string strWord; 
22            int Index = (int)(char.ToUpper(cChar)) - (int)'A'; 
23            if (Index >= 0 && Index < 26) 
24                strWord = startChars[Index]; 
25            else 
26                strWord = startChars[0]; 
27            
28            //return string.Format("(({0}>='{1}' AND {0}<'[') OR ({0} >= '{3}' AND {0} < '{{') OR {0}>='{2}')",  
29            //strFieldName, char.ToUpper(cChar), strWord, char.ToLower(cChar)); 
30            
31            return string.Format("(({0} >= '{3}' AND {0} <= 'zzzzzzzz') OR {0}>='{2}')",  
32            strFieldName, char.ToUpper(cChar), strWord, char.ToLower(cChar)); 
33        } 
34
35        /**//// <summary> 
36        /// 将指定字段值的每个字符分割，这样可以生成同音查询的SQL 
37        /// </summary> 
38        /// <param name="fieldName">字段名</param> 
39        /// <param name="fieldValue">字段值</param> 
40        /// <returns>生成的可以进行同音查询的SQL</returns> 
41        public static string GetCharFullCondition(string fieldName, string fieldValue) 
42        { 
43            StringBuilder sql = new StringBuilder(1024); 
44            int i = 1; 
45            foreach (char c in fieldValue) 
46            { 
47                if (i > 1) 
48                    sql.Append(" AND "); 
49                int index = (int)(char.ToUpper(c)) - (int)'A'; 
50                string startWord, endWord; 
51                if (index >= 0 && index < 26) 
52                { 
53                    startWord = startChars[index]; 
54                    endWord = endChars[index]; 
55                } 
56                else 
57                { 
58                    startWord = startChars[0]; 
59                    endWord = endChars[0]; 
60                } 
61                string subStr = String.Format("SUBSTRING({0}, {1}, {2})", fieldName, i, 1); 
62                sql.AppendFormat("({0} BETWEEN '{1}' AND '{2}')", subStr, startWord, endWord); 
63                ++i; 
64            } 
65
66            return sql.ToString(); 
67        } 
68    } 
69}