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
'-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'First remove the head and tail spaces
m_strDest = ltrim(rtrim(m_strDest))
'Replace &, "and" etc. with +, -, spaces
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 go
m_intBeginPos = 1
do while m_intBeginPos <> 0
m_intBeginPos = instr(m_strDest ,chr(32))
if m_intBeginPos <> 0 then 'If 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()]: After processing spaces m_strDest = " + m_strDest)
'-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'First replace single quotes with double quotes
m_strDest = replace ( m_strDest , chr(39) , chr(34))
'Set an initial value to make the loop go
m_intBeginPos = 1
m_intEndPos =1
m_strHead = ""
m_strTail = ""
do while m_intBeginPos <> 0 and m_intEndPos <> 0
'If double quotes are found, note the starting position and find the next double quote
m_intBeginPos = instr(m_strDest , chr(34))
if m_intBeginPos <> 0 then 'If the first quote is found
call print("[AnalyseKeyword()]: The location where the first quote appears: " + cstr(m_intBeginPos))
m_intEndPos = instr(m_intBeginPos + 1 , m_strDest ,chr(34))
if m_intEndPos <> 0 then 'If the second quote is found
call print("[AnalyseKeyword()]: The position where the second quote appears: " + cstr(m_intEndPos))
'Separate the entire string into three paragraphs in quotes
call print ("[AnalyseKeyword()]: Process quotation marks m_strDest = " + m_strDest)
m_strHead = left(m_strDest , m_intBeginPos - 1)
call print ("[AnalyseKeyword()]: Process quotation mark 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 there is a + sign in the quotes, it will be processed as a character and 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()]: After processing quotation marks m_strDest = " + m_strDest)
'-----------------------------------------------------------------------------------------------------------------------------
'-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'Troubleshooting multiple plus signs, if multiple plus signs are encountered, they are considered strings, not logical characters
m_strDest = replace (m_strDest , "+++" ,"|||")
m_strDest = replace (m_strDest , "++" , "||")
call print ("[AnalyseKeyword()]: After processing multiple minus signs is completed m_strDest = '" + m_strDest + "'")
'Treat 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 processing spaces on both sides of the minus sign m_strDest = '" + m_strDest + "'")
'-----------------------------------------------------------------------------------------------------------------------------
'-----------------------------------------------------------------------------------------------------------------------------
'Troubleshooting the problem of multiple minus signs. When encountering multiple minus signs, they are considered to be strings, not logical characters.
m_strDest = replace (m_strDest , "---" ,"~~~")
m_strDest = replace (m_strDest , "--" , "~~")
call print ("[AnalyseKeyword()]: After processing multiple minus signs is completed m_strDest = '" + m_strDest + "'")
'Treat 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 spaces on both sides of the plus sign m_strDest = '" + m_strDest + "'")
'-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
'-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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
'-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
m_strDest = replace(m_strDest , "--" , "~~")
m_strDest = replace(m_strDest , "++" , "||")
m_strDest = replace(m_strDest , chr(32) , "@")
AnalyseKeyword = m_strDest
call print ("[AnalyseKeyword()]: After all processing is completed m_strDest = '" + m_strDest + "'")
end function
%>
Stored procedures
/*********************************************************************/
/* proc name : Up_ParseWordSearch */
/* */
/* Description: Keyword search �
/* */
/* parameters: @a_strCategoryID Category id �
/* �
/* �
/* �
/* */
/* 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) --Conditional Part of the Sql Statement
declare @m_strSqlSelect varchar(255) -Sql statement selection part
declare @m_strSqlCategory varchar(100) -Classification Part of SQL Statement
/*Determine the selection part of SQL based on the call location*/
select @m_strSqlSelect
= case
When @a_intPosition = 4 then �
"select ProductID , 'Title' = ProductName , 'Description' = left(Description , 100) "
+ " from Product where "
when @a_intPosition = 5 then �
"select ID , Title ,'Description' = left(convert(varchar,content) , 100) "
+ " from BusinessChance where "
when @a_intPosition = 6 then �
"select CompanyID , 'Title' = CompanyName , 'Description' =left(Description , 100) "
+ " from Company where "
end
/*Determine the classification part of sql based on the classification ID*/
select @m_strSqlCategory
= case
when @a_strCategoryID <> "0" then " CategoryID like '" + @a_strCategoryID + "%' and "
else ""
end
/*Determine the conditional part of SQL based on the call location*/
select @m_strSqlCondition
= case
when @a_intPosition = 4 --commodity
then "(ProductName like '%" + @a_strParseWord + "%'"
+ " or Description like '%" + @a_strParseWord + "%'"
+ " or ProducerName like '%" + @a_strParseWord + "%') "
When @a_intPosition = 5 �
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