1

I have a single table in which I'm trying to run an SQL query (Classic ASP). The table is structured like so:

ID  Name    City        State   Zip     Country
1   Name1   Los Angeles CA      90210   USA
2   Miami   New York    NY      10011   USA
3   Name3   Miami       FL      33184   USA

I'd like to be able to run a query that searched multiple columns (e.g. "Miami, FL) and returns the proper results.

The search comes in as Name=SearchTerm, and my current SQL query is:

fp_sQry="SELECT * FROM tableName WHERE ((Shiur = '2') or (Shiur IS NULL)) AND ((City LIKE '::Name::%') or (State LIKE '%::Name::') or (Name LIKE '%::Name::%') or (Zip LIKE '%::Name::%') or (Country LIKE '%::Name::%')) AND (Nusach LIKE '%::Nusach::%') ORDER BY Name ASC"

As it stands, I am only able to run a search for a single column, so Name=Miami actually searches all columns and returns all the proper results, whereas Name=Miami,+FL or Name=Miami+FL returns 0 results.

Any ideas on how to tweak my query to match for multiple parts of the query in several columns?

e_known
  • 535
  • 1
  • 6
  • 16

1 Answers1

3

Looks to my like you're trying to do a full-text search on a backend that doesn't support full-text searching. You'd probably be better off switching from Access to SQL Server. IIRC even the Express edition supports full-text search these days.

On Access you can kind of emulate full-text search by modifying your query like this:

SELECT *
FROM tableName
WHERE (Shiur = '2' OR Shiur IS NULL)
  AND City + State + Name + Zip + Country LIKE '%::Name::%'
  AND Nusach LIKE '%::Nusach::%'
ORDER BY Name ASC

Note that all concatenated fields (City + State + Name + Zip + Country) must be of the same datatype.

However, even with that, you'll still have to parse your input string and modify your query when you want to search for more than one term. For your example input Miami+FL you'd need to split that string:

inputString = "Miami+FL"
searchTerms = Split(inputString, "+")

modify the query to something like this:

SELECT *
FROM tableName
WHERE (Shiur = '2' OR Shiur IS NULL)
  AND City + State + Name + Zip + Country LIKE '%::param1::%'
  AND City + State + Name + Zip + Country LIKE '%::param2::%'
  AND Nusach LIKE '%::Nusach::%'
ORDER BY Name ASC

and pass searchTerms(0) and searchTerms(1) as 2 parameters to the query.

Ansgar Wiechers
  • 193,178
  • 25
  • 254
  • 328
  • This answered helped clarify a few things, thanks a bunch! Still not working, with `DIM searchTerm, inputSplit searchTerm = Request.querystring("Name") inputSplit = Split(searchTerm, ",")` and `fp_sQry="SELECT * FROM tblShuls WHERE ((Shiur = '2') or (Shiur IS NULL)) AND City + State + Name + Zip + Country LIKE '%::response.write searchTerm::%' or (City = '::City::') or (Country = '::Country::') AND (Nusach LIKE '%::Nusach::%') ORDER BY Name ASC"` I end up getting back results with all the entries in my database, so they apparently all qualify for this very open wildcard. – e_known Jul 02 '13 at 12:52
  • I don't think the `response.write searchTerm` belongs there. However, you should use parameterized queries anyway (see the last link in my answer or the answers to [this question](http://stackoverflow.com/q/1092512/1630171)). – Ansgar Wiechers Jul 02 '13 at 13:00