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

On Tue, 10 Sep 2002, Daryl Beattie wrote:

> Really? The collating elements are locale-specific?!? They are actually
> hard-coded somewhere??

It's part of the locale definition AFAIK. Some languages have/had
multi-character elements that have a particular sorting position that's
different than the individual characters.

> If this is so, and this is what is meant by the definition of a collating
> element, then I am going about things the entirely wrong way when trying to
> select a varchar range. In which case I would be tempted to do something
> like this instead:
>
> testdb=# select 'test' ~* '^[[.a.]-[.z.]][[.a.]-[.a.]]';
> [same as]
> testdb=# select 'test' ~* '^[a-z][a-a]';
>
> Of course, this won't match aa-za, because it won't match ab, ac, ..., az,
> bb, bc, ..., etc.
>
> So, does anybody have any advice on how to select a range?

Umm, I'd say use an or condition for the pieces, maybe like:
^([a-y][a-z]|za)
(if the last had more than a-a you'd need a character set there)

That's a little ugly, but should work.

> Stephan, thanks for clearing me up on the collating elements. Perhaps it
> should be in the docs that they are locale-specific ... so that simpletons
> like me won't be misled into thinking that they can just make up collating
> elements as they seem fit.

That might be a good idea, yeah.