Custom REGEXP Function to be used in a SQLITE SELECT Statement

Question

I have an SQLITE Database's File which in one of the table columns there is some simple Regular Expressions.

These Expressions are something like /foo(.?) or /foo/bar/(.?) and so on...

Well, when we try to match some text against a Regular Pattern, in PHP, we do:

preg_match( $pattern, $target, $matches )

Replacing the variables with the content, obviously.

What I would like to do is send ANY STRING as value of a WHERE Clause and, when searching the SQLITE Database's File, use each of the stored Regular Expressions to match a pattern in the given string.

I think that using PHP's sqlite_create_function() I can create some kind of routine to do this, but I don't know exactly how, since is the first time I develop using SQLITE.

If interest, it's part of an MVC Routing of a Framework I'm developing.

Thank you very much, in advance.

Tomas · Accepted Answer · 2012-01-27 11:58:05Z

3

You can use SQLiteDatabase::createFunction documentation here or PDO::sqliteCreateFunction documentation 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);
?>

Use:

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

answered Jan 27, 2012 at 11:58

Tomas

3331 silver badge16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tricasse Over a year ago

For multi-byte strings, you can use:

$db->sqliteCreateFunction('regexp', function($pattern, $value) {     mb_regex_encoding('UTF-8');     return (false !== mb_ereg($pattern, $value)) ? 1 : 0; });

Ish · Accepted Answer · 2011-01-11 23:01:03Z

1

Is this what you are looking for?

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

answered Jan 11, 2011 at 23:01

Ish

29.8k11 gold badges62 silver badges78 bronze badges

1 Comment

Bruno Augusto Over a year ago

It would be if I was trying to pass the Regular Expression. I would like to work in the other way, sending the full URL request and one of the Regular Expressions in SQL Database match with it. Almost like a reverse of preg_match()'s params. If possible I can avoid some unnecessary loops.

Collectives™ on Stack Overflow

Custom REGEXP Function to be used in a SQLITE SELECT Statement

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related