Re: Matching uppercased russian words (\x0410-\x042F) in UTF8 database 8.4.13

[Albe Laurenz <laurenz.albe@xxxxxxxxxx>]

Alexander Farber wrote:
> I have prepared an SQL fiddle for my question:
> http://sqlfiddle.com/#!11/8a494/4
> 
> And also described it in more detail at
> http://stackoverflow.com/questions/15500270/string-matching-in-insert-trigger-how-to-use-in-
> conditionals-to-return-null
> 
> Does anybody please know how to check for
> UTF8 range \x0410-\x042F in my code below?
> 
> I've tried both
> new.word !~ '^[\x0410-\x042F]{2,}$'
> (fails with syntax error) and
> new.word !~ '^[\u0410-\u042F]{2,}$'
> (triggers even for correct words):

Strange, it works here (RHEL 6, x86_64, PostgreSQL 9.2.2,
encoding "UTF8", collation and ctype "de_DE.UTF8"):

test=> SELECT 'ПРОВЕРКА' ~ '^[\u0410-\u042F]{2,}$';
 ?column?
----------
 t
(1 row)

test=> SELECT 'ABCDE' ~ '^[\u0410-\u042F]{2,}$';
 ?column?
----------
 f
(1 row)

> create table good_words (
>         word varchar(64) primary key
> );
> 
> create or replace function keep_clean() returns trigger as $body$
>         begin
>                 new.word := upper(new.word);
> 
>                 /* next line does not compile? */
>                 IF new.word !~ '^[\x0410-\x042F]{2,}$' THEN
>                     RAISE EXCEPTION 'Not an uppercased Russian word in UTF8';
>                 END IF;
> 
>                 IF new.word ~ '^[ЪЫЬ]' OR new.word ~ 'Ъ$' THEN
>                     return NULL;
>                 END IF;
> 
>                 /* does not return NULL for 'ошибббка'? */
>                 IF new.word ~ '(.)\1\1' AND new.word NOT LIKE '%ШЕЕЕ%'
> AND new.word NOT LIKE '%ЗМЕЕЕ%' THEN
>                     return NULL;

This works for me as well:

test=> SELECT 'ошибббка' ~ '(.)\1\1'
          AND 'ошибббка' NOT LIKE '%ШЕЕЕ%'
          AND 'ошибббка' NOT LIKE '%ЗМЕЕЕ%';
 ?column?
----------
 t
(1 row)

test=> SELECT 'ошиббка' ~ '(.)\1\1'
          AND 'ошиббка' NOT LIKE '%ШЕЕЕ%'
          AND 'ошиббка' NOT LIKE '%ЗМЕЕЕ%';
 ?column?
----------
 f
(1 row)

>                 END IF;
> 
>                 return new;
>         end;
> $body$ language plpgsql;

What do you get for

SELECT pg_encoding_to_char(encoding),
       datcollate,
       datctype
FROM pg_database WHERE datname = current_database();

and for 

SHOW client_encoding;

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general