Search programs combined with asp and stored procedures

Complex. It supports multiple logical operators, including +-and or spaces, and can be added as needed. You can search for the table based on the condition, and the speed is also optimized, which can be said to be very fast. Of course, because it was previously written, there are many problems. Almost all SQL server features are used in the stored procedure, such as the cursor (record set paging. Okay, don't blow it off. Check the program yourself.

Asp FunctionsCopy codeThe Code is as follows: function AnalyseKeyword (a_strSource)
Dim m_strDest, m_intLoop
Dim m_intBeginPos, m_intEndPos
Dim m_strHead, m_strMiddle, m_strTail
M_strDest = a_strSource

'------------------------------ Processing space ------------------------------------------------------

'First remove the leading and trailing Spaces
M_strDest = ltrim (rtrim (m_strDest ))

'Replace &, "and" with +,-, and space
M_strDest = replace (m_strDest, "&", "+ ")
M_strDest = replace (m_strDest, "AND", "+ ")
M_strDest = replace (m_strDest, "OR", chr (32 ))
M_strDest = replace (m_strDest, "NOT ","-")

'Initialize the variable to make the following Loop
M_intBeginPos = 1

Do while m_intBeginPos <> 0
M_intBeginPos = instr (m_strDest, chr (32 ))
If m_intBeginPos <> 0 then', if a space is found
M_strHead = rtrim (ltrim (left (m_strDest, m_intBeginPos )))
Call print ("[AnalyseKeyword ()]: process space m_strHead =" + m_strHead)
M_strTail = rtrim (ltrim (right (m_strDest, len (m_strDest)-m_intBeginPos )))
Call print ("[AnalyseKeyword ()]: process space m_strTail =" + m_strTail)
M_strDest = m_strHead + "*" + m_strTail
Else
Exit do
End if
Loop
M_strDest = replace (m_strDest, "*", chr (32 ))
Call print ("[AnalyseKeyword ()]: m_strDest =" + m_strDest)
'------------------------------- Space processing is completed -------------------------------------------------

'------------------- Process single double quotation marks -----------------------------------------------------

'Replace single quotes with double quotes
M_strDest = replace (m_strDest, chr (39), chr (34 ))

'Set an initial value for the loop
M_intBeginPos = 1
M_intEndPos = 1
M_strHead = ""
M_strTail = ""
Do while m_intBeginPos <> 0 and m_intEndPos <> 0
'If double quotation marks are found, write down the start position and look for the next double quotation marks.
M_intBeginPos = instr (m_strDest, chr (34 ))
If m_intBeginPos <> 0 then', if the first quotation mark is found

Call print ("[AnalyseKeyword ()]: position where the first quotation mark appears:" + cstr (m_intBeginPos ))

M_intEndPos = instr (m_intBeginPos + 1, m_strDest, chr (34 ))
If m_intEndPos <> 0 then', if the second quotation mark is found

Call print ("[AnalyseKeyword ()]: location where the second quotation mark appears:" + cstr (m_intEndPos ))

'Separate the entire string into three sections by quotation marks
Call print ("[AnalyseKeyword ()]: Process quotation marks m_strDest =" + m_strDest)
M_strHead = left (m_strDest, m_intBeginPos-1)
Call print ("[AnalyseKeyword ()]: Process quotation marks m_strHead =" + m_strHead)
M_strMiddle = mid (m_strDest, m_intBeginPos + 1, m_intEndPos-m_intBeginPos-1)
Call print ("[AnalyseKeyword ()]: Process quotation marks m_strMiddle =" + m_strMiddle)
M_strTail = right (m_strDest, len (m_strDest)-m_intEndPos)
Call print ("[AnalyseKeyword ()]: m_strTail =" + m_strTail)

'If the quotation mark contains a plus sign, it is treated as a character and is temporarily replaced with other characters.
M_strMiddle = replace (m_strMiddle, "+", "| ")
M_strDest = m_strHead + replace (rtrim (ltrim (m_strMiddle), chr (32), "#") + m_strTail

Else
Exit do
End if
Else
Exit do
End if
Loop
M_strDest = replace (m_strDest, chr (34), "+ ")
Call print ("[AnalyseKeyword ()]: m_strDest =" + m_strDest)

'------------------------------- The quotation marks have been processed -------------------------------------------------

'----------------------------- Handle the issue of spaces on both sides of multiple plus signs and plus signs -----------------------------

'Processing the problem of multiple plus signs. If multiple plus signs are encountered, they are considered as strings rather than logical operators.
M_strDest = replace (m_strDest, "++", "| ")
M_strDest = replace (m_strDest, "++", "| ")
Call print ("[AnalyseKeyword ()]: After processing multiple minus signs, m_strDest = '" + m_strDest + "'")

'Process the spaces on both sides of the plus sign
M_strDest = replace (m_strDest, "+", "+ ")
M_strDest = replace (m_strDest, "+", "+ ")
M_strDest = replace (m_strDest, "+", "+ ")
Call print ("[AnalyseKeyword ()]: After the space on both sides of the minus sign is processed, m_strDest = '" + m_strDest + "'")

'------------------------------- Process the plus sign -----------------------------

'------------------------------- Handle multiple minus signs and spaces on both sides -----------------------------

'Processing the problem of multiple minus signs. If multiple minus signs are met, they are considered as strings rather than logical operators.
M_strDest = replace (m_strDest ,"---","~~~ ")
M_strDest = replace (m_strDest ,"--","~~ ")

Call print ("[AnalyseKeyword ()]: After processing multiple minus signs, m_strDest = '" + m_strDest + "'")

'Process the spaces on both sides of the minus sign
M_strDest = replace (m_strDest ,"-","-")
M_strDest = replace (m_strDest ,"-","-")
M_strDest = replace (m_strDest ,"-","-")
Call print ("[AnalyseKeyword ()]: After processing the spaces on both sides of the plus sign, m_strDest = '" + m_strDest + "'")

'------------------------------- After the minus sign is processed -----------------------------

'------------------------------ The question of adding or subtracting numbers from both ends of the string -----------------
If len (m_strDest)> = 3 then
M_strHead = left (m_strDest, 1)
M_strMiddle = mid (m_strDest, 2, len (m_strDest)-2)
M_strTail = right (m_strDest, 1)
If m_strHead = "+" or m_strHead = "-" then
M_strHead = ""
End if
If m_strTail = "+" or m_strTail = "-" then
M_strTail = ""
End if
M_strDest = m_strHead + m_strMiddle + m_strTail
End if
'---------------------------- Processed -------------------------------------
M_strDest = replace (m_strDest ,"--","~~ ")
M_strDest = replace (m_strDest, "++", "| ")
M_strDest = replace (m_strDest, chr (32 ),"@")
AnalyseKeyword = m_strDest
Call print ("[AnalyseKeyword ()]: m_strDest = '" + m_strDest + "'")

End function
%>

Stored Procedure
/*************************************** ******************************/
/* Proc name: Up_ParseWordSearch */
/**/
/* Description: keyword search */
/**/
/* Parameters: @ a_strCategoryID category id */
/* @ A_intPosition location of the call */
/* @ A_strParseWord: Search Keyword */
/* @ A_intRowCount: Maximum number of records retrieved */
/**/
/* Date: 2000/6/28 */
/**/
/* Author: Liuyunpeng */
/**/
/* History :*/
/*************************************** ******************************/

If exists (select * from sysobjects where id = object_id ("up_ParseWordSearch "))
Drop proc up_ParseWordSearch
Go

Create proc up_ParseWordSearch @ a_strParseword varchar (255 ),
@ A_strCategoryID varchar (255 ),
@ A_intPosition tinyint,
@ A_intRowCount int
As
Declare @ m_strSqlCondition varchar (255) -- SQL statement condition Section
Declare @ m_strSqlSelect varchar (255) -- Select part of SQL statement
Declare @ m_strSqlCategory varchar (100) -- SQL statement category

/* Select the SQL statement based on the call location */
Select @ m_strSqlSelect
= Case
When @ a_intPosition = 4 then -- commodity Library
"Select ProductID, 'title' = ProductName, 'description' = left (Description, 100 )"
+ "From Product where"
When @ a_intPosition = 5 then -- business opportunity Database
"Select ID, Title, 'description' = left (convert (varchar, content), 100 )"
+ "From BusinessChance where"
When @ a_intPosition = 6 then -- company database
"Select CompanyID, 'title' = CompanyName, 'description' = left (Description, 100 )"
+ "From Company where"
End

/* Determine the SQL classification part based on the category ID */
Select @ m_strSqlCategory
= Case
When @ a_strCategoryID <> "0" then "CategoryID like '" + @ a_strCategoryID + "%' and"
Else ""
End

/* Determine the SQL condition part based on the call location */
Select @ m_strSqlCondition
= Case
When @ a_intPosition = 4 -- Item
Then "(ProductName like '%" + @ a_strParseWord + "% '"
+ "Or Description like '%" + @ a_strParseWord + "% '"
+ "Or ProducerName like '%" + @ a_strParseWord + "% ')"
When @ a_intPosition = 5 -- business opportunities
Then "(Title like '%" + @ a_strParseWord + "% '"
+ "Or Keyword like '%" + @ a_strParseWord + "% ')"
When @ a_intPosition = 6
Then "(CompanyName like '%" + @ a_strParseWord + "% '"
+ "Or Description '%" + @ a_strParseWord + "% ')"
End

Set rowcount @ a_intRowCount
Exec (@ m_strSqlSelect + @ m_strSqlCategory + @ m_strSqlCondition)
Set rowcount 0
Go