|MySQL text search|
Can I get surrounding words?
Say I have a text field, filled with a few hundred words.
On a search (SELECT * FROM tbl WHERE fld LIKE '%word%'), is it possible to display only a few lines of the result, specifically those 'surrounding' the searched word?
... this is the text surrounding word but I don't want it to go on and on ...
I know I can do this with php string parsing, but I'm curious to see if anyone knows how to do this in the query itself.
if in the select you grab the whole thing then you use strpos, find out where the word is and do substr at -10 and +10 then cut out partial words
something like that should work
this is for php, but you can do similar with mysql, too:
Returns the position of the first occurrence of substring substr in string str. This is the same as the two-argument form of LOCATE(), except that the order of the arguments is reversed.
SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len)
The forms without a len argument return a substring from string str starting at position pos. The forms with a len argument return a substring len characters long from string str, starting at position pos. The forms that use FROM are standard SQL syntax. Beginning with MySQL 4.1.0, it is possible to use a negative value for pos. In this case, the beginning of the substring is pos characters from the end of the string, rather than the beginning. A negative value may be used for pos in any of the forms of this function.
MySQL Manual string functions [dev.mysql.com]
with these and other functions you should be able to implement it directly into your query and reduce the amount of data returned from your database server.
Thanks for the suggestions, I didn't realize had all those string functions.