Custom REGEXP function to be used in SQLITE SELECT Statement

Question

I have a SQLITE database file which in one of the columns of the table contains some simple regular expressions.

These expressions are something like / foo (.?) Or / foo / bar / (.?) Etc ...

Well, when we try to match some text with a regular template, in PHP we do:

preg_match ($ pattern, $ target, $ matches)

Replacing variables with content is obvious.

I would like to send ANY STRING as the value of the WHERE clause and when searching for the SQLITE database file, use each of the saved regular expressions to match the pattern on this line.

I think that with PHP sqlite_create_function () I can create some kind of routine for this, but I don’t know exactly how this happens since I am developing SQLite for the first time.

If interest is part of the MVC Routing Framework I'm developing.

Thanks in advance.

Answer 1

You can use the SQLiteDatabase::createFunctiondocumentation here or the PDO::sqliteCreateFunctiondocumentation here.

I did something like this:

<?php
function _sqliteRegexp($string, $pattern) {
    if(preg_match('/^'.$pattern.'$/i', $string)) {
        return true;
    }
    return false;
}
$PDO->sqliteCreateFunction('regexp', '_sqliteRegexp', 2);
?>

Using:

SELECT route FROM routes WHERE pattern REGEXP 'your/url/string' LIMIT 1

Answer 2

Is this what you are looking for?

e.g. SELECT * FROM `my_table` WHERE `my_column` REGEXP "\/foo(.?)"