OT: search text in mySQL

lucianoes

This is a question about mySQL, not PHP. I am asking it here because I like this forum, I always hang around here. Please bear with me. My question is:
How do I search for words in a mySQL table? More especifically, how do I search for whole words in a mySQL table? 

If I use:
select * from table where column = 'string'
mySQL will look for columns that contain 'string' and only 'string'.

If I use:
select * from table where (column like "%string%")
mySQL will look for columns that contain 'string' and possibly something else. That does not necessarily mean that 'string' is a whole word.

I know I could submit the resulting rows to a lot of PHP stunts so that the script displays nothing but the rows where 'string' is indeed a whole word, but in this particular script I would like to avoid that. It doesn't make sense writing additional code for something that mySQL probably can do on its own. I've been reading mySQL documentation for over an hour looking for that but can't seem to find it.
So, if someone can tell me right away what syntax I should use to search for whole words in a database, that would be super.

Thank you very much,
    Luciano ES
    Santos, SP - Brasil

[deleted]

MYSQL supports 'FULLTEXT' indexes, which currently only match on whole words and then only if they are longer than 2 or 3 characters.

That might do what you want.

Otherwise there is always the excellent (if slightly inefficient) article about 'slapping together a searchengine, on this very site.

And if that doesn't do what you want you can always start messing with regular expressions, but that is dead-slow in mysql (and rightly so)

A forum, a FAQ, what else do you need?

Time to YAPF!: http://www.hvt-automation.nl/yapf/

Anon

Hi!

MySQL supports regular expression matching. The easiest regular expression would be \bstring\b, which means that the word should be delimited by word-boundary characters (for instance, beginning of the whole field's value, a whitespace character, etc). I am not quite sure which regexp MySQL uses - regular expressions or POSIX (extended) regular expressions. \b seems to work in the latter only. Anyway, check the docs, it should work. I am a bit concerned about speed though, a complex regular expression could make database queries a bit slow, so Vincent's approach could be more right. However, if you do not need all the bells and whistles of full-text indexing, I guess regular expressions are the way to go.

Best regards,
Stas

lucianoes

Thank you both. I read that part in the manual about FULLTEXT indexes. Turns out all my tables were in BLOB format, but what the hell, I turned them all into TEXT.

That's not good enough yet.

1 - FULLTEXT indexes let me search whole words, but whole words only. No partial matches. "%string%", for example, is not allowed. I would have to furnish my form with a separate field just for that.

2 - The current version of mySQL does not let me use boolean operators with MATCH, it's scheduled for version 4 only. That will dertainly fulfill all my needs, when it's available.

Vincent, I've kept that article in my disk for weeks, I always postpone reading it. But now, if I were to use PHP to parse the results and make up for mySQL's shortcomings, what I've learned so far would be enough to make my own search engine myself, I don't have to read the article. But like I said, it's a question about mySQL, not PHP. I thought that maybe mySQL can do what I want but I don't know how. I was afraid of adding an unnecessary stage to the project.

Thank you guys.
Luciano ES
Santos, SP - Brasil

[deleted]

I'm curious: how would you build your own search engine?

lucianoes

Oh, I think I know what you're thinking. That's not it. What I am doing now is supposed to work locally only, in my disk. I'll add a search engine to my Web site later, but it will only work within my site.

I might make an actual Web search engine someday, but not now.

    Luciano ES
    Santos, SP - Brasil

[deleted]

When you said "I'll build my own searchengine" I was just wondering how you'd go about it if you did not read the article yet 🙂 that's all.

Ofcourse you won't create a web-search for the entire internet, who has the bandwidtch and CPU power to do that? (and who'll bother now that we have google?)

lucianoes

Ah, but if you have PHP, Google becomes a mere starting point.

Until a year ago, I could hardly imagine. PHP has changed my life.

Luciano ES
Santos, SP - Brasil

lucianoes

Hi, Stanislav.

Just so you know, here is an excerpt from the manual:

Description of MySQL regular expression syntax

A regular expression (regex) is a powerful way of specifying a complex search.

MySQL uses Henry Spencer's implementation of regular expressions, which is aimed at conformance with POSIX 1003.2. MySQL uses the extended version.

    Luciano ES
    Santos, SP - Brasil

[deleted]

The thing with regex (and like also) is that they will not use indexes at all.

So if you have 200MB of content, all 200MB will be searched.

Fulltext uses indexing which is slightly more intelligent, and the 'slapping together a searchengine' is even better.