How to implement exact search and blur search with SQL statements

When writing a Web project, the search is the function that must be provided.

Sometimes customers enter a set of keywords separated by spaces or other separators, we need to find the following results according to input:

Absolutely containing the result of continuous emergence of whole group

Contains each entry in the entire group of keywords, but not necessarily continuous results

The result of any or more keywords in the whole group of keywords

Just like you search for SQL Server 2000 in Google,

It first strictly matches the entire string

If you can't find it, see if there is a result of these three words.

If there is no result that the three words contain, try to find out the result of any word.

The following code is a function that dynamically builds this search mode in the code.

KEYWORD is the keyword

SearchMode is the search mode, 1 represents accurate, 2, represents all included, 3 represents arbitrary included

String strcondition = "id in (";

String strsubquery = "SELECT DISTINCT Productid from ProductCateView Where Id <> 0";

IF (Request.QueryString ["country"]! = null)

STRSUBQUERY = "AND industryregionID =" Request.QueryString ["country"];

if (Request.QueryString ["Industry]! = NULL)

STRSUBQUERY = "And Industryid =" Request.QueryString ["industry"];

if (Request.QueryString ["Keyword"]! = null

{

// SearchMode: Search mode, 1, represents exact search, 2 represents the same contains the search 3, representing a search for any word

String strkeyword = server.urldecode (Request.QueryString ["keyword"]. Replace ("'", "' '));

String strTemp = "";

STRSUBQUERY = "and";

KeywordArray;

IF (strkeyword.indexof (',')! = - 1)

KeyWordArray = strkeyword.split (',');

Else

IF (strkeyword.indexof ('|')! = - 1)

KeywordArray = strkeyword.split ('|');

Else

KeyWordArray = strkeyword.split (null);

// division keyword

IF (KeywordArray.length <2 || strSearchmode == "1")

{

STRSUBQUERY = "(SHORTDESC As Varchar) '' Keyword '' SubcategoryName Like '%" StrKeyword "%')

}

Else

{

IF (strSearchMode == "2")

{

Strtemp = "ProductFullName ' Cast (shortdesc as varchar) ' ' keyword ' ' subcategoryName Like'"; string strsqlkeyword = "

For (int i = 0; i

{

STRSQLKEYWORD = "%" KeywordArray [i];

}

StRTEMP = strsqlkeyword "% '";

}

IF (strSearchmode == "3")

{

strTemp = ""

For (int i = 0; i

{

Strtemp = "ProductFullName '' Cast (shortdesc as varchar) '' keyword '' subcategoryName Like '%" KeywordArray [i] "%' OR"

}

Strtemp = strTemp.trimend (new char [] {'o', 'r'});

}

strsubQuery = strTemp;

}

STRSUBQUERY = ")"

}

STRCONDITION = strsubquery;

Strcondition = ")";

Return (strcondition);

In three modes, the first specifies constructs SQL conditions similar to the following.

Field1 Field2 Field3 Like% Keyword%

Second configuration

Field1 Field2 Field3 Like% Keyword1% Keyword2% Keyword3%

Third construct

Field1 Field2 Field3 LIKE% Keyword1% or Field1 Field2 Field3 Like% Keyword2% or Field1 Field2 Field3 Like% Keyword3%

These three modes come

Among them, Keyword1, Keyword2, Keyword, is a word separated by Keyword.

Generally, customers can be assumed to separate blank characters, or, | or, or;

However, in general, customers always use spaces to separate