Nathan Nathan - 1 year ago 53
SQL Question

MySQL: Search keyword in a comment string

I've looked through several similar topics on stackoverflow that is similar my question but I did not find anything that can help me yet. I have this SQL query:

SELECT * FROM twitter_result
WHERE LOWER(TweetComment) LIKE LOWER('%lebron james%')

I want to search a TweetComment that contains the word "LeBron James" and "NBA" at a same time. But these two words need to stand alone by themselves. Like it should not return a tweet that contains #LeBron James and #NBA (or NBATalk)

For instance, it should return a tweet like this

LeBron James Donates $41 Million To Send 1,100 Kids To College, Becomes 6th Most Charitable Athlete NBA In World

where Lebron James and NBA stand alone (no
characters). I have the
there to ignore the case sensitive. Any help is greatly appreciated. Thanks

Sorry I forgot to add, I am just using SQL in PHPMyAdmin

vkp vkp
Answer Source

If you plan to use a regexp use,

select * from twitter_result
where --ignore tweets that contain #lebron james and #nba
      TweetComment not regexp '.*#lebron james.*|.*#nba.*' 
      --select only those tweets that contain lebron james AND nba
and   TweetComment regexp '[[:<:]]lebron james[[:>:]]'
and   TweetComment regexp '[[:<:]]nba[[:>:]]'

All the patterns being searched for, have to be stated explicitly as MySQL by default doesn't support lookarounds.

The above match is case insensitive by default. Use regexp binary if the search needs to be case sensitive. Add more search words as needed.