Searching in the database - exact word

[Stefano Mtangoo]

Now, heading might be a bit misleading. If that is the case forgive me as I cannot think of best heading!

i have database of books, that is e-books. Each table have page number and contents (to simulate true book). The problem is, I have to search for a word in the book and return page number and contents. I know LIKE '%mykeyword%' helps to some extent but it returns much unneeded stuffs. Eg if the keyword is Goa it will bring goalkeeper, goat et al. I want to be able to get pages which contains exactly word Goa and ignore others. Which query am I supposed to use. I have run out of Ideas so, I need your help

[pritaeas]

How about searching with REGEX.

$query = "SELECT * FROM table WHERE column REGEXP '[[:<:]]" . $word . "[[:>:]]'"

[mschroeder]

Assuming your table is a MyISAM and the particular column has a full-text index on it:

SELECT * FROM table WHERE MATCH (col1, col2, col3) AGAINST ('+Goa' IN BOOLEAN MODE);

http://dev.mysql.com/doc/refman/5.1/en/fulltext-boolean.html

***Apparently this does not REQUIRE a fulltext index, but will take a major performance hit

[Stefano Mtangoo]

I didn't said that I use SQLite3, however I will search to see if those solutions are available with SQLite

[pritaeas]

From the sqlite site:
The like() function is used to implement the "Y LIKE X [ESCAPE Z]" expression. If the optional ESCAPE clause is present, then the like() function is invoked with three arguments. Otherwise, it is invoked with two arguments only. Note that the X and Y parameters are reversed in the like() function relative to the infix LIKE operator. The sqlite3_create_function() interface can be used to override the like() function and thereby change the operation of the LIKE operator. When overriding the like() function, it may be important to override both the two and three argument versions of the like() function. Otherwise, different code may be called to implement the LIKE operator depending on whether or not an ESCAPE clause was specified.

I do not know about SQLite, so can't give an example, but perhaps you can find one (looks like a function in C).

[mschroeder]

Not sure what the environment limitations are, but SQLite3 has fulltext searching capabilities, but they are not enabled by default. Very Similar to mysql syntax it appears.

http://www.sqlite.org/fts3.html

[Stefano Mtangoo]

Thanks guys!
theoretically my problem is solved. I will post if I hit a wall.
Again thanks a lot!