Re: [SQL] please help me on regular expression
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 > [email protected] > Здесь спама нет http://mail.yandex.ru/nospam/sign -- 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
Hi, Thanks for your reply. Indeed, why not? Tena Sakai [email protected] On 2/3/10 3:38 AM, "msi77" 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 >> [email protected] >> > > Здесь спама нет http://mail.yandex.ru/nospam/sign -- 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
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 -- 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, Dirk. Regards, Tena Sakai [email protected] On 2/3/10 11:43 AM, "Dirk Jagdmann" 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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] please help me on regular expression
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 > [email protected] > > > On 2/3/10 11:43 AM, "Dirk Jagdmann" 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 ([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
