Boolean search: Jim Bob and Bob

Factual Questions
1

Let’s say I have a database full of customer records, and in there are two people that often come in together named Jim Bob and Bob. I have the following counts of records:

Jim Bob: 100
Jim Bob and Bob: 50
Bob: 10

I need a boolean search that gives me any occurances of Bob the person. I can’t just do “Bob”, though, because then I get the top 100 records. I can’t do “Bob and not (Jim Bob)” because that only gives me 10 records, cutting both the top and middle sets.

I need a search that will say “All records that have Bob without Jim in front of it, even if Jim Bob shows up somewhere else in the record.”

I’ve stumped five people so far. Help!

2

What’s the record structure? Can you search for “Bob” and “and Bob”?

3

The records don’t actually say “and Bob”. They just have the name(s) scattered throughout. I can also use “adjacent” and “within”.

4

Can you define variables on-the-fly that behave as data fields, such that they can be searched on?

Let ([ jb = “Jim Bob”);
twobobs = Case (PatternCount (YourTable.YourColumn; “Bob”)>1; 1)
];

Search = Bob not “Jim Bob” +
Search = twobobs = 1
)

5

I’m not sure it can be done with simple Boolean searches. Your records are of four types:[ol][li]Records containing neither “Bob” nor “Jim Bob”.[]Records containing “Bob” but not “Jim Bob”.[]Records containing “Jim Bob” but not “Bob”.Records containing both “Jim Bob” and “Bob”.[/ol]You want records of types 2 and 4. The query “Bob” will return records of types 2, 3, and 4; the query “Jim Bob” will return records of types 3 and 4. Neither of these queries differentiates between types 3 and 4, so any query constructed out of the basic queries “Bob” and “Jim Bob” (along with AND, OR, NOT) that returns the records of type 4 must also return the records of type 3.[/li]
In other words, you’re going to need something other than just the queries “Bob” and “Jim Bob” and Boolean operators to get this to work. The “adjacent” operator might help you here, but I don’t immediately see how.

6

[quote=“MikeS, post:5, topic:610738”]

[ol][li]Records containing neither “Bob” nor “Jim Bob”.[]Records containing “Bob” but not “Jim Bob”.[]Records containing “Jim Bob” but not “Bob”.Records containing both “Jim Bob” and “Bob”.[/ol][/li][/QUOTE]

Can you think of a way to do 3? “Jim Bob but not Bob”? 'Cause then I can just subtract that from a “Bob” search.

7

I’d do a find-and-replace and rename Jim Bob as JB.

8

If you can do wild cards, then I think the following query will work to select records of type #3:

“Jim Bob” AND NOT (“Bob * Jim Bob” OR “Jim Bob * Bob”)

where the asterisks stand for any number of letters/names between the two. The expression in parentheses (without the NOT) would select only records of type #4. The easiest overall query would then be

(“Bob” AND NOT “Jim Bob”) OR “Bob * Jim Bob” OR “Jim Bob * Bob”

This only works if the names are all in the same field of each record, or your system’s wild card searches can span multiple fields.

Beyond that, I’m still stumped. What does the “within” query you mentioned do?

9

you have to declare a variable that references the first few letters in the variable.

syntax varies with the language. Typically you use Name(1:3) which says start in byte 1 and use three bytes.

Name := DB_Name

Select Name(1:3)=‘Bob’

that eliminates ‘Jim Bob’