Select records from database

[watersniper]

This isn't neopets related, but since you guys are all brilliant programmers, maybe you have some experience and can shed some light?

I'm using enterprise javabeans and have a database which uses EJB-QL (which doesn't seem to recognize REGEXP, otherwise I could use .\\b).
I need to select records containing keywords, eg. "pin". And don't want it to return things "lynchpin" or "pineapple".

Any idea how to do this?

Edit: A clarification on the question --
Essentially my column consists of chunks of text, and I'm looking for the records whose chunk of text contain a keyword.
Normally it would be this way:
SELECT e.column FROM TABLE e WHERE e.column LIKE '%pin%'
However, this will cause records containing text with the terms 'lynchpin' and 'pineapple' to be chosen as well.

So I'm looking for something that will bound the keyword, such as in regex: ".\bpin\b."

Otherwise I will have to select all the records then run the text through a Pattern/Matcher to see if it returns false, which is inefficient.

[j03]

No experience with the environment you are working with but this should still be of use:

[Only registered and activated users can see links]

Check the solution given to that question.

watersniper

[watersniper]

Sorry Infamous Joe I don't really get how the example you suggested relates.

Perhaps I should clarify my question.

[Zachafer]

Never used that DB, but this could/should work?

SELECT e.column FROM TABLE e WHERE e.column LIKE ' pin'
UNION
SELECT e.column FROM TABLE e WHERE e.column LIKE 'pin '

Better yet, does it support in? ie:
SELECT e.column FROM TABLE e WHERE e.column IN (' pin', 'pin ', "pin\n", "\npin") //etc

[watersniper]

Thanks, Zachafer, but EJBQL doesn't support that either. Your first solution would ignore any text such as these: ' pin,' , ' pin!', '(pin ', ' pin)' etc which are all valid syntactically in my chunks of text. In frustration I moved my database to mysql so I could use REGEXP.

In case anyone ever stumbles upon this thread with the same problem, one workaround is to enter all the allowed values in another table, then you could write
SELECT DISTINCT e.column FROM FirstTable e, ValueTable v WHERE e.column LIKE v.value

Sucks if your keyword constantly changes though.