A type of string search.

Question

Post reply

A type of string search.

Charles Deaton

SSC-Addicted

Points: 482
More actions
July 11, 2011 at 1:51 pm

#240789

I’ve been asked if there is a method that would allow a search string similar to the line below coming from a web interface that can query a SQL Server database.
Car; Ford; Focus; 2005-2011; Red; $4000-$5000
The above search would search for a Red Ford Focus Car build between 2005 and 2011 costing between $4000 and $5000. It would need to allow for addition predicates and fewer. For example maybe they want only a 2 door (not sure if a Focus can have 4 doors) or do not care about the color.
Also let’s say that each database record has 50 values, not all would be allowed to be searched.
I’ve seen this type of input on web sites but can’t seem to find the method behind it.
Charlie.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

i've always done this with the user interface/web page building a dynamic SQL;

something like this (assuming all data entry comes from drop down lists, and vb.net as far as syntax:

Dim sql As String

sql = "SELECT VW.ColumnList FROM VW_SEARCH VW WHERE 1 = 1"

If Model <> String.Empty Then

sql = sql & " AND VW.Model = {0} "

sql = String.Format(sql,Model)

End If

If MinYear<> String.Empty Then

sql =sql & " AND VW.Year >= {1} "

sql = String.Format(sql,MinYear)

End If

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 2

You might also look at full-text searches and full-text indexing.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Charles Deaton SSC-Addicted Points: 482 More actions · Answer 3

I looked at full text for this and can't see that it would work. We do currently use full text but on a "comment" value stored in the table.

The query would not come from a drop down box. They would need to type is as I had.

Charlie.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

I like to keep everything in stored procedures despite some of them making use of Dynamic SQL. It makes it much simpler when refactoring your database is the topic. As a direct alternative to building an SQL statement in application code you could do all the work in a stored procedure. Please have a look at this:

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Lowell SSC Guru Points: 323518 More actions · Answer 5

if you look at edmunds.com, they have a typical search fro autos/old and new; i would avoid allowinging raw text/data entry for fields if possible; much easier to use predefined values, and it helps limit SQL injection attacks as well

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Nevyn SSChampion Points: 14210 More actions · Answer 6

Charles Deaton (7/11/2011)
I looked at full text for this and can't see that it would work. We do currently use full text but on a "comment" value stored in the table.
The query would not come from a drop down box. They would need to type is as I had.
Charlie.

You really want to give them one free form text box to enter and delimit multiple search values themselves?

Seems like it would be very inconvenient to use ... any formatting errors on their part could show up as false negatives, and validating it would be pretty nightmarish. You'd probably want to force them to give each attribute a name and value (ie: make=ford; model=focus;) to help validate, which would require even more data entry from them.

Is there a reason it has to be one data entry field with them doing the delimiters?

Charles Deaton SSC-Addicted Points: 482 More actions · Answer 7

Keep in mind this was not my idea….. I would agree this will be difficult for our users to use. However I need to press on. Back in MSSQL 7 and 2000 English Query was available and that would have worked. I’ve look at Natural Language engines and appliances also but they are expensive.

I’ll keep looking.

Charlie

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 8

This is a first for me...I never before heard anyone say they used the "English Query" sub-system. I am curious as to why it was dropped from the product since I did not come across anything citing a direct replacement offered by Microsoft, however it is a complex area of software and maybe the reason is as simple as a lack of user installations. When you arrive at a decision on a new direction, if you think of it and have a moment, please post back what you chose to implement. Thanks.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato