SQL query for words (not the sentence)

I would like to query a single column (varchar):

sample datarows:

1) The fox jumps like a foo on my bar
2) Jumpers are not cool
3) Apple introduced iJump

When I enter a search crite开发者_如何转开发ria like... jump

I expect to get a resultset of: jumps, Jumpers, iJump (So I dont want the complete row)

Currently I'm using MySQL (I'm open to suggestions as long it's open source)

Since you're using MySQL, I might suggest looking into LIB_MYSQLUDF_PREG.

This open source library will provide you with additional regex functionality, including the PREG_CAPTURE function, which extracts a regex match from a string.

Using this function, you could easily build a regex to return the match you're looking for... Something like:

\b\w*jump\w*\b

Getting any row with your search criteria is easy:

SELECT sentence
FROM sentences
WHERE sentence LIKE '%jump%'

I'd probably do the rest in application logic, since doing it in the database doesn't help you at all.

Also, any method of splitting a string and handling it will probably be database-specific, so you would need to say which one you're using.