Umberto wrote:
>
> ----- Original Message -----
> From: "Zabach, Elke" <[EMAIL PROTECTED]>
> To: "'Umberto'" <[EMAIL PROTECTED]>;
> <[EMAIL PROTECTED]>
> Sent: Friday, July 04, 2003 4:27 AM
> Subject: RE: SAPDB 7.4 LIKE pattern matching ranges
>
>
> > Umberto wrote:
> > >
> > > Hello,
> > >
> > > We are implementing a simple billing system using SAPDB 7.4
> > > under Linux
> > > (Redhat 9 and SuSE 8.1) that requires some basic pattern
> matching with
> > > ranges.
> > >
> > > According to this thread, it supports just what we need:
> > >
> > >
> http://listserv.sap.com/pipermail/sapdb.general/2001-September
/008355.html
>
> > However, the range spec doesn't seem to work.
> >
> > For example, assuming that the table Billing has a record with the value
> > '9665277273' in the cNumber column, the following query does not return
any
> > results:
> >
> > SELECT * FROM Billing WHERE cNumber LIKE '966(0-5)*'
> >
> > On the other hand, this does:
> >
> > SELECT * FROM Billing WHERE cNumber LIKE '9665%'
> >
> > The latter is not practical since the user would have to query for every
> > number between 9660% and 9665% individually.
> >
> > Am I missing something?
>
> Bad luck. Because of our main customer (our own company) we had to change
> the like-predicate according to SQL92-standard, meaning that only
> '%' and '_' as written in the docs
>
http://www.sapdb.org/7.4/htmhelp/8c/ccce27c71c11d2a97100a0c9449261/frameset.
htm
> are handled starting with 7.4.3.17.
> I'm not sure I understand the reasoning behind this. Since it already
> supported '%' and '_' *in addition to* ranges, what benefit was there to
> remove the ranges?
In SQL-standard a '(' is a '(', nothing more, nothing less, no special meaning.
If one is looking for a '(', he can just write '(', no escape character needed.
If the ranges still existed, '(' has a special meaning, i.e. the beginning of a range,
and to be able to search for '(' you have to use escape-character. --> it is not
ANSI-like.
Therefore, to be standard-conform we had to remove the ranges, sorry.
Elke
SAP Labs Berlin
>
> > In your example cNumber >= '9660' and cNumber < '9666' could be helpful.
> > in more complex situations (several ranges in the like-pattern) this will not do,
> > unfortunately.
>
> This doesn't help because the fact of the matter is that the patterns are
> actually stored in the database and whole numbers are provided by user
> input. So, the query actually looks like this:
>
> SELECT * FROM Billing WHERE '96611623652' LIKE cPattern
>
> This would normally match a record where cPattern = '966(0-5)*'.
>
> What benefit was there in removing a feature that did not interfere with the
> SQL92-standard basic functionality? If we go to REGEXP instead in a future
> release, I assume we'll lose the ability to use the index as in MySQL.
>
>
>
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
