Implementing Regular Expression Support...?

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Implementing Regular Expression Support...?

lists@mgreg.com
Hi All,

I'm currently using SQLITE in a few production apps.  I'm using various languages such as Ruby, PERL, RB, etc.  I have need to use regular expressions in some of my queries, but I'm not sure how to implement "user defined functionality".  Where are the hooks?  Is there a particular mechanism/language I must use to create them?  Is this something I'm required to recompile SQLITE for?

Best,
Michael
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Implementing Regular Expression Support...?

J. King-3
On Tue, 08 Dec 2009 12:11:13 -0500, [hidden email] <[hidden email]>  
wrote:

> Hi All,
>
> I'm currently using SQLITE in a few production apps.  I'm using various  
> languages such as Ruby, PERL, RB, etc.  I have need to use regular  
> expressions in some of my queries, but I'm not sure how to implement  
> "user defined functionality".  Where are the hooks?  Is there a  
> particular mechanism/language I must use to create them?  Is this  
> something I'm required to recompile SQLITE for?

You do so by defining a user function called 'regexp'.  The means by which  
one defines a user function depends on the language.  See, for instance,  
[1] for Ruby.  For a 'regexp' function you would specify two arguments,  
pattern and string to match against.

[1]  
<http://sqlite-ruby.rubyforge.org/sqlite3/classes/SQLite3/Database.html#M000115>

--
J. King
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Implementing Regular Expression Support...?

lists@mgreg.com
On Dec 8, 2009, at 12:24 PM, J. King wrote:

> On Tue, 08 Dec 2009 12:11:13 -0500, [hidden email] <[hidden email]>  
> wrote:
>
>> Hi All,
>>
>> I'm currently using SQLITE in a few production apps.  I'm using various  
>> languages such as Ruby, PERL, RB, etc.  I have need to use regular  
>> expressions in some of my queries, but I'm not sure how to implement  
>> "user defined functionality".  Where are the hooks?  Is there a  
>> particular mechanism/language I must use to create them?  Is this  
>> something I'm required to recompile SQLITE for?
>
> You do so by defining a user function called 'regexp'.  The means by which  
> one defines a user function depends on the language.  See, for instance,  
> [1] for Ruby.  For a 'regexp' function you would specify two arguments,  
> pattern and string to match against.
>
> [1]  
> <http://sqlite-ruby.rubyforge.org/sqlite3/classes/SQLite3/Database.html#M000115>
>
> --
> J. King


What if you're making calls to SQLITE directly, and not via the language's API?  Is there any way to make the support "native"?  Can I drop a script of some sort that SQLITE will call (no matter what language I'm using to perform the call)?  I guess I'm looking for a more abstract understanding of the hows and whys first.

Thanks,
Michael
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Implementing Regular Expression Support...?

Dami Laurent (PJ)
In reply to this post by J. King-3
 

>-----Message d'origine-----
>De : [hidden email]
>[mailto:[hidden email]] De la part de J. King
>Envoyé : mardi, 8. décembre 2009 18:24
>À : General Discussion of SQLite Database
>Objet : Re: [sqlite] Implementing Regular Expression Support...?
>
>On Tue, 08 Dec 2009 12:11:13 -0500, [hidden email] <[hidden email]>  
>wrote:
>
>> Hi All,
>>
>> I'm currently using SQLITE in a few production apps.  I'm
>using various  
>> languages such as Ruby, PERL, RB, etc.  I have need to use regular  
>> expressions in some of my queries, but I'm not sure how to
>implement  
>> "user defined functionality".  Where are the hooks?  Is there a  
>> particular mechanism/language I must use to create them?  Is this  
>> something I'm required to recompile SQLITE for?
>
>You do so by defining a user function called 'regexp'.  The
>means by which  
>one defines a user function depends on the language.  See, for
>instance,  
>[1] for Ruby.  For a 'regexp' function you would specify two
>arguments,  
>pattern and string to match against.
>
>[1]  
><http://sqlite-ruby.rubyforge.org/sqlite3/classes/SQLite3/Datab
>ase.html#M000115>
>
>--
>J. King

Hi,

If you use Perl, make sure to get the latest version of DBD::SQLite, where the SQLite "regexp" function is automatically hooked to Perl regexes. See http://search.cpan.org/dist/DBD-SQLite/lib/DBD/SQLite.pm#REGEXP_function :

SQLite includes syntactic support for an infix operator 'REGEXP', but without any implementation. The DBD::SQLite driver automatically registers an implementation that performs standard perl regular expression matching, using current locale. So for example you can search for words starting with an 'A' with a query like

  SELECT * from table WHERE column REGEXP '\bA\w+'

If you want case-insensitive searching, use perl regex flags, like this :

  SELECT * from table WHERE column REGEXP '(?i:\bA\w+)'
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users