RegExp in SqLite using Php

Teryaki

One of the important thing that i kept messing as i switched from MySql to SqLite is the direct usage of regular expressions in sql statments. After searching many times in the internet for a solution, i only found out that SqLite doesn’t support RegExp by default except by changing the functionality of the database engine which depends on your running system.

The REGEXP operator is a special syntax for the regexp() user function. No regexp() user function is defined by default and so use of the REGEXP operator will normally result in an error message.

At stackoverflow.com i found a discussion about this issue that didn’t help me much except this part here

One basic requirement to invoke the function REGEXP in sqlite is
“You should create your own function in the application and then provide the callback link to the sqlite driver”.
For that you have to use sqlite_create_function (C interface). You can find the detail from here and here

I almost gave up und thought that the only possible way is to run all rows from myTable and check them one by one by calling the preg_match() function in php. But such a proccess will take to much time to complete. In this moment i was thinking how would php resolve this problem?

According to php documents we can create or use existing function by using the SQLite3::createFunction. Here we have a simple example:

<?php
function my_udf_md5($string) {
    return md5($string);
}

$db = new SQLite3('mysqlitedb.db');
$db->createFunction('my_udf_md5', 'my_udf_md5');

var_dump($db->querySingle('SELECT my_udf_md5("test")'));

// result:
// string(32) "098f6bcd4621d373cade4e832627b4f6" 
?>

In PDO its almost the same when using PDO::sqliteCreateFunction which is still experimentell (see warnung) but works fine for our purpose. Here is the same example using PDO:

<?php

function my_udf_md5($string) {
    return md5($string);
}

$db = new PDO('sqlite:mysqlitedb.db');
$db->sqliteCreateFunction('mymd5', 'my_udf_md5', 1);
// for the sqliteCreateFunction there are 3 parameters needed:
// 1: function_name (any name you want)
// 2: callback (the name of our function my_udf_mdf)
// 3: Arguments (In this case our function has 1 argument)

$rows = $db->query('SELECT mymd5("test") FROM files')->fetchAll();

?>

Using preg_match function

You can copy the following sqlite_regExp() function and try it by yourself. It works for me

function sqlite_regExp($sql)
{
    $db = new PDO('sqlite:mysqlitedb.db');
    if($db->sqliteCreateFunction("regexp", "preg_match", 2) === FALSE) exit("Failed creating function!");
    if($res = $db->query($sql)->fetchAll()){ return $res; }
    else return false;
}

// calling our function / sort matches
if($rows = sqlite_regExp("SELECT * FROM myTable WHERE regexp('/sql(ite)*/ui', myRow)")){
    foreach($rows as $row) echo $row[0];
}

That’s it!

I believe that this methode is much effective way than using ready-made commands in a sql statement. It allows us to be more efficient in the way how we search in databases by using our self-made functions.