SQLite3 and applescript comparing strings

Scripting Forums AppleScript | Mac OS X
#1

This is mainly a SQLite3 question but I’m hoping somebody may have a quick answer for me because I couldn’t google one…

I have a SQLite3 database created using the do shell script command. I can read the data and do all of the usual stuff, but I have one question. Right now I use applescript to do all of my string comparisons but I’m wondering if anyone knows how to let SQLite to the comparing.

For example suppose I have a database like so:
theBook theLength
this book title 10
that book title 20

If I want to find all of the books that have the word “book” in it, right now I read the entire table into applescript and issue the applescript command…

if “book” is in “this book title” then do something

But it occurs to me that SQLite3 should be able to do that for me so I don’t have to bring the entire table into applescript first. It would be nice if what came back to applescript was just the records I wanted. Does anyone know how to do simple string comparisons in SQLite3? The SQLite command would look something like the following but I don’t know what the equivalent “IS IN” command would be.

select theBook from myTable where theBook = ‘book’ <----- how do I change = into is in???

#2

Check this tutorial out.

Regards,

Craig

#3

Thanks Craig, but that link doesn’t go anywhere. I get a “bad request” returned.

I think I know which one you mean, the unScripted article by Adam Bell titled “A Tutorial Introduction to SQLite3 - a Simple Database Engine”. I found a cached version on google. But if that’s the one you mean then that does the same as every other tutorial I’ve seen… they only show the syntax for something equaling something… not partial searches like we do in applescript using “is in” or “contains”.

Let me know if you meant something else or not.

#4

I see what you mean. I can’t find what you are looking for anywhere.

If I run across something in the future I will let you know.

Cheers,

Craig

#5

Thank you Jacques !!!

I knew it was simple but I certainly searched my butt off trying to find it. I figured it would be a wild card and I tried * as the wildcard but it’s %. Great. I was all over the sqlite3 site and many others… but not the sql site. DOH!

#6

That’s not syntactically correct, tho it works in this instance. Values (as opposed to column names) should use single quotes. So the correct syntax is:

WHERE theBook LIKE ‘%book%’

Tom
BareFeet

–
Comparison of SQLite GUI apps for Mac OS X:
http://www.tandb.com.au/sqlite/compare/