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

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: 04/Feb/16 05:39 PM
Component/s: Engine
Affects Version/s: None
Fix Version/s: 2.5 Alpha 1

Issue Links:

SF_ID: 807985
Target: 2.5.0
QA Status: Done successfully
Test Details: See also test for CORE-2006

 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

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

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
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

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

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

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   Change History   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.