Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 7ED766331CB for ; Tue, 2 Feb 2010 19:23:08 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 62993-04 for ; Tue, 2 Feb 2010 23:22:57 +0000 (UTC) X-Greylist: delayed 00:05:26.821392 by SQLgrey-1.7.6 Received: from mail.egcrc.net (mail2.egcrc.net [63.193.204.10]) by mail.postgresql.org (Postfix) with ESMTP id 0BB9E634A2F for ; Tue, 2 Feb 2010 19:22:22 -0400 (AST) Received: from EGCRC-EX04.egcrc.org ([::1]) by EGCRC-EX03.egcrc.org ([::1]) with mapi; Tue, 2 Feb 2010 15:16:52 -0800 From: Tena Sakai To: Pavel Stehule CC: "pgsql-sql@postgresql.org" Date: Tue, 2 Feb 2010 15:16:51 -0800 Subject: Re: please help me on regular expression Thread-Topic: [SQL] please help me on regular expression Thread-Index: AcqkR7cFgWpWtTg+QhuTGqkSAU+fKwAFzGZo Message-ID: In-Reply-To: <162867791002021238j31ad8d7cge44bbce28ff451f3@mail.gmail.com> Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: user-agent: Microsoft-Entourage/13.3.0.091002 acceptlanguage: en-US Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable MIME-Version: 1.0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5 tests=BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 201002/24 X-Sequence-Number: 33623 Thank you kindly, Pavel. Regards, Tena Sakai On 2/2/10 12:38 PM, "Pavel Stehule" wrote: > 2010/2/2 Tena Sakai : >> Hi everybody, >>=20 >> I need a bit of help on postgres reqular expression. >> With a table of the following definition: >>=20 >> =A0 =A0 =A0 =A0 =A0 Table "tsakai.pheno" >> =A0Column =A0 | =A0 =A0 =A0 Type =A0 =A0 =A0 =A0| Modifiers >> -----------+-------------------+----------- >> =A0subjectid | integer =A0 =A0 =A0 =A0 =A0 | not null >> =A0height =A0 =A0| character varying | not null >> =A0race =A0 =A0 =A0| character varying | not null >> =A0blood =A0 =A0 | character varying | not null >>=20 >> I want to catch entries in height column that includes a >> decimal point. =A0Here's my attempt: >>=20 >> =A0select subjectid, height >> =A0 from tsakai.pheno >> =A0where height ~ '[:digit:]+.[:digit:]+'; >>=20 >> Which returns 0 rows, but if I get rid of where clause, >> I get rows like: >>=20 >> =A0subjectid | height >> -----------+-------- >> =A0 =A0 55379 | 70.5 >> =A0 =A0 55383 | 69 >> =A0 =A0 55395 | 70 >> =A0 =A0 56173 | 71 >> =A0 =A0 56177 | 65.5 >> =A0 =A0 56178 | 70 >> =A0 =A0 =A0 . =A0 =A0 =A0. >> =A0 =A0 =A0 . =A0 =A0 =A0. >>=20 >> And when I escape that dot after first plus sign with a backslash, >> like this: >> =A0where height ~ '[:digit:]+\.[:digit:]+'; >> then I get complaint: >>=20 >> WARNING: =A0nonstandard use of escape in a string literal >> LINE 3: where height ~ '[:digit:]+\.[:digit:]+'; >> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ^ >> HINT: =A0Use the escape string syntax for escapes, e.g., E'\r\n'. >>=20 >> From there, it was a downward spiral descent... >>=20 >=20 > you have to use a prefix 'E' - E'some string with \backslash' >=20 > for your case the reg. expr could be >=20 > postgres=3D# select '70.5' ~ e'\\d+\.\\d+'; > ?column? > ---------- > t > (1 row) >=20 > http://www.postgresql.org/docs/8.1/static/functions-matching.html >=20 > or >=20 > postgres=3D# select '70.5' ~ e'[[:digit:]]+\.[[:digit:]]+'; > ?column? > ---------- > t > (1 row) >=20 > Regards > Pavel Stehule >> Please help. >>=20 >> Thank you. >>=20 >> Regards, >>=20 >> Tena Sakai >> tsakai@gallo.ucsf.edu >>=20 >>=20 >>=20 >> -- >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql >>=20