0

I would like to know how can I ignore the accent in multiple search with eloquent from Laravel.

Currently I have this :

// $searchTerms => 'Héllo human';
$explodedTerms = explode(" ", $searchTerms); 

...

// $explodedTerms => ['Héllo', 'human'];
$query->where(function ($q) use ($explodedTerms) {
    foreach ($explodedTerms as $value) {
        $q->whereRaw('LOWER(`text`) LIKE ?', ['%'.strtolower($value).'%' ]);
        }
    });
...

I would like to have the search terms 'hello human' to match the field text.

I tried with ILIKE but I have an error :

$q->whereRaw('LOWER(`text`) ILIKE ?', ['%'.strtolower($value).'%' ]);

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 'ILIKE

Jérémie Chazelle
  • 1,721
  • 4
  • 32
  • 70
  • Perhaps this? https://stackoverflow.com/a/4126152/5159168 – Andreas May 22 '19 at 09:13
  • Thx @Andreas for your answer. I tried with this solution, but I have the result "h'ello"... no match with the field 'text'. – Jérémie Chazelle May 22 '19 at 09:26
  • `ILIKE`? You like? – Paul Spiegel May 22 '19 at 09:57
  • @PaulSpiegel https://stackoverflow.com/a/21216559/5453732, but with whereRaw doesn't work.. I think that it's logic that doesn't work with whereRaw... – Jérémie Chazelle May 22 '19 at 10:01
  • @JérémieChazelle - I've never heard of `ILIKE` in MySQL - And it doesn't make much sense. The default COLLATION in MySQL configuration and laravel settings is case insensitive (`eg. utf8mbr4_unicode_ci`). With a CI collation any `LIKE` and any `=` search is always case insensitive. `SELECT 'E' = 'e'` will return TRUE by default. Also `SELECT 'E' = 'é'`. So you usually don't need to do anything. If the simple LIKE doesn't work, post your column collation. – Paul Spiegel May 22 '19 at 10:30

1 Answers1

0

this is the answer $str = iconv('utf-8','ASCII//IGNORE',$str);

Mustafa Goda
  • 109
  • 6