SQL query for words (not the sentence)

Question

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 criteria 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)

What happens where there are more than one jump's in the row? — George Johnston
– George Johnston, Commented Oct 13, 2011 at 19:45
I suggest reading up on full text search. Depending on database, you may already have support for it. — Oded
– Oded, Commented Oct 13, 2011 at 19:48
I've never ever heard of a SQL-Statement doing this. I suggest looping the resultset with a regex for filtering. EDIT: Whoops! Google proofs me wrong. There may be support for it depending on your DB-System. But maybe the suggested solution is of some value anyway? — Fildor
– Fildor, Commented Oct 13, 2011 at 19:48
first occurrence returned is good, if more could be returned it's even better — Terence
– Terence, Commented Oct 13, 2011 at 19:49

Michael Fredrickson · Accepted Answer · 2011-10-13 20:10:30Z

2

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

answered Oct 13, 2011 at 20:10

Michael Fredrickson

37.5k6 gold badges95 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Brendan Long · Accepted Answer · 2011-10-13 19:55:49Z

1

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.

edited Oct 13, 2011 at 19:55

answered Oct 13, 2011 at 19:49

Brendan Long

54.5k21 gold badges154 silver badges194 bronze badges

1 Comment

TROODON Over a year ago

And "LIKE '%jump'" to find jumps, Jumpers. And "LIKE 'jump%'" to find iJump And simple "LIKE 'jump'" to find jumps, Jumpers, iJump :3 But i recomend use sphinx or lucene

Collectives™ on Stack Overflow

SQL query for words (not the sentence)

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related