% LIKE% retrieves, for example. FEMALE DATA on calling MEN

Question

$itemList = DB::table('items') ->orderBy('id', 'desc') ->where('item_status', 2) ->where(function($query) use($queryArr) { foreach($queryArr as $uID){ $query->whereRaw("tags LIKE '%$uID%'"); } })->paginate(21); 

I have encountered this problem since ancient times. The problem when doing a LIKE search is that it captures WOMAN data when it is simple: MEN

Mainly because MEN is inside women

I also tried the following but failed (this type of word capture) men without women data

 $query->where('tags', 'LIKE', '%'.$uID.'%'); SELECT 'a word a' REGEXP '[[:<:]]word[[:>:]]'; 

How to use this query on Word boundary in laravel query builder

Tried this and still failed $ query-> whereRaw ("tags LIKE REGEXP" [[: <:]] Men [[:>:]] '");

 SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'REGEXP '[[:<:]]Men[[:>:]]')' at line 1 (SQL: select count(*) as aggregate from items where item_status = ? and (tags LIKE REGEXP '[[:<:]]Men[[:>:]]')) (Bindings: array ( 0 => 2, )) 

I also understand that some ask why I didn’t just create the right way to handle this category of positions. Well, I think that now using full-text search is no more suitable for me, when it comes to scaling, I will use voltage search. true?

UPDATE

Sorry for not giving an example tag.

bag, shoes, Men , wedges

bag, shoes, men

Men shoes bag

Answer 1

If the values ​​are separated by commas, try using the following

 WHERE `tags` REGEXP "(^|,)men(,|$)" 

This will require a comma or end of line around the word men.

Answer 2

Your REGEXP solution throws a syntax error due to the optional LIKE keyword. The correct syntax is simply WHERE column REGEXP 'pattern' .

If you have difficulty remembering this, try using RLIKE instead, which is a synonym for MySQL REGEXP. There is less chance that you accidentally write WHERE column LIKE RLIKE ... in your query because it is more explicitly redundant.

The sample itself should work fine. Personally, I don't like the use of these word classes in MySQL; since you know that your tags are separated by a comma and a space, [ ,]Man[ ,] will function in exactly the same way. The boundary word class is broken into punctuation marks other than underscores, so you may run into problems if you have tags that are hyphenated, for example.

If you want to use multiple word tags with spaces, any of the previous patterns is a mistake. In this case, I would try to use commas as a separator, get rid of spaces and use anchors instead, as suggested in one of the other answers: (^|,)Man($|,)

Answer 3

In your query, you said: find something containing the tag 'MEN' => '%MEN%' , so it shows: WOMEN AND MEN, so to answer your question, you should use start from instead of contains, for example:

 $query->where('tags', 'LIKE', $uID.'%');