[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