Laravel: SQLite REGEXP

laravel:sqlite_regexp.php

// create the function
DB::connection()->pdo->sqliteCreateFunction("REGEXP", "preg_match", 2);

// build a query
DB::table('tablename')->raw_where('REGEXP("#^[a-z]+$#iu", tablename.row)')->get();

Thanks for this great line!

I don't know for older versions, but as for L5, you should go this way:

// create the function
DB::connection()->getPdo()->sqliteCreateFunction("REGEXP", "preg_match", 2);

since DB wouldn't allow you to access 'pdo' as it's a private property now.

see http://laravel.com/docs/4.2/database#accessing-connections

Thanks!

If you're using both SQLITE and MySQL

DB::connection()->getPdo()->sqliteCreateFunction('REGEXP', function ($pattern, $value) {
                mb_regex_encoding('UTF-8');
                return (false !== mb_ereg("/$pattern/", $value)) ? 1 : 0;
            });

is a good alternative;

Thanks all for this thread 👏

I had to remove the delimiter to make this work. I use this code in my AppServiceProvider.

if (DB::Connection() instanceof \Illuminate\Database\SQLiteConnection) {
    DB::connection()->getPdo()->sqliteCreateFunction('REGEXP', function ($pattern, $value) {
        mb_regex_encoding('UTF-8');
        return (false !== mb_ereg($pattern, $value)) ? 1 : 0;
    });
}

Thanks! Works perfectly!