關鍵字檢索，找到所有數據

昵稱10504424 2013-11-18

展開全文

using System.Linq;
2 using Core.Common;
3 using System.Web;
4 using System.Text.RegularExpressions;
5
6 namespace Core.Helper
7 {
8     public class KeySearch
9     {
10         private static int _keyCode = 2;//關鍵字最小組合字數
11
12         //過濾關鍵字
13         private static string[] ReplaceKey ={18                                                "省",
19                                                "市",
20                                                "縣",
21                                                "鄉(xiāng)",
22                                                "鎮(zhèn)",
23                                                "地方",
24                                                " "
25                                            };
26         /// <summary>
27         /// 搜索關鍵字組合方法
28         /// </summary>
29         /// <param name="keyword"></param>
30         /// <returns></returns>
31         public static string KeySearchSpan(string keyword)
32         {
33             string reStr = "";
34             string copyStr = keyword;
35             if (copyStr.Length < _keyCode)
36             {
37                 reStr = copyStr + ",";
38             }
39             else
40             {
41                 var query = from data in ReplaceKey
42                             select data;
43                 foreach(var s in query)
44                 {
45                     copyStr = Regex.Replace(copyStr, s, "", RegexOptions.Compiled | RegexOptions.IgnoreCase | RegexOptions.Singleline);
46                 }
47                 int count = copyStr.Length - _keyCode + 1;
48                 for (int i = 0; i < count; i++)
49                 {
50                     reStr += copyStr.Substring(i, _keyCode) + ",";
51                 }
52             }
53             return reStr;
54         }
55     }
56 }

通過方法處理后的字符串為 "北京,京小,小飯,飯鍋,鍋餐,餐廳,廳電,電話,"

按照這樣的關鍵字再到數據庫中去搜索，應該不會落下任何匹配的數據了

不多說在看看存儲過程怎么處理

 1 -- =============================================
 2 -- 作者:huangtao
 3 -- 功能描述: 電話搜索列表
 4 -- 當前版本: 001.001.001 
 5 -- 修改描述: 1.
 6 --             2.
 7 -- =============================================
 8 ALTER PROCEDURE [dbo].[PH_Tel_SearchList_S]
 9 @SITEID         INT,             --城市ID
10 @KEYWORDS        VARCHAR(300),     --模糊查詢條件 以，號分割
11 @CURPAGE        INT,             --當前頁          
12 @PAGESIZE       INT,             --分頁大小
13 @COUNT            INT  OUTPUT      --總條數
14 AS
15 BEGIN
16     DECLARE @POS INT --字符串分割個數
17     DECLARE @LEN INT --單次字符長度
18     DECLARE @TEMP VARCHAR(10) --單次字符內容
19     DECLARE @WHERE VARCHAR(2000)  --拼接的SQL語句條件
20     DECLARE @FIELDS VARCHAR(1000)  --字段名
21     SET @POS=CHARINDEX(',',@KEYWORDS,0)
22     SET @WHERE=' SITEID='+CAST(@SITEID AS VARCHAR(10)) + ' AND CCOOCHK=1 AND ISDEL=0 AND ('
23     WHILE(@POS>0)
24     BEGIN
25         SET @TEMP=SUBSTRING(@KEYWORDS,0,@POS)
26         SET @LEN=LEN(@TEMP)
27         SET @WHERE=@WHERE+'(TELNAME+TELINFO+TELINFO1+SPINFO+KEYWORD) LIKE ''%' +@TEMP+'%'' '
28         SET @KEYWORDS=STUFF(@KEYWORDS,1,@LEN+1,'')
29         SET @POS=CHARINDEX(',',@KEYWORDS,0)
30         IF @POS>0
31             SET @WHERE=@WHERE+' OR '
32     END
33     SET @WHERE=@WHERE+')'    
34     SET @FIELDS='ID,TELNAME,TELINFO,TELINFO1,SPINFO,KEYWORD,ISTJ,ISYP,STATE,ISSTATE,UPTIME'
35     EXEC PAGE_NEW 'YP_DIR',@FIELDS,@WHERE,'ISTJ DESC,UPTIME DESC ',@PAGESIZE,@CURPAGE, @COUNT OUT 
36 END