[SQL] please help me on regular expression
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 [email protected] -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] please help me on regular expression
2010/2/2 Tena Sakai : > 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 > [email protected] > > > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] please help me on regular expression
Thank you kindly, Pavel. Regards, Tena Sakai On 2/2/10 12:38 PM, "Pavel Stehule" wrote: > 2010/2/2 Tena Sakai : >> 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 >> [email protected] >> >> >> >> -- >> Sent via pgsql-sql mailing list ([email protected]) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql >> -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
