[Info-ingres] Am I doing SIMILAR wrong?

[Martin Bowes]

Hi Roy,



It's something going wrong with the period...or our interpretation of it...!



If I replace it with the [:punct:] set I get some very weird results.



Ie. This works as I expect.

select * from foo where name not similar to '[-[:UPPER:][:punct:]]+';



┌──────────┐

│name      │

├──────────┤

└──────────┘

(0 rows)



But this doesn't:



select * from foo where name not similar to '[-[:UPPER:].]+';

┌──────────┐

│name      │

├──────────┤

│HANN      │

│LARGE-HANN│

│ST.HANN   │

└──────────┘

(3 rows)





Marty

-----Original Message-----
From: Roy Hann [mailto:specially at processed.almost.meat]
Sent: 23 July 2019 12:06
To: info-ingres at lists.planetingres.org
Subject: [Info-ingres] Am I doing SIMILAR wrong?



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

_______________________________________________

Info-ingres mailing list

Info-ingres at lists.planetingres.org

https://lists.planetingres.org/mailman/listinfo/info-ingres
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.planetingres.org/pipermail/info-ingres/attachments/20190723/8fbb14aa/attachment.html>