Regexp matching

From: Eduardas Kazakas <eduardas(dot)kazakas(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Regexp matching
Date: 2010-09-28 08:34:30
Message-ID: AANLkTin+K709+5M0MjHHGW_Z5WZba_v_60VcEWDq7GZB@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hello, I have some problems using character class matching (e.g. [:alpha:]).

For example I have a table:

CREATE TABLE re_test (text_column character varying (50) NOT NULL);

Notice, that there are some specific characters.

INSERT INTO re_test VALUES ('AŠDF');
INSERT INTO re_test VALUES ('AŠDF45');
INSERT INTO re_test VALUES ('AŠDF FDŠA');
INSERT INTO re_test VALUES ('ASDF FDŠA');
INSERT INTO re_test VALUES ('58ASDF FDŠA');
INSERT INTO re_test VALUES ('ašDf');
INSERT INTO re_test VALUES ('aŠdf');

SELECT * FROM re_test WHERE text_column ~ '[^[:alpha:]]' and text_column ~
[:upper:];

Goal:
I want to write such statement which returns me only those records which
have only one word and those words must be uppercase.
So I expect this statement to return only one record where text_column =
AŠDF.

Maybe someone could give me more detail explanation how to use those regexp
classes, because the documentation tells very little about this.

Some more information:

PostgreSQL9

OS - Windows x86-32
DB encoding - UTF-8
lc_collate - English_United States.1252
lc_ctype - English_United States.1252
lc_messages - English_United States.1252
lc_monetary - English_United States.1252
lc_numeric - English_United States.1252
lc_time - English_United States.1252

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message gangadhar bandaru 2010-09-28 09:02:23 eB7E2R
Previous Message Tarlika Elisabeth Schmitz 2010-09-27 16:54:18 Re: identifying duplicates in table with redundancies