regexp in like expressions

[DenesL]

It seems that several DB backends support having a regular expression
pattern besides the simple % placeholder; I tested MSSQL 2008 R2 based
on
http://msdn.microsoft.com/en-us/library/ms179859.aspx
and it works.

It is possible to have similar capabilities in SQLite with the
advantage that it is applicable to any field, not only text, but it
requires a patch since SQLite uses a different keyword, REGEXP instead
of LIKE (among other things).

One way to patch it in would be to change Expression's like function
in dal.py from

def like(self, value):
return Query(self.db, self.db._adapter.LIKE, self, value)

to

def like(self, value, regexp=False):
if regexp and hasattr(self.db._adapter,'REGEXP'):
return Query(self.db, self.db._adapter.REGEXP, self, value)
return Query(self.db, self.db._adapter.LIKE, self, value)

and add the following to class SQLiteAdapter

def
REGEXP(self,first,second):
return '(%s REGEXP %s)' %
(self.expand(first),self.expand(second,'string'))

@staticmethod
def
regexp_search(expr,item):

reg=re.compile(expr)
return reg.search(str(item)) is not
None

and add this to its __init__

self.connection.create_function("REGEXP", 2,
SQLiteAdapter.regexp_search)

In this case the regexp is Python's so I can for example select all
records that have an integer field starting with 92, 93, 94 or 95 by
doing

rows=db(db.nums.num.like('^9[2-5]+',True)).select()

which uses the SQL command
SELECT nums.id, nums.num FROM nums WHERE (nums.num REGEXP
'^9[2-5]+');

Here in case this interests anybody.

[Massimo Di Pierro]

This should be added. I will do today. Do we know if it works with
sqlite?

On Feb 20, 9:26 pm, DenesL <denes1...@yahoo.ca> wrote:
> It seems that several DB backends support having a regular expression
> pattern besides the simple % placeholder; I tested MSSQL 2008 R2 based

> onhttp://msdn.microsoft.com/en-us/library/ms179859.aspx

[DenesL]

On Feb 21, 9:23 am, Massimo Di Pierro <massimo.dipie...@gmail.com>
wrote:

> This should be added. I will do today. Do we know if it works with
> sqlite?

Yes, I made those changes in 1.91.6 and the example works as shown.
The patch applies to 1.92.1 as well.

[Omri Har-Shemesh]

Hi,

I have been looking for this option in the DAL and came across this discussion,

however it seems that this is no longer defined in the dal.py I have (version 1.99.2).

Has this option been dropped again? Is it possible to achieve this behavior in a different

way? I looked through the book but didn't find any mention of this in the DAL chapter.

Cheers,

Omri