Thank you kindly, Pavel. Regards,
Tena Sakai On 2/2/10 12:38 PM, "Pavel Stehule" <pavel.steh...@gmail.com> wrote: > 2010/2/2 Tena Sakai <tsa...@gallo.ucsf.edu>: >> 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'. >> >> From there, it was a downward spiral descent... >> > > you have to use a prefix 'E' - E'some string with \backslash' > > for your case the reg. expr could be > > postgres=# select '70.5' ~ e'\\d+\.\\d+'; > ?column? > ---------- > t > (1 row) > > http://www.postgresql.org/docs/8.1/static/functions-matching.html > > or > > postgres=# select '70.5' ~ e'[[:digit:]]+\.[[:digit:]]+'; > ?column? > ---------- > t > (1 row) > > Regards > Pavel Stehule >> Please help. >> >> Thank you. >> >> Regards, >> >> Tena Sakai >> tsa...@gallo.ucsf.edu >> >> >> >> -- >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql >> -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
