Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id D0062632C55 for ; Wed, 3 Feb 2010 12:36:11 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 49896-03 for ; Wed, 3 Feb 2010 16:35:51 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.egcrc.net (63-193-205-1.egcrc.org [63.193.205.1]) by mail.postgresql.org (Postfix) with ESMTP id 9DAA9633B3D for ; Wed, 3 Feb 2010 12:36:00 -0400 (AST) Received: from EGCRC-EX04.egcrc.org ([::1]) by EGCRC-EX03.egcrc.org ([::1]) with mapi; Wed, 3 Feb 2010 08:35:57 -0800 From: Tena Sakai To: msi77 CC: "pgsql-sql@postgresql.org" Date: Wed, 3 Feb 2010 08:35:57 -0800 Subject: Re: please help me on regular expression Thread-Topic: [SQL] please help me on regular expression Thread-Index: AcqkxW18w/2Vsd6BSVii5NKUEjOX3AAKqYFu Message-ID: In-Reply-To: <21331265197116@webmail115.yandex.ru> 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="windows-1251" Content-Transfer-Encoding: quoted-printable MIME-Version: 1.0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.257 tagged_above=-10 required=5 tests=AWL=-1.172, BAYES_40=-0.185, RDNS_DYNAMIC=0.1 X-Spam-Level: X-Archive-Number: 201002/26 X-Sequence-Number: 33625 Hi, Thanks for your reply. Indeed, why not? Tena Sakai tsakai@gallo.ucsf.edu On 2/3/10 3:38 AM, "msi77" wrote: > Why not to use >=20 > select subjectid, height > from tsakai.pheno > where height like '%.%'; >=20 > ? >=20 >> 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... >> Please help. >> Thank you. >> Regards, >> Tena Sakai >> tsakai@gallo.ucsf.edu >>=20 >=20 > =C7=E4=E5=F1=FC =F1=EF=E0=EC=E0 =ED=E5=F2 http://mail.yandex.ru/nospam/si= gn