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

Stephan Szabo sszabo(at)bigpanda(dot)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.