From: Tena Sakai <tsakai@gallo.ucsf.edu>
To: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
Date: Tue, 2 Feb 2010 11:22:54 -0800
Subject: please help me on regular expression
Thread-Topic: please help me on regular expression
Thread-Index: AcqkPjlQLbYtXREqQfa692dmE3caig==
Message-ID: <C78DBD69.918D%tsakai@gallo.ucsf.edu>
Accept-Language: en-US
Content-Language: en-US
user-agent: Microsoft-Entourage/13.3.0.091002
acceptlanguage: en-US
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0

Hi everybody,

I need a bit of help on postgres reqular expression.
With a table of the following definition:

           Table "tsakai.pheno"
  Column   |       Type        | Modifiers
-----------+-------------------+-----------
 subjectid | integer           | not null
 height    | character varying | not null
 race      | character varying | not null
 blood     | character varying | not null

I want to catch entries in height column that includes a
decimal point.  Here's my attempt:

 select subjectid, height
   from tsakai.pheno
  where height ~ '[:digit:]+.[:digit:]+';

Which returns 0 rows, but if I get rid of where clause,
I get rows like:

 subjectid | height
-----------+--------
     55379 | 70.5
     55383 | 69
     55395 | 70
     56173 | 71
     56177 | 65.5
     56178 | 70
       .      .
       .      .

And when I escape that dot after first plus sign with a backslash,
like this:
  where height ~ '[:digit:]+\.[:digit:]+';
then I get complaint:

WARNING:  nonstandard use of escape in a string literal
LINE 3: where height ~ '[:digit:]+\.[:digit:]+';
                       ^
HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.