Re: OT - sql query

by "rudy limeback" <r937(at)interlog.com>

 Date:  Thu, 23 Mar 2000 01:00:40 -0500
 To:  "Laurie M. Landry" <lmlweb(at)lmlweb.com>,
<hwg-techniques(at)hwg.org>
  todo: View Thread, Original
>SELECT * FROM Customers where Country LIKE '%" &
>Request.QueryString("keyword") & "%%%'"


hi laurie

sorry don't know asp but i do know sql (i'm guessing sql/server, right?)

those asp percent signs are confusing me so let's write it in plain sql
first

  select *
    from customers
  where country like '%foo%'

here the percents are the sql/server wildcard character

note the above will find any of the following countries

  anythingfoo
  anyfoothing
  fooanything

if you want *only* results like fooanything, then leave off the first
percent

testing multiple columns requires either an AND or an OR condition,
depending on what you want -- probably the OR

  select *
    from customers
  where country like '%foo%'
     or city like '%bar%'

notice what happens if bar is a zero-length string, the second condition
devolves to

   or city like '%%'

which is exactly the kind of behaviour one would expect if there was no
entry in the form field for city -- i.e. the user would expect all cities
if she doesn't type in a search string in the city field

as for substituting the search strings in the sql using asp, sorry, can't
help you there -- i could if it was cold fusion  ;o)

<tip>
test your sql first in query analyzer before running it from the web page
</tip>

_____________
rudy limeback
http://r937.com/
http://evolt.org/

HWG hwg-techniques mailing list archives, maintained by Webmasters @ IWA