Solved

Boolean Searches

Posted on 2001-06-06
21
551 Views
Last Modified: 2010-05-18
Hi,

I am trying to make it so someone can search my database for something like:

5 and 30 not 31

-or-

john and jane

-or-

candle or wax

-or-

stereo and speakers not radio

I'm sure you get the point.

The point is I don't know what they are going to search for, so my frontend program (written in VB) can't just search based on knowing what fields.  I can't do a freetext file or whatever it is - so is it possible to do boolean searches.  Or take a string (from the combo box where they enter the search) and build it to search the DB based on that criteria?  IF I need to be a bit more clear let me know.
0
Comment
Question by:Cusack
  • 10
  • 6
  • 3
  • +1
21 Comments
 
LVL 32

Expert Comment

by:bhess1
ID: 6160525
Looking at your example selections, you'll need:

(1) A parsing module, to separate the conditions into their logical components

(2) A query builder.

Example:

stereo and speakers not radio

The WHERE for this would be:

WHERE MyField LIKE '%stereo%' AND MyField Like '%speakers%' AND MyField NOT Like '%radio%'

So, your parsing code needs to be something like:

Function ParseConditions(ByVal Cond as String, ByVal CompareFieldName As String) As String
Dim iPos as Integer, sWork as String
Dim sItems() As String
Dim bLastLogical As Integer

Const cAND = 1
Const cOR = 2
Const cNOT = 3

If Trim(Cond) = "" Then
   ParseConditions = ""
   Exit Function
End If

sItems = Split(Cond," ")
iPos = 0
bLastLogical = True
Do While iPos <= UBound(sItems)
   Select Case UCase(sItems(iPos))
   Case "AND"
      If bLastLogical = 0 AND iPos <> UBound(sItems) Then
         sWork = sWork & " AND " & CompareFieldName & " LIKE '%"
         bLastLogical = cAND
      End If
   Case "OR"
      If bLastLogical = 0 AND iPos <> UBound(sItems) Then
         sWork = sWork & " OR " & CompareFieldName & " LIKE '%"
         bLastLogical = cOR
      End If
   Case "NOT"
      If bLastLogical = 0 AND iPos <> UBound(sItems) Then
         sWork = sWork & " AND " & CompareFieldName & " NOT LIKE '%"
         bLastLogical = cNOT
      End If
   Case ""
      ' Ignore - extra space
   Case Else
      If sWork = "" Then sWork = "WHERE " & CompareFieldName & " Like '%"
      sWork = sWork & sItems(iPos) & "%'"
      bLastLogical = 0
   End Select
   iPos = iPos + 1
Loop
If bLastLogical <> 0 Then
   SELECT CASE bLastLogical
   CASE True
      sWork = ""
   CASE cAND
      sWork = Right(sWork, Len(sWork) - (Len(CompareFieldName) + 13)
   CASE cOR
      sWork = Right(sWork, Len(sWork) - (Len(CompareFieldName) + 12)
   CASE cNOT
      sWork = Right(sWork, Len(sWork) - (Len(CompareFieldName) + 17)
   End Select
End If
ParseConditions = sWork
End Function
0
 

Author Comment

by:Cusack
ID: 6160760
If I do it that way won't the SQL string end up too long?  or is that not an issue?

The other thing...lets say they do

Radio and 300 not 2000

so I could end up needing to call three separate fields - but I don't know what they are going to search for...this is going to be a major pain in the arse!

so in the above example...

Item = radio

price = 300

year != 2000

Your example won't find the fields that match that right?  Only if they are all in the same field...?
0
 

Author Comment

by:Cusack
ID: 6160764
by the way that's why I made this a 300 point question...I knew it was going to a difficult answer!  I have spent the last 2 weeks trying to figure this out (SQL newbie here)
0
 
LVL 1

Expert Comment

by:falkor
ID: 6160812
Interesting problem.  Essentially, it sounds like you need to reporduce the access query builder for your users.  Can you tell me a little more about your table - field count etc...?  Do you need to to simulate joins or unions?  Please say no.  It sounds like a neat challange.  One suggestion: for OR conditions on the same field, I normally like to generate a dynamic IN list.
0
 

Author Comment

by:Cusack
ID: 6160819
tmpVar = Split(tmpSQL, Chr(32))

For L = LBound(tmpVar) To UBound(tmpVar)
                   
                    If LCase(tmpVar(L)) = "and" Then
                        tmpBuild = tmpBuild & " AND"
                    ElseIf LCase(tmpVar(L)) = "or" Then
                        tmpBuild = tmpBuild & " OR"
                    ElseIf LCase(tmpVar(L)) = "not" Then
                        stbs = True
                        stbs2 = True
                    Else
                        If stbs = False Then
                            tmpBuild = tmpBuild & " [Field*] LIKE '%" & tmpVar(L) & "%'"
                        Else
                            stbs = False
                            tmpBuild = tmpBuild & " AND [Field*] NOT LIKE '%" & tmpVar(L) & "%'"
                        End If
                    End If
       
                Next

' then I replace the [Field*] with my fields names

I think that's pretty much the same as what you gave me...the problem is I can't let the user do a search for 'radio and 2001', or 'radio and sony' as examples.  I have another window that lets people do stuff like that - where I prefill data based on the DB...but a search window is critical!
0
 

Author Comment

by:Cusack
ID: 6160829
Falkor, I have 20 fields, one table...however it can be done is fine by me.  As far as I know I don't need joins or unions...  A dynamic IN list sounds like a good idea that I hadn't thought of!
0
 
LVL 1

Expert Comment

by:falkor
ID: 6160832
Interesting problem.  Essentially, it sounds like you need to reporduce the access query builder for your users.  Can you tell me a little more about your table - field count etc...?  Do you need to to simulate joins or unions?  Please say no.  It sounds like a neat challange.  One suggestion: for OR conditions on the same field, I normally like to generate a dynamic IN list.
0
 
LVL 32

Expert Comment

by:bhess1
ID: 6160852
Hmmm... you may need to have multiple condition boxes on your front end, for the fields needed for the compare.  Consider something like:

Item Desc: ________________
Price: __________________
Year: ___________________
etcetera...

Think of how a price matching or item finding web site works (e.g. Travelocity).  You need to specify multiple different fields when defining your query on these sites, and your front end will either need to match that sort of logic, or require entering the field name with each criteria entered (not a good idea).

If you have separate input fields, you can also have different types of parsing.  For example, with your Price field, you could have the user entering a maximum dollars amount, and build your query directly like:

"PRICE <= " & txtPrice.Text

Now, assuming you have multiple text fields (which is what the above parsing logic is set to handle), you would call the function multiple times:

Sub cmdGo_Click ()
Dim sWork as String, sFinal as String
Dim sQuery as String

If txtDescConditions <> "" Then
   sWork = ParseConditions(txtDescConditions, "Description")
   sFinal = sWork
End If

If txtReviewConditions <> "" Then
   sWork = ParseConditions(txtReviewConditions, "Review")
   If sFinal = "" Then
      sFinal = sWork
   Else
      sFinal = sFinal & " AND " & Mid(sWork, 7)
   End If
End If

If txtStoreLocConditions <> "" Then
   sWork = ParseConditions(txtStoreLocConditions, "Store_Location")
   If sFinal = "" Then
      sFinal = sWork
   Else
      sFinal = sFinal & " AND " & Mid(sWork, 7)
   End If
End If

....

Then, handling your numeric fields:

If Val(txtPriceCond) > 0 Then
   If sFinal = "" Then sFinal = "WHERE " Else sFinal = " AND "
   sFinal = sFinal & "Price <= " & Format(Val(txtPriceCond),"##0.00")
End If

....

Finally, build your entire query:

sQuery = "SELECT * From MyTable"
If sFinal <> "" Then sQuery = sQuery & " " & sFinal
Set rs = adoConn.Open sFinal

0
 
LVL 32

Expert Comment

by:bhess1
ID: 6160853
Hmmm... you may need to have multiple condition boxes on your front end, for the fields needed for the compare.  Consider something like:

Item Desc: ________________
Price: __________________
Year: ___________________
etcetera...

Think of how a price matching or item finding web site works (e.g. Travelocity).  You need to specify multiple different fields when defining your query on these sites, and your front end will either need to match that sort of logic, or require entering the field name with each criteria entered (not a good idea).

If you have separate input fields, you can also have different types of parsing.  For example, with your Price field, you could have the user entering a maximum dollars amount, and build your query directly like:

"PRICE <= " & txtPrice.Text

Now, assuming you have multiple text fields (which is what the above parsing logic is set to handle), you would call the function multiple times:

Sub cmdGo_Click ()
Dim sWork as String, sFinal as String
Dim sQuery as String

If txtDescConditions <> "" Then
   sWork = ParseConditions(txtDescConditions, "Description")
   sFinal = sWork
End If

If txtReviewConditions <> "" Then
   sWork = ParseConditions(txtReviewConditions, "Review")
   If sFinal = "" Then
      sFinal = sWork
   Else
      sFinal = sFinal & " AND " & Mid(sWork, 7)
   End If
End If

If txtStoreLocConditions <> "" Then
   sWork = ParseConditions(txtStoreLocConditions, "Store_Location")
   If sFinal = "" Then
      sFinal = sWork
   Else
      sFinal = sFinal & " AND " & Mid(sWork, 7)
   End If
End If

....

Then, handling your numeric fields:

If Val(txtPriceCond) > 0 Then
   If sFinal = "" Then sFinal = "WHERE " Else sFinal = " AND "
   sFinal = sFinal & "Price <= " & Format(Val(txtPriceCond),"##0.00")
End If

....

Finally, build your entire query:

sQuery = "SELECT * From MyTable"
If sFinal <> "" Then sQuery = sQuery & " " & sFinal
Set rs = adoConn.Open sFinal

0
 
LVL 32

Expert Comment

by:bhess1
ID: 6160866
One more note:  I should modify the CASE in my original entry to handle the correct entry:

not philadelphia

--------------

Select Case UCase(sItems(iPos))
  Case "AND"
     If bLastLogical = 0 AND iPos <> UBound(sItems) Then
        sWork = sWork & " AND " & CompareFieldName & " LIKE '%"
        bLastLogical = cAND
     End If
  Case "OR"
     If bLastLogical = 0 AND iPos <> UBound(sItems) Then
        sWork = sWork & " OR " & CompareFieldName & " LIKE '%"
        bLastLogical = cOR
     End If
  Case "NOT"
' Change - Handle initial NOT in string
     If bLastLogical = 0 AND iPos <> UBound(sItems) Then
        sWork = sWork & " AND " & CompareFieldName & " NOT LIKE '%"
        bLastLogical = cNOT
     ElseIf bLastLogical < 0 AND iPos <> UBound(sItems) Then
        If sWork = "" Then
           sWork = "WHERE " & CompareFieldName & " NOT LIKE '%"
        Else
           sWork = sWork & " AND " & CompareFieldName & " NOT LIKE '%"
        End If
     End If
  Case ""
     ' Ignore - extra space
  Case Else
     If sWork = "" Then sWork = "WHERE " & CompareFieldName & " Like '%"
     sWork = sWork & sItems(iPos) & "%'"
     bLastLogical = 0
  End Select
  iPos = iPos + 1
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 1

Expert Comment

by:falkor
ID: 6160874
On your interface, do you have them typing in their search criteria, or selicting from multi select list boxes, already populated with distinct lists?
0
 

Author Comment

by:Cusack
ID: 6160881
I have one window where there are populated list and combo boxes...but this isn't involving that...it's a combo box (just because I have a search history) where they would type something like:

radio and sony not 2000
0
 

Author Comment

by:Cusack
ID: 6160902
bhess1...that's a lot of code (which maybe fine) however I am a little confused on one point...are you building the SQl string so it tries to satisfy each thing?

Something like this? (I'm going to use two fields for simplicity)

Search term: Sony and radio not 2000

select * from mytable where myfield1 like %parm(1)% or myfield2 like %parm(1) and myfield1 like %parm(2)% or myfrield2 like %parm(2)% and myfield1 not like %parm(3)% and myfield2 not like %parm(3)%

Am I corrrect?
0
 

Author Comment

by:Cusack
ID: 6160912
select * from mytable where myfield1 like %Sony% or myfield2 like %Sony% and (myfield1 like %radio% or myfrield2 like %radio%) and (myfield1 not like %2000% and myfield2 not like %2000%)
0
 

Author Comment

by:Cusack
ID: 6160916
something like that?  Sorry this is a bit over my head...I have a bad grasp on anything above simple select/update/insert/delete statements.
0
 
LVL 32

Expert Comment

by:bhess1
ID: 6160988
No, not at all.  Basically, I am recommending that you input conditions by field.  Certainly, it is possible to check all possible fields for all possible conditions, but this is *NOT* an efficient way to query a database.  Your overhead and query execution time would be horrendous.

Instead, you need to specify your conditions separately for each field that can have conditions, and parse out the conditions into one large WHERE field.

In my last post, at the top, there was a quick example:

Item Desc: ________________
Price: __________________
Year: ___________________
etcetera...

In this example, you might enter:

Item Name: coat
Item Desc: green or blue not pink
Price: 199.95
Year: ___________________

Processing this, your code might look like my example above, with different field names:

Sub cmdGo_Click ()
Dim sWork as String, sFinal as String
Dim sQuery as String

If txtNameConditions <> "" Then
  sWork = ParseConditions(txtNameConditions, "Item_Name")
  sFinal = sWork
End If

If txtDescriptionConditions <> "" Then
  sWork = ParseConditions(txtDescriptionConditions, "Item_Description")
  If sFinal = "" Then
     sFinal = sWork
  Else
     sFinal = sFinal & " AND (" & Mid(sWork, 7) & ")"
  End If
End If

If Val(txtPriceCond) > 0 Then
  If sFinal = "" Then sFinal = "WHERE " Else sFinal = " AND "
  sFinal = sFinal & "Price <= " & Format(Val(txtPriceCond),"##0.00")
End If

If Val(txtYearCond) > 0 Then
  If sFinal = "" Then sFinal = "WHERE " Else sFinal = " AND "
  sFinal = sFinal & "Year = " & Format(Val(txtYearCond), "0000")
End If

sQuery = "SELECT * From MyTable"
If sFinal <> "" Then sQuery = sQuery & " " & sFinal
Set rs = adoConn.Open sFinal

End Sub

Your final query in this case would look like:

SELECT * From MyTable WHERE Item_Name LIKE '%coat%' AND (Item_Desc Like '%green%' OR Item_Desc Like '%blue' AND Item_Desc NOT Like '%pink%') AND Price <= 199.95
0
 
LVL 3

Expert Comment

by:mathavra
ID: 6161002
This is just a suggestion:

How about concatenating all the columns and use the "like" and "not like" operators in the SQL statement?. If you have number columns, you can convert them to CHAR before concatenating it.

NOTE: The biggest problem with this approach would be that the query would possibly perform a table scan. i.e. it would have slow response time.
0
 

Author Comment

by:Cusack
ID: 6161449
mathavra...can you give me an example of what you mean?

Bhess1, I agree that's the best way to do it...however, I need to have a search box that they can enter boolean search phrases in.  El Jefe demands it! haha.  I already have another window that lets the user do that, but El jefe thinks that may turn some people off...down with El jefe!
0
 
LVL 3

Expert Comment

by:mathavra
ID: 6161511
Here is sample set of code & statements:

1> use tempdb
2> go
1> create table table1
2> (
3> col1 char(10),
4> col2 money,
5> col3 char(5)
6> )
7> go
1> insert into table1 values ("John", 1000, "XXXX")
2> insert into table1 values ("Simba",2000, "Ron")
3> insert into table1 values ("John",2000, "Jane")
4> insert into table1 values ("John",1000,"Jane")
5> go
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
1>

1> select
2> col1 + convert(char(15), col2) + col3
3> from table1
4> where
5> col1 + convert(char(15), col2) + col3 like "%John%"  and
6> col1 + convert(char(15), col2) + col3 like "%Jane%"  and
7> col1 + convert(char(15), col2) + col3 not like "%2000%"
8> go

 ------------------------------
 John              1000.00Jane

(1 row affected)
0
 
LVL 32

Accepted Solution

by:
bhess1 earned 300 total points
ID: 6161584
Then El Jefe will need to provide you with the parsing logic!  ;-)

Well, let's make some assumptions, and make the code more complex:

Assumption:  You need parentheses for grouping
   Reason:  green and blue or red and yellow not puce is different from (green and blue) or (red and yellow) not puce

Assumption:  You know whether or not a field is numeric {Duh}

Assumption:  Numeric values will only compare to numeric fields.

Consider this case:

blue and xxl and 1999 not 3.5

If all fields are compared with all values, your WHERE clause might be:

(Data Fields:  Name, Desc, Year, Size, Price, Color, Country)

SELECT * From MyTable Where ((Name Like '%blue%' and Name like '%xxl%')
   OR (Desc Like '%blue%' and Desc like '%xxl%')
   OR (Size Like '%blue%' and Size like '%xxl%')
   OR (Color Like '%blue%' and Color like '%xxl%')
   OR (Country Like '%blue%' and Country like '%xxl%'))
   AND ((Year = 1999 AND NOT Year = 3.5)
   OR (Price = 1999 AND NOT Price = 3.5)

Clearly, this won't work.  Thus, you would have to add logic to your code to determine that a given field should be compared to a given value.  

An alternate (and even slower) method that should mostly return correct results would be:


Select * From (Select * From (Select * From (SELECT * From MyTable Where Name Like '%blue%' OR Desc Like '%blue%' Or Size Like '%blue%' Or Color Like '%blue%') AS A
WHERE A.Desc like '%xxl%' OR A.Size like '%xxl%' OR A.Color like '%xxl%' OR A.Country like '%xxl%') As B
WHERE B.Year = 1999 OR B.Price = 1999) As C
WHERE C.Year = 3.5 OR C.Price = 3.5


Adding an OR, or parentheses, into the computation would entail using a UNION query to generate the resultset:

(blue or green) and xxl and 1999 not 3.5

Select * From (Select * From (Select * From (SELECT * From MyTable Where Name Like '%blue%' OR Desc Like '%blue%' Or Size Like '%blue%' Or Color Like '%blue%'
UNION
SELECT * From MyTable Where Name Like '%green%' OR Desc Like '%green%' Or Size Like '%green%' Or Color Like '%green%') AS A
WHERE A.Desc like '%xxl%' OR A.Size like '%xxl%' OR A.Color like '%xxl%' OR A.Country like '%xxl%') As B
WHERE B.Year = 1999 OR B.Price = 1999) As C
WHERE C.Year = 3.5 OR C.Price = 3.5

This would be functional, but would be hell on your DB Server, and would *NOT* scale to more than a few simultaneous queries at all well, due to the multiple temp tables, table scanning, etc.

However, if conditions were entered by field:

NAME:
DESC:
COLOR:  green or blue not sky
SIZE:   xxl
YEAR:   1999     0 Equal to    0 No later than   0 No earlier than
PRICE:  3.5      0 No more than    0 Exactly

(assuming Equal To and No More Than are chosen as options)

Then your query would come out as:

SELECT * From MyTable WHERE ((Color Like '%green%' Or Color Like Blue) And Color Not Like '%sky%')
   AND (size = '%xxl%')
   AND (Year = 1999)
   AND (Price <= 3.5)

Which would execute dozens, if not hundreds, of times as fast.

Argue the case with examples like this.  The other method will be slow, will *feel* slow to users, and will not be as accurate.

Remind El Jefe that the Web searches doing things like this (I assume he's thinking something like a Yahoo or Google search) use a Full-Text index on all of the data.  That's not what he wants here (and you would have to copy all of the data in the record into one text field for it to work anyway).

Good Luck.
0
 

Author Comment

by:Cusack
ID: 6161674
bhess, I agree with your logic :)  I think I am going to copy your post and send email a dirty email saying "REVOLUTION!"  With a DB that is about 1-2 gigs this thing is gonna take 3 days to complete a query...he's gonna have to accept that fact...that'll teach him to not let me get my full-text indexing on

*DOWN WITH EL JEFFE!*

I appreciate everyone elses input, but this is gonna be too damn taxing on the system resources and is going to bog down with multiple people running queries.  So I must go with El Jeffe's I mean Bhess's comments.

Thanks :)
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now