Laravel: How to ignore database field's special characters when doing a 'LIKE' query?

Viewed 427

Would like to ask for your help as I'm having challenges creating a Laravel query.

Here's my current code:

$products = Product::where(function($query) use ($searchTerm) {
   $query->where('product_name', 'LIKE', "%$searchTerm%");
   $query->orWhere('product_category', 'LIKE', "%$searchTerm%");
   $query->orWhere('product_description', 'LIKE', "%$searchTerm%");
   })
   ->where('lang', '=', 'en')
   ->orderBy('product_name', 'ASC')
   ->get();

If I have a product named Michael's Handsoap and the user searches using the keyword "Michaels", it doesn't return anything with the query above. Is there a way to remove special characters from database fields as you do the query? Any input would be appreciated.

Thanks!

EDIT: Figured that it's best to just add a product_name_search column that contains the product name stripped of special characters (using for example, preg_replace("/[^A-Za-z0-9 ]/", '', $productName))

This way, $query->where('product_name_search', 'LIKE', "%$searchTerm%") can now be used. It adds another parameter, but at least you've got them all covered now.

0 Answers
Related