RegExp in SqLite using Php

19.Oct.2012  |  Posted by admin  |  Filed under Tutorials

One of the important thing that i kept messing as i chnaged 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 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 ony 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:

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:


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.

Related Postssqlite regex regular expression php pdo createFunction sqliteCreateFunction database


Written by: Andy on 09.May.2013

I´m glad to have stumbled onto this.  However I think that the actual call to regExp should use regexp and not preg_match.

Written by: admin on 09.May.2013

oh yah
I fixed it, thx :)

Write a Comment



We are a team, that resides in Germany (please, do forgive our bad English) and we have developed an international based and uncomplicated software for users, who do not know (or do not like to know) much about all this programs like HTML, PHP, SqLite, JS, Browser differences etc.. Users who like  to ....Read More
Havalite CMS in english language Havalite CMS in deutscher Sprache هافالايت العربية لنشر المقالات