Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

im making a program which it asks you a question and you give it an answer. How to make this work well, ive made the answers very small. example...

Q: What is the fundametal particle that protons are made of?

A: Quarks

Thats what my database looks like anyway (RowID, Question,Answer). Now im using Microsoft Access as my database, which isnt ideal but i have to live with it, i want to query the database so in the answer field i could say...

The funcdamental particle is called a quark, Bob!

And it would see the word quark, match it in the database, and find the rest of the data irrelevant (because it is!)

My answercolumn is called answer, and this is one example of what i have tried...

SELECT * FROM Grade_Theory WHERE *Answer* LIKE 'The funcdamental particle is called a quark, Bob!'

OR

SELECT * FROM Grade_Theory WHERE 'The funcdamental particle is called a quark, Bob!' LIKE *Answer*

However none of this works, i get an "missing expected operator" error.

I hop ei have worded the question well enough :P

Thanks!

Replies are listed 'Best First'.
Re: Wildcards, SQL and Perl
by UnderMine (Friar) on Mar 13, 2004 at 20:40 UTC
    Lets start at the begining. What are those *asterixes* doing around answer? You might just be able to get away with :-
    SELECT * FROM Grade_Theory WHERE 'The funcdamental particle is called 
+a quark, Bob!' LIKE '%'||Answer||'%'

    [download]
    But I have never needed to use this reverse syntax. To be perfectly honest with you this looks less like a perl question and more like an advanced database design question.

    Ideally you need to understand the importance of the key words in the answers.

    So let us try and build up a dynamic query that tries to find important words. Your initial SQL starts off :- 

    SELECT * FROM Grade_Theory

    [download]
    Although this sounds ok it can create massive performace issues in the future when some adds many large columns to the table. Always specify exactly what you are after. 
    SELECT RowID, Question,Answer
  FROM Grade_Theory

    [download]
    Now comes the tricky part finding the matching answer. Now we do not know exactly how many words are relivent so we need to try and find a best match. Perl can help by generating the where clause. 
    my @words = split /\W+/, 'The funcdamental particle is called a quark,
+ Bob!';
my @parts = ();
for (@words) {
  push @parts, "Answer like '%$_%'";
}
my $where_clause = join (" or\n",@parts);

my $sql = "SELECT RowID, Question,Answer
             FROM Grade_Theory
            WHERE $where_clause";

print "$sql\n";

    [download]
    This will result in the following type of query. 
    SELECT RowID, Question,Answer
             FROM Grade_Theory
            WHERE Answer like '%The%' or
Answer like '%funcdamental%' or
Answer like '%particle%' or
Answer like '%is%' or
Answer like '%called%' or
Answer like '%quark%' or
Answer like '%Bob%'

    [download]
    You can improved this by removing short/irrelvent words from the word list. 
    my @words = split /\W+/, 'The funcdamental particle is called a quark,
+ Bob!';
my @long_words=();
for (@words) {
  push @long_words, $1 if (/^(\w{3,})$/);
}
my @parts = ();
for (@long_words) {
  push @parts, "Answer like '%$_%'";
}
my $where_clause = join (" or\n",@parts);

my $sql = "SELECT RowID, Question,Answer
             FROM Grade_Theory
            WHERE $where_clause";

print "$sql\n";

    [download]
    This will result in the following resulting query. 
    SELECT RowID, Question,Answer
             FROM Grade_Theory
            WHERE Answer like '%The%' or
Answer like '%funcdamental%' or
Answer like '%particle%' or
Answer like '%called%' or
Answer like '%quark%' or
Answer like '%Bob%'

    [download]
    If you want to go any further you really need a completely different design of database. If you have a table that contains words and a many to many relationship between that and your question table you could then do a far more interesting query. But this is PERL monks so I will lay of the database theory.

    Hope it helps
    UnderMine

[reply]
[d/l]
[select]
Re: Wildcards, SQL and Perl
by pbeckingham (Parson) on Mar 13, 2004 at 19:00 UTC

    Please post your code. Your Perl code, that is. Until then, I'd hazard a guess that the SQL LIKE operator is not what you want, as it uses a pattern to search for in a column value, whereas your column value is the pattern to be searched for in the answer string.

    I think you need to look for a pattern in the answer string using Perl.

[reply]
[d/l]
Re: Wildcards, SQL and Perl
by Anonymous Monk on Mar 13, 2004 at 20:20 UTC

    This is a poor approach to testing users' answers. What if I get "What animal is considered man's best friend?" and I don't know the answer. All I have to do is provide "cat parrot dog wolf lion fish hamster" as my answer and I'll get it correct. Regular expressions probably aren't the way to go here. Unless this isn't a very important quiz at all and you don't care about such trivial things :)

[reply]
Re: Wildcards, SQL and Perl
by argggh (Monk) on Mar 13, 2004 at 20:39 UTC
    If I were to do exactly what you're trying to do, only using postgres, I'd do something like this: 
    $ create table foo (bar varchar);
$ insert into foo(bar) values('quark');
$ select * from foo where 'something about quarks and stuff' like '%'|
+|bar||'%';
  bar  
-------
 quark
(1 row)

    [download]
    Which seems to work, in a way.
[reply]
[d/l]
Re: Wildcards, SQL and Perl
by esskar (Deacon) on Mar 13, 2004 at 19:08 UTC
    what about just getting the answer and then test with a regexp (or plain index) if the answer of the database is included in the answer of the user 
    SELECT * FROM Grade_Theory WHERE *Question* = 'The funcdamental partic
+le is called a quark, Bob!'
# now out the answer in the var $answer
if($user_answer =~ !$answer!)
{
 # okay
}

    [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom