Alternates in LIKE

#1

Can anyone tell me which Ingres release first introduced alternates in
the LIKE predicate?

--
Roy

UK Ingres User Association Conference 2010 will be on Tuesday June 8 2010
Go to http://www.iua.org.uk/join to get on the mailing list.

#2

On 22 Jan., 09:03, Roy Hann <specia... (AT) processed (DOT) almost.meat> wrote:

Quote:

Can anyone tell me which Ingres release first introduced alternates in
the LIKE predicate?

--
Roy

Hi all,

I must admit that I don't know what alternates in the LIKE predicate
look like. So as I'm curious: can anybody give an SQL example for
this?

TIA
Gerhard

#3

Gerhard Hofmann wrote:

Quote:

On 22 Jan., 09:03, Roy Hann <specia... (AT) processed (DOT) almost.meat> wrote:
Can anyone tell me which Ingres release first introduced alternates in
the LIKE predicate?

--
Roy

Hi all,

I must admit that I don't know what alternates in the LIKE predicate
look like. So as I'm curious: can anybody give an SQL example for
this?

The | character, preceded by your favorite escape character is used to
delimit multiple possible patters to match. Here is an example that
matches strings begining with "Wh" or ending with "er":

* select description from parts
* where description like 'Wh%\|%er' escape '\' \g
Executing . . .

+--------------------+

Quote:

(4 rows)
continue
*

--
Roy

UK Ingres User Association Conference 2010 will be on Tuesday June 8 2010
Go to http://www.iua.org.uk/join to get on the mailing list.

#4

Quote:

I must admit that I don't know what alternates in the LIKE predicate
look like. So as I'm curious: can anybody give an SQL example for
this?

Ditto.

I just trawled the 9.2.0 SQL reference guide and the description of the
like predicate doesn't include anything on alternates.

Martin Bowes

#5

Hi all,

And for 2.6 (HP) & 9.2.0 (WNT) error message with this syntax is :
E_AD1018 Illegal pattern match specified: Illegal ESCAPE sequence.
The ESCAPE char must be followed by one of:
`%' (percent)
`_' (underscore)
`[' (left square bracket)
`]' (right square bracket)
another ESCAPE char.

--
Jean-Pierre Zuate
La Fage Conseil
+33(0)6 11 40 11 09
jean-pierre.zuate (AT) lafageconseil (DOT) fr
http://lafageconseil.fr/

2010/1/25 Martin Bowes <martin.bowes (AT) ctsu (DOT) ox.ac.uk>

Quote:

I must admit that I don't know what alternates in the LIKE predicate
look like. So as I'm curious: can anybody give an SQL example for
this?

Ditto.

I just trawled the 9.2.0 SQL reference guide and the description of the
like predicate doesn't include anything on alternates.

Martin Bowes

_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://ext-cando.kettleriverconsulti...fo/info-ingres

#6

So What am I doing wrong here:

II 9.2.0 (a64.lnx/143)NPTL + p13643

select name from iidatabase where trim(name) like 'a%\|%e' escape '\';
....SQL OK but erroneously returns no rows.

select name from iidatabase where trim(name) like 'g%\|%e' escape '\';
....SQL Stynatx error.
E_AD1018 Illegal pattern match specified: Illegal ESCAPE sequence.
The ESCAPE char must be followed by one of:
`%' (percent)
`_' (underscore)
`[' (left square bracket)
`]' (right square bracket)
another ESCAPE char.
(Mon Jan 25 10:46:01 2010)

Marty

-----Original Message-----
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of Roy
Hann
Sent: 25 January 2010 10:31
To: info-ingres (AT) kettleriverconsulting (DOT) com
Subject: Re: [Info-Ingres] Alternates in LIKE

Gerhard Hofmann wrote:

Quote:

On 22 Jan., 09:03, Roy Hann <specia... (AT) processed (DOT) almost.meat> wrote:
Can anyone tell me which Ingres release first introduced alternates
in
the LIKE predicate?

--
Roy

Hi all,

I must admit that I don't know what alternates in the LIKE predicate
look like. So as I'm curious: can anybody give an SQL example for
this?

The | character, preceded by your favorite escape character is used to
delimit multiple possible patters to match. Here is an example that
matches strings begining with "Wh" or ending with "er":

* select description from parts
* where description like 'Wh%\|%er' escape '\' \g
Executing . . .

+--------------------+

Quote:

(4 rows)
continue
*

--
Roy

UK Ingres User Association Conference 2010 will be on Tuesday June 8
2010
Go to http://www.iua.org.uk/join to get on the mailing list.

_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://ext-cando.kettleriverconsulti...fo/info-ingres

#7

It seems to be when you have a result drained by the like clause.

For me no error message for 'a%' and so on, but I've got some for 'i%' ...
--
Jean-Pierre Zuate
La Fage Conseil
+33(0)6 11 40 11 09
jean-pierre.zuate (AT) lafageconseil (DOT) fr
http://lafageconseil.fr/

2010/1/25 Martin Bowes <martin.bowes (AT) ctsu (DOT) ox.ac.uk>

Quote:

So What am I doing wrong here:

II 9.2.0 (a64.lnx/143)NPTL + p13643

select name from iidatabase where trim(name) like 'a%\|%e' escape '\';
...SQL OK but erroneously returns no rows.

select name from iidatabase where trim(name) like 'g%\|%e' escape '\';
...SQL Stynatx error.
E_AD1018 Illegal pattern match specified: Illegal ESCAPE sequence.
The ESCAPE char must be followed by one of:
`%' (percent)
`_' (underscore)
`[' (left square bracket)
`]' (right square bracket)
another ESCAPE char.
(Mon Jan 25 10:46:01 2010)

Marty

-----Original Message-----
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of Roy
Hann
Sent: 25 January 2010 10:31
To: info-ingres (AT) kettleriverconsulting (DOT) com
Subject: Re: [Info-Ingres] Alternates in LIKE

Gerhard Hofmann wrote:

On 22 Jan., 09:03, Roy Hann <specia... (AT) processed (DOT) almost.meat> wrote:
Can anyone tell me which Ingres release first introduced alternates
in
the LIKE predicate?

--
Roy

Hi all,

I must admit that I don't know what alternates in the LIKE predicate
look like. So as I'm curious: can anybody give an SQL example for
this?

The | character, preceded by your favorite escape character is used to
delimit multiple possible patters to match. Here is an example that
matches strings begining with "Wh" or ending with "er":

* select description from parts
* where description like 'Wh%\|%er' escape '\' \g
Executing . . .

+--------------------+
|description |
+--------------------+
|Fastener |
|Wheel Assy |
|Header |
|Trailer |
+--------------------+
(4 rows)
continue
*

--
Roy

UK Ingres User Association Conference 2010 will be on Tuesday June 8
2010
Go to http://www.iua.org.uk/join to get on the mailing list.

_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://ext-cando.kettleriverconsulti...fo/info-ingres

_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://ext-cando.kettleriverconsulti...fo/info-ingres

#8

Martin Bowes wrote:

Quote:

So What am I doing wrong here:

II 9.2.0 (a64.lnx/143)NPTL + p13643

select name from iidatabase where trim(name) like 'a%\|%e' escape '\';
...SQL OK but erroneously returns no rows.

select name from iidatabase where trim(name) like 'g%\|%e' escape '\';
...SQL Stynatx error.
E_AD1018 Illegal pattern match specified: Illegal ESCAPE sequence.
The ESCAPE char must be followed by one of:
`%' (percent)
`_' (underscore)
`[' (left square bracket)
`]' (right square bracket)
another ESCAPE char.
(Mon Jan 25 10:46:01 2010)

Looks like a bug in 9.2 doesn't it? A slight variant of your queries
work as expected in 10.0.

c:\temp>sql iidbdb
INGRES TERMINAL MONITOR Copyright 2009 Ingres Corporation
Ingres Microsoft Windows Version II 10.0.0 (int.w32/109) login
Mon Jan 25 11:10:18 2010

continue
* select name from iidatabase where trim(name) like 'i%\|%e' escape '\'; \g
Executing . . .

+--------------------------------+

Quote:

name |
+--------------------------------+
imadb |
isidb |
iidbdb |
+--------------------------------+

(3 rows)
continue
* select name from iidatabase where trim(name) like 'g%\|%e' escape '\'; \g
Executing . . .

+--------------------------------+

Quote:

name |
+--------------------------------+

+--------------------------------+
(0 rows)
continue
*

--
Roy

UK Ingres User Association Conference 2010 will be on Tuesday June 8 2010
Go to http://www.iua.org.uk/join to get on the mailing list.

c

#9

Quote:

-----Original Message-----
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com [mailto:info-
ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of Martin Bowes
Sent: 25 January 2010 10:35
To: Ingres and related product discussion forum
Cc: Gerhard Hofmann
Subject: Re: [Info-Ingres] Alternates in LIKE

I must admit that I don't know what alternates in the LIKE predicate
look like. So as I'm curious: can anybody give an SQL example for
this?

Ditto.

I just trawled the 9.2.0 SQL reference guide and the description of
the
like predicate doesn't include anything on alternates.

Martin Bowes

It's in the 9.3 documentation. Looks like if it is in 9.2 then it would
be in a later patch.

#10

Do you want to raise it as a bug or shall I?

Marty

From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of
Jean-Pierre Zuate, La Fage Conseil
Sent: 25 January 2010 11:08
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] Alternates in LIKE

It seems to be when you have a result drained by the like clause.

For me no error message for 'a%' and so on, but I've got some for 'i%'
....
--
Jean-Pierre Zuate
La Fage Conseil
+33(0)6 11 40 11 09
jean-pierre.zuate (AT) lafageconseil (DOT) fr
http://lafageconseil.fr/

2010/1/25 Martin Bowes <martin.bowes (AT) ctsu (DOT) ox.ac.uk>

So What am I doing wrong here:

II 9.2.0 (a64.lnx/143)NPTL + p13643

select name from iidatabase where trim(name) like 'a%\|%e' escape '\';
....SQL OK but erroneously returns no rows.

select name from iidatabase where trim(name) like 'g%\|%e' escape '\';
....SQL Stynatx error.

E_AD1018 Illegal pattern match specified: Illegal ESCAPE sequence.
The ESCAPE char must be followed by one of:
`%' (percent)
`_' (underscore)
`[' (left square bracket)
`]' (right square bracket)
another ESCAPE char.

(Mon Jan 25 10:46:01 2010)

Marty

-----Original Message-----
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of Roy
Hann

Sent: 25 January 2010 10:31
To: info-ingres (AT) kettleriverconsulting (DOT) com

Subject: Re: [Info-Ingres] Alternates in LIKE

Gerhard Hofmann wrote:

Quote:

On 22 Jan., 09:03, Roy Hann <specia... (AT) processed (DOT) almost.meat> wrote:
Can anyone tell me which Ingres release first introduced alternates
in
the LIKE predicate?

--
Roy

Hi all,

I must admit that I don't know what alternates in the LIKE predicate
look like. So as I'm curious: can anybody give an SQL example for
this?

The | character, preceded by your favorite escape character is used to
delimit multiple possible patters to match. Here is an example that
matches strings begining with "Wh" or ending with "er":

* select description from parts
* where description like 'Wh%\|%er' escape '\' \g
Executing . . .

+--------------------+

Quote:

(4 rows)
continue
*

--
Roy

UK Ingres User Association Conference 2010 will be on Tuesday June 8
2010
Go to http://www.iua.org.uk/join to get on the mailing list.

_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://ext-cando.kettleriverconsulti...fo/info-ingres

_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://ext-cando.kettleriverconsulti...fo/info-ingres