Syntax that will vary the SQL "LIKE" statement 1

[KingElvis]

Probably a simple question, but...

A viewer selects a radiobox indicating "Exact Surname" or "Partial Surname". A variable called "SearchType" will then be assigned the value "=" or "LIKE"

My SQL code must then do a search using "=" or "LIKE' depending on which radiobox was selected.

I can't seem to modify my code so that this rather simple feature will work. Any help?

My existing code is:

<%
parts=Split(strSearch," ")
strSQL = " "& FieldSelect &" LIKE '%" &parts & "%'"
for n=LBound(parts)+1 to UBound(parts)
strSQL = strSQL&" OR "& FieldSelect &" LIKE '%" &parts&"%' "
next
strSQL = "SELECT * FROM TableName WHERE "&strSQL & " ORDER BY entry, sort;"
%>

 

[Chopstik]

I think I understand. If so, then you just need to create an if/then statement within your strSQL that is looking for the "SearchType" variable.

<%
    parts=Split(strSearch," ")
    strSQL = " "& FieldSelect &" LIKE '%" &parts(n) & "%'"
    for n=LBound(parts)+1 to UBound(parts)
    strSQL = strSQL&" OR "& FieldSelect
      [COLOR=red]if SearchType = "=" then
        strSQL = " = '" & parts(n) & "' "
      else
        strSQL = " LIKE '%" &parts(n)&"%' "
      end if[/color]
    next
    strSQL = "SELECT * FROM TableName WHERE "&strSQL & " ORDER BY entry, sort;"
%>

------------------------------------------------------------------------------------------------------------------------
"The major difference between a thing that might go wrong and a thing that cannot possibly go wrong is that when a thing that cannot possibly go wrong goes wrong it usually turns out to be impossible to get at or repair."
--Dou

 

[KingElvis]

I wanted the variable "SearchType" inserted into the code something like this:


parts=Split(strSearch," ")
strSQL = " "& FieldSelect &" "& SearchType&" '%" &parts & "%'"
for n=LBound(parts)+1 to UBound(parts)
strSQL = strSQL&" OR "& FieldSelect

 

[Chopstik]

I'm not sure right offhand that this code wouldn't work. The only issue would be the '%' that would show as part of the string if it were = instead of their normal functionality if it were a LIKE statement. That was part of why I suggested the use of the if/then statement.

------------------------------------------------------------------------------------------------------------------------
"The major difference between a thing that might go wrong and a thing that cannot possibly go wrong is that when a thing that cannot possibly go wrong goes wrong it usually turns out to be impossible to get at or repair."
--Dou

 

[KingElvis]

Could you just recheck the syntax you gave me. Doesn't seem to work.


Thanks

 

[Chopstik]

Actually, the example I provided was just an example, not meant to be taken at face value. Sorry, I should have clarified. But I did see one issue when I looked at it again. Try this and see if it helps:

<%
    parts=Split(strSearch," ")
    strSQL = " "& FieldSelect &" LIKE '%" &parts(n) & "%'"
    for n=LBound(parts)+1 to UBound(parts)
    strSQL = strSQL&" OR "& FieldSelect
      if SearchType = "=" then
        strSQL = [COLOR=red]strSQL & [/color]" = '" & parts(n) & "' "
      else
        strSQL = [COLOR=red]strSQL & [/color]" LIKE '%" &parts(n)&"%' "
      end if
    next
    strSQL = "SELECT * FROM TableName WHERE "&strSQL & " ORDER BY entry, sort;"
%>

------------------------------------------------------------------------------------------------------------------------
"The major difference between a thing that might go wrong and a thing that cannot possibly go wrong is that when a thing that cannot possibly go wrong goes wrong it usually turns out to be impossible to get at or repair."
--Dou

 

[KingElvis]

Sorry, still not working. The Form page contains this code:


'''''''''''''''''''''''''''
if request("SearchType") = "NormSearch" then
strSearch = request.QueryString("search")
stype = "LIKE"
SoundexText = " a surname or partial surname of "
FieldSelect = "Search"
end if

if request("SearchType") = "ExactSearch" then
strSearch = request.QueryString("search")
stype = "="
SoundexText = " a an exact surname of "
FieldSelect = "Search"
end if
''''''''''''''''''''''''''''''''''
And the new SQL Code is as follows:

parts=Split(strSearch," ")
strSQL = " "& FieldSelect &" LIKE '%" &parts & "%'"
for n=LBound(parts)+1 to UBound(parts)
strSQL = strSQL&" OR "& FieldSelect

if sType = "=" then
strSQL = strSQL & " = '" & parts & "' "
else
strSQL = strSQL & " LIKE '%" &parts&"%' "
end if
next
strSQL = "SELECT * FROM GhettoII WHERE "&strSQL & " ORDER BY entry, sort;"

 

[KingElvis]

Hi Chopstik,

I've just realized that there are TWO "LIKE" functions in my code. You only dealt with the second one.

 

[Chopstik]

After you finish building your SQL statement, do a response.write strSQL and then a response.end to view the actual SQL statement and see if you can track down the problem that way. Copy and paste it into Query Analyzer (if using SQL Server) or the equivalent for whichever DB you are using and see if that will help.

Incidentally, your FieldSelect is always going to be the word "Search" instead of your field name based on what you have above. Is that what you intended or am I missing something?

------------------------------------------------------------------------------------------------------------------------
"The major difference between a thing that might go wrong and a thing that cannot possibly go wrong is that when a thing that cannot possibly go wrong goes wrong it usually turns out to be impossible to get at or repair."
--Dou

 

[Chopstik]

Ok, you've lost me. I'm not sure what you're referring to when you say you have two LIKE statements and I've only dealt with the second one. Could you clarify?

------------------------------------------------------------------------------------------------------------------------
"The major difference between a thing that might go wrong and a thing that cannot possibly go wrong is that when a thing that cannot possibly go wrong goes wrong it usually turns out to be impossible to get at or repair."
--Dou

 

[KingElvis]

Look at the code in my first posting. The clause "LIKE" appears on line 2 and on line 4. Fro what I see, we have only dealt with the second "LIKE"

Thanks

 

[Chopstik]

So, couldn't you just apply the same logic to it? Or, even better, just get rid of the first one and set your for/next statement to start with the first record as opposed to the second? Something like this:

<%
    parts=Split(strSearch," ")
    for n=LBound(parts) to UBound(parts)
    strSQL = strSQL&" OR "& FieldSelect
      if SearchType = "=" then
        strSQL = strSQL & " = '" & parts(n) & "' "
      else
        strSQL = strSQL & " LIKE '%" &parts(n)&"%' "
      end if
    next
    strSQL = "SELECT * FROM TableName WHERE "&strSQL & " ORDER BY entry, sort;"
%>

------------------------------------------------------------------------------------------------------------------------
"The major difference between a thing that might go wrong and a thing that cannot possibly go wrong is that when a thing that cannot possibly go wrong goes wrong it usually turns out to be impossible to get at or repair."
--Dou

 

[Turkbear]

Hi, Here's a radical idea, why not use LIKE regardless
of the selection?
If it is
= 'value'
it is also

like '%value%'

 

[Chopstik]

That would be an idea but it would also return results if the record read - 'value' or 'supervalue', etc. If he only wants the 'value' records, then it would have to be explicit. But that is an option.

------------------------------------------------------------------------------------------------------------------------
"The major difference between a thing that might go wrong and a thing that cannot possibly go wrong is that when a thing that cannot possibly go wrong goes wrong it usually turns out to be impossible to get at or repair."
--Dou

 

[Turkbear]

Hi,
Would not the original method do the same?

The query does not know if it is a exact or a partial name that it is searching for.

Using the 2 %s will always return anything that has the search criteria within it in any position.

If the partial name would always be the first part of the name then using 1 % ( i.e. LIKE '" &parts&"%' " )
would prevent that behavior.

 

[KingElvis]

My ACCESS database has two unique columns...

a) A column that lists ALL surnames in a specific record. eg, the field in this column might contain the entry "JONES SMITH BROWN".

b) A column that lists the "soundex" equivalent of these names. It might read something like "435000 637680 894567"
(You can see this 'soundex' in action at :

http://www.shoreshim.org/searchdb.asp

 

[Chopstik]

KingElvis,

Did you resolve this problem?

------------------------------------------------------------------------------------------------------------------------
"The major difference between a thing that might go wrong and a thing that cannot possibly go wrong is that when a thing that cannot possibly go wrong goes wrong it usually turns out to be impossible to get at or repair."
--Dou

 

[MakeItSo]

Hi guys,

just to add my five cents: you could use LIKE for any case.
Then you only need one line to distinguish:

if SearchType <> "=" then parts(n) = "%" & parts(n) & "%"
strSQL = strSQL & " = '" & parts(n) & "' "

background:

if the record read - 'value' or 'supervalue', etc.

"WHere searchvalue LIKE 'value' (without % signs) will return the same as the = statement.
;-)

Cheers,
Andy


[blue]The last voice we will hear before the world explodes will be that of an expert saying:
"This is technically impossible!" - Sir Peter Ustinov[/blue]
andreas.galambos@bowneglobal.de
HP:

http://andygalambos.de