A type of string search.

  • Charles Deaton

    Old Hand

    Points: 372

    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.

  • Lowell

    SSC Guru

    Points: 323398

    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: 53197

    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

    Old Hand

    Points: 372

    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: 182268

    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: 323398

    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: 13942

    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

    Old Hand

    Points: 372

    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: 182268

    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

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

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