1. What exactly is in the DOC column? Binary data of some disgusting, inferior proprietary word
processor document format, plain text, XML, LaTeX files?
2. If you answered "But I like Clippy!" to the above, go to jail! Go directly to jail! Do not pass go,
do not collect any cash! This goes for Word, WordPerfect, OpenOffice/StarOffice's default saves
(please read on before flaming me!) etc. To continue, be a true geek and switch to something that claims
to emphasise substance over structure, like LaTeX or DocBook XML.
3. If you've gotten to here, it's just a matter of conjugating your query:
SELECT userid, name, lname FROM funky_table WHERE doc LIKE '%search_string%';
SQL uses the percentage character (%) as a wildcard to match 0 or more of any character. Use it to
fill in the space in and around your search string. The LIKE predicate above tells MySQL to ignore case
when doing comparisons.
If you want to use a word processor document format, look at decompressing OpenOffice files before
inserting them, and you get XML to play with.
Additional things that may be of use to you:
1. TEXT fields and BLOB fields are basically identical, except that comparisons done with regard to TEXT
fields are case-insensitive. The manual calls them "case-insensitive BLOBs".
2. The above works on all MySQL table types but can be slow as all buggery. Populate that WHERE clause with
everything you can to reduce the rows examined. Also, avoid strings that are less than 3 characters long, otherwise
MySQL 4.0.x and above won't use one of the faster search techniques that the optimiser has at it's disposal.
3. If you can work without transactions (many applications can) or you are happy to have at least the document field
in a MyISAM table, look into FULLTEXT searches. Of course, this only really works nicely for plain text, and
has a new set of caveats to deal with. MySQL 4.1.x brings rather nice enhancements to this part of MySQL, but
don't bug poor Heikki about adding FULLTEXT to InnoDB as some of us are of the opinion that no one
at Innobase Oy ever sleeps or goes home (or MySQL AB for that matter).
Regards,
Chris
Ugo Bellavance wrote:
-----Message d'origine----- De : Andy Fletcher [mailto:[EMAIL PROTECTED] Envoyé : Tuesday, January 27, 2004 4:42 AM À : [EMAIL PROTECTED] Objet : Hi all.
I am just starting to work with SQL and Mysql server language and am embedding some queries into another program I am developing.
The database I have created needs to store Documents and I have used Blob type columns for this. If I want to:
Select USERID,NAME,LNAME Where DOC contains "what ever words or phrases" ;
How would I do this ?
First, try to use a significant subjet to your messages. It helps when searching the archive.
Then, I don't think your needs can be fullfilled, since I don't think mysql can tell what is in a Blob, except that it is a series of 1 and 0.
hth
Many thanks for information on this.
Here from you soon I hope, Best regards Andy Fletcher
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
