[Info-ingres] Am I doing SIMILAR wrong?
Roy Hann
specially at processed.almost.meat
Tue Jul 23 11:06:07 UTC 2019
I am trying to write a simple data validation query looking for names
containing impermissible characters. I am falling down over names
containing a '.' character. Here's what I'm seeing:
* create table foo ( name varchar(10) not null );
* insert into foo values ('HANN'),('LARGE-HANN'),('ST.HANN');
* \g
Executing . . .
(3 rows)
continue
* select * from foo \g
Executing . . .
+----------+
|name |
+----------+
|HANN |
|LARGE-HANN|
|ST.HANN |
+----------+
(3 rows)
continue
* select * from foo where name not similar to '[-''ABCDEFGHIJKLMNOPQRSTUVWXYZ]+' \g
Executing . . .
+----------+
|name |
+----------+
|ST.HANN |
+----------+
(1 row)
continue
---------------------------
So far, so good. But now...
---------------------------
* select * from foo where name not similar to '[-.''ABCDEFGHIJKLMNOPQRSTUVWXYZ]+' \g
Executing . . .
+----------+
|name |
+----------+
|HANN |
|LARGE-HANN|
|ST.HANN |
+----------+
(3 rows)
continue
---------------------------------------------------------------------
I am pretty sure '.' is not a special character in a character class,
and sure enough, if I try to escape it....
---------------------------------------------------------------------
* select * from foo where name not similar to '[-\.''ABCDEFGHIJKLMNOPQRSTUVWXYZ]+' escape '\' \g
Executing . . .
E_AD101D Illegal pattern match specified: Illegal ESCAPE sequence.
The ESCAPE char must be followed by one of:
`[' (left bracket)
`]' (right bracket)
`(' (left paren)
`)' (right paren)
`|' (vertical bar)
`^' (circumflex)
`-' (minus sign)
`+' (plus sign)
`*' (asterisk)
`_' (underscore)
`%' (percent)
`?' (question mark)
`{' (left brace)
another ESCAPE char.
(Tue Jul 23 11:55:21 2019)
Am I doing something wrong? I can't for the life of me see it.
FYI, this is II 11.0.0 (su9.us5/100)
Roy
More information about the Info-ingres
mailing list