search result record which has a word exactly the same as the keyWord

[dotJoon]

Data in myTable
(ID) message
(1) I am a boy 
(2) You're a girl
(3) He is ambitious 
(4) They are happy 
(5) who are you

I have above data in myTable, and I have the following code.

 SQL code
select ID, message
from myTable
where message like '%#keyWord#%'

If keyword is "am", it will produce the following result.

result
(1) I am a boy
(3) He is ambitious

I like to produce the following target result.

target result
(1) I am a boy

Thanks in advance

[itsyM]

Try:

SELECT ID, message
FROM myTable
WHERE message LIKE '% am %'

Note the space before and after the 'am'.

[r937]

itsym, that will find the word only if it has spaces before and after

it will not find these:

(6) Am I really?
(7) That's who I am

and if you then say --

where message like '% am %'
   or message like 'am %'
   or message like '% am'

then you will not find

(9) That's who I am!

this problem is best solved with a regular expression

[kleineme]

Hi,

regular expressions with MySQL:

http://dev.mysql.com/doc/mysql/en/Regexp.html

You may use this one:

select ID, message
from myTable
where message rlike '[[:<:]]keyword[[:>:]]'

[[:]]
[[:>:]]
These markers stand for word boundaries. They match the beginning and end of words, respectively. A word is a sequence of word characters that is not preceded by or followed by word characters. A word character is an alphanumeric character in the alnum class or an underscore (_).

[r937]

tatsächlich

thanks, kleineme

good answer