sql complex like expression @user

Question

im trying to find all the mentions for the matched username (sam) from the database text code with following:

$sql = "select * from tweet where feed like '%@sam%'";

this will return all the rows which user have been mention at.

but now the problem is, it also returns rows with @sam3, @sam-dadf or anything that is after @sam..

how can i limit this, so only specific username shows from the text and not all the matching sams.. following are the text format in the database, that is inserted in feed row.

 1. i been out with @sam today, but im
    not sure what we should do
 2. we head great party today and all the frineds were invited, such as @sam, @jon, @dan...
 3. i been out today with @samFan and with @dj. << this row should not get pull from database``

Answer 1

I would recomend using mysql regular expression to try to achive this.

Search the string sfor your required text, disregarding selected followup chars

MySQL Regular Expressions

Answer 2

I'm sorry, but I'm not familiar with MySql.

In Microsoft SQL Server, the Like clause can allow you to specify fields to NOT match.

Ex: if ('I like @Spam.' LIKE '%@Spam[^a-z]%') SELECT 1

Answer 3

select * from table where feed REGEXP '@sam[^A-Za-z0-9]'

Answer 4

Yes, use REGEXP (or RLIKE), but watch out for the common regex mistake of looking for the end of the desired "token" as merely a common, negated character class like [^A-Za-z0-9] -- instead use the zero-width, "end of word" match construct [[:>:]] (which perl's engine and other inspired regexen know as \b).

The negated character class fails to match at the end-of-string:

mysql> SELECT 'I am @sam' REGEXP '@sam[^A-Za-z0-9]' AS "Does This Match?";
+------------------+
| Does This Match? |
+------------------+
|                0 | 
+------------------+
1 row in set (0.00 sec)

where the word boundary match succeeds:

mysql> SELECT 'I am @sam' REGEXP '@sam[[:>:]]' AS "Does This Match?";
+------------------+
| Does This Match? |
+------------------+
|                1 | 
+------------------+
1 row in set (0.00 sec)

If [[:>:]] isn't quite right for your application (because your "username" character set is not what the MySQL regex engine thinks of as one side of a word boundary in your locale), you can, alternatively, specify a negated character class and separately test for end-of-string:

SELECT ... WHERE (feed REGEXP '@sam[^A-Za-z0-9]' or feed REGEXP '@sam$')