Обсуждение: 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 nullheight | character varying | not
nullrace | character varying | not nullblood | 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
2010/2/2 Tena Sakai <tsakai@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 > tsakai@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 >
Thank you kindly, Pavel. Regards, Tena Sakai On 2/2/10 12:38 PM, "Pavel Stehule" <pavel.stehule@gmail.com> wrote: > 2010/2/2 Tena Sakai <tsakai@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 >> tsakai@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 >>
Why not to use select subjectid, height from tsakai.phenowhere height like '%.%'; ? > 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 > Здесь спама нет http://mail.yandex.ru/nospam/sign
Hi, Thanks for your reply. Indeed, why not? Tena Sakai tsakai@gallo.ucsf.edu On 2/3/10 3:38 AM, "msi77" <msi77@yandex.ru> wrote: > Why not to use > > select subjectid, height > from tsakai.pheno > where height like '%.%'; > > ? > >> 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 >> > > Здесь спама нет http://mail.yandex.ru/nospam/sign
Be careful when working with backslashes and regular expressions for the proper (double) escaping! # select '70a5' ~ e'\\d+\.\\d+';?column? ----------t (1 row) # select '70a5' ~ e'\\d+\\.\\d+';?column? ----------f (1 row) # select '70.5' ~ e'\\d+\\.\\d+';?column? ----------t -- ---> Dirk Jagdmann ----> http://cubic.org/~doj -----> http://llg.cubic.org
Thank you, Dirk. Regards, Tena Sakai tsakai@gallo.ucsf.edu On 2/3/10 11:43 AM, "Dirk Jagdmann" <jagdmann@gmail.com> wrote: > Be careful when working with backslashes and regular expressions for > the proper (double) escaping! > > # select '70a5' ~ e'\\d+\.\\d+'; > ?column? > ---------- > t > (1 row) > > # select '70a5' ~ e'\\d+\\.\\d+'; > ?column? > ---------- > f > (1 row) > > # select '70.5' ~ e'\\d+\\.\\d+'; > ?column? > ---------- > t
Some people, when confronted with a problem, think "I know, I'll use regular expressions." Now they have two problems. — Jamie Zawinski 3 feb 2010 kl. 21.32 skrev Tena Sakai: > Thank you, Dirk. > > Regards, > > Tena Sakai > tsakai@gallo.ucsf.edu > > > On 2/3/10 11:43 AM, "Dirk Jagdmann" <jagdmann@gmail.com> wrote: > >> Be careful when working with backslashes and regular expressions for >> the proper (double) escaping! >> >> # select '70a5' ~ e'\\d+\.\\d+'; >> ?column? >> ---------- >> t >> (1 row) >> >> # select '70a5' ~ e'\\d+\\.\\d+'; >> ?column? >> ---------- >> f >> (1 row) >> >> # select '70.5' ~ e'\\d+\\.\\d+'; >> ?column? >> ---------- >> t > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql