Re: [GENERAL] Selecting Varchar range (through JDBC).

Stephan Szabo sszabo@bigpanda.com

On Tue, 10 Sep 2002, Daryl Beattie wrote:

> Dear PostgreSQL people,
>
>     A little while ago I posted a message about selecting a varchar
> range through JDBC. Thomas was nice enough to give me some pointers, and I
> went back to the database and tested some things out. The clincher came when
> I realized that one of my JUnit tests was actually passing using the varchar
> range; some of the regular expressions worked, while others did not.
>     What I found is that using the collating elements in regex
> comparisons does not work if the collating arguments are more than one
> character in length. According to
> http://www.postgresql.org/idocs/index.php?functions-matching.html#FUNCTIONS-
> REGEXP, regular expressions should work with multi-character collating
> arguments. I did a bunch of tests at the PostgreSQL command-line to verify
> this finding. Here are some highlights:
>
> testdb=# select 'test' ~* '^[[.a.]-[.z.]]';
>  ?column?
> ----------
>  t
> (1 row)
>
> testdb=# select 'test' ~* '^[[.aa.]-[.za.]]';
> ERROR:  Invalid regular expression: invalid collating element
> testdb=# select 'test' ~* '^[[.a.]-[.za.]]';
> ERROR:  Invalid regular expression: invalid collating element
> testdb=# select 'test' ~* '^[[.aa.]-[.z.]]';

Are you really in a locale where aa or za are collating elements?

> ERROR:  Invalid regular expression: invalid collating element
> testdb=# select 'chchcc' ~ '[[.ch.]]*c';
> ERROR:  Invalid regular expression: invalid collating element
>
>
>     That last test was taken straight from the PostgreSQL interactive
> documentation at the link that I provided above. The documentation states
> that it should match.

Note the caveat in the paragraph above.  If the collating sequence
includes a ch collating element...
It means if you're in a locale where ch collates as a single element you
can use .ch. to refer to that element.