Issue Details (XML | Word | Printable)

Key: CORE-769
Type: New Feature New Feature
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Adriano dos Santos Fernandes
Reporter: Pavel Cisar
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Wildcards/Regular Expressions in WHERE clause - SIMILAR TO predicate

Created: 17/Sep/03 12:00 AM   Updated: 21/Jun/11 11:44 AM
Component/s: Engine
Affects Version/s: None
Fix Version/s: 2.5 Alpha 1

Time Tracking:
Not Specified

Issue Links:
Relate
 

SF_ID: 807985
Target: 2.5.0
Wiki Page: https://wiki.firebirdsql.org/wiki/index.php?page=SIMILAR+TO


 Description  « Hide
SFID: 807985#
Submitted By: pcisar

A RegEx could be compiled and hence run faster against
a table.
----------------------
User: peter_jacobi
Logged In: YES
user_id=845149

Responding to rdifalco comments:

Your query can be formulated in standard SQL as:
... WHERE some_text LIKE 'ttl%=%1';
and will be correctly executed by Firebird.

Generally speaking:

More powerful RegExps are already in FB, see
src/jrd/evl_like.cpp, but not exposed to the SQL
frontend (yet).
----------------------
User: rdifalco
Logged In: YES
user_id=571449

This is a VERY cool thing about MySQL. It allows regular
expression selects on VARCHAR or TEXT BLOB columns. This
is quite powerful and regular expressions are more and
more
becoming the standard for text searching and matching.

For example, I can do:

   some_text matches "ttl*.=*.1"

To match "ttl=1", "ttl =1", "ttl= 1", "ttl = 1", etc.
----------------------
User: groovbird
Logged In: YES
user_id=17128

Wildcards are patterns, Regular Expressions are patterns,
but a lot more flexible. I suggest you take a look at the
following documents:

http://sitescooper.org/tao_regexps.html

http://lib.stat.cmu.edu/scgn/v52/section1_7_0_1.html
----------------------
User: helebor
Logged In: YES
user_id=60469

How is this RegEx different to a CONTAINING clause?

select blah,.... from foo
where blahsomething CONTAINING 'blah' ;

Are you talking about wildcards?
----------------------
User: groovbird
Logged In: YES
user_id=17128

A Regular Expression is a way of testing wether a string
matches a certain pattern. Regular Expressions are
commonly known among Unix developers; it exists in the
most popular scripting languages, from Perl to PHP. It
originates from the unix command "grep".

The "like" clause in SQL today has a similar purpose,
allthough the possibilities are extremely limited.

In most implementations, a RegEx can be compiled to be
reused against a number of strings. This way, it would be
nice to have a "like" clause using Regular expressions.
Given the following example:

select * from employees where name matches '^[ab]';

would select all employees whose name starts with either a
or b.

Implementations of Regular Expressions for use in Unix
C/C++ aplications are readily available as a shared
library, but I'm myself not an experienced C developer
so I
wouldn't be able to just "jump in" and do it myself.

 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Adriano dos Santos Fernandes added a comment - 28/Jul/06 06:20 AM
Will be done with the SQL standard SIMILAR predicate.

Adriano dos Santos Fernandes added a comment - 25/Nov/07 03:22 PM
Implemented SIMILAR TO predicate.