Post reply

Parsing a parameter text field to behave like a search engine

  • January 23, 2013 at 11:48 am

    #269312

    Good Day Everyone,

    Is there a way to implement a basic 'search engine' using a text field parameter? For instance by parsing a value containing "(Customer AND sales) OR (Credit)" ? Should I consider looking into creating a dynamic dataset for instance ?

    I could not find any related search on this folder "Reporting Services » Reporting Services 2008 Development" although it appears to be quite an interesting feature.

    Thank you for your time and suggestions,

    Don

  • January 23, 2013 at 12:15 pm

    #1580227

    it's certainly possible, but I'm not sure if you can integrate it into Reporting Services:

    check out this great article here on SQL Server Central, which has the coding and explanation to get started:

    A Google-like Full Text Search[/url]

    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!

  • January 23, 2013 at 12:49 pm

    #1580239

    Hello Lowel,

    First let me thank you for such a quick response.

    Now before going any further, it is my understanding that the CONTAINS predicate can only be used with a full-text indexed context. Provided that I could implement it, would using this technique guarantee me from retrieving all the results that would match the criteria given in the predicate ? Or would I risk a case for specific record not being retrieved ?

    I ask this very naive question because as I've run a quick test by creating a full-text index on a field of the table I would like to query - let's call it "CustomerResponse" - I observed this behavior. (I am not familiar at all with the FULL-TEXT features)

    SELECT myTableID FROM myTable CONTAINS(CustomerResponse,'MyFirstKeyword AND MySecondKeyword')

    => results : 28 rows

    SELECT myTableID FROM myTable WHERE CustomerResponse LIKE '%MyFirstKeyword%' AND CustomerResponse LIKE '%MySecondKeyword%'

    => results : 38 rows

    I think my question now goes beyond the original context of my post.

    Thank you,

    Don

  • January 23, 2013 at 1:00 pm

    #1580245

    Donbuz,

    What you'll end up with here is nearly pure dynamic SQL. True search engine mechanics will have to have an interpreter turn 'english' style engine mechanics into a usable query. Be forewarned, you're going to HAVE to do a lot of input cleansing to make sure an aggravated user can't break anything, or worse, an experienced hacker getting access to anything they shouldn't.

    I've built these before and you can get close to it, but if you're looking to mimic Yahoo or Google, you're looking at a from scratch build your own. They end up being very complex and over half the code is anti-maliciousness checking.

    No real easy way to do it.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • January 23, 2013 at 1:07 pm

    #1580249

    Thank you for this input Craig.

    Security matters are definitely a great concern when dealing with user inputs and dynamic SQL.

    I doubt the ratio cost/benefice of such an implementation in my project is really worth it.

    Take care,

    Don

  • January 23, 2013 at 3:45 pm

    #1580294

    donbuz (1/23/2013)

    Thank you for this input Craig.

    Security matters are definitely a great concern when dealing with user inputs and dynamic SQL.

    I doubt the ratio cost/benefice of such an implementation in my project is really worth it.

    Yeah, sorry Don, wish I had a better answer for you. Search engine mechanics like this are usually not meant for side projects, but usually reserved for significant user interface designs. I've built a couple for e-commerce sites and even then we limited functionality simply because designing for all the search engine pieces (which is googles's main point, and wasn't ours) was far too complex for our ROI.

    Unfortunately, I couldn't repost any of that code because it's all under NDA's, even if I had kept copies of it.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

Viewing 6 posts - 1 through 5 (of 5 total)

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