Re: regular expressions in query

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "F(dot)Bissett" <fbissett(at)blueyonder(dot)co(dot)uk>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: regular expressions in query
Date: 2005-02-12 17:27:20
Message-ID: 22229.1108229240@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

"F.Bissett" <fbissett(at)blueyonder(dot)co(dot)uk> writes:
> </head><BODY BGCOLOR=3D"#F0F0F0" ><p><SPAN style=3D"font-size:10pt;">On Fri=
> , 11 Feb 2005 19:56:33 -0800, Jeff Davis wrote:<br /></SPAN><SPAN style=3D"=
> font-size:10pt;color:navy;">&gt;=A0Try using the "~" regex matching operato=
> r instead of ILIKE.</SPAN><SPAN style=3D"font-size:10pt;"><br /></SPAN><SPA=
> N style=3D"font-size:10pt;color:navy;">&gt;</SPAN><SPAN style=3D"font-size:=
> 10pt;"><br /></SPAN><SPAN style=3D"font-size:10pt;color:navy;">&gt;=A0Regar=
> ds,</SPAN><SPAN style=3D"font-size:10pt;"><br /></SPAN><SPAN style=3D"font-=
> size:10pt;color:navy;">&gt;=A0Jeff Davis</SPAN><SPAN style=3D"font-size:10p=
> t;"><br /></SPAN><SPAN style=3D"font-size:10pt;color:navy;">&gt;</SPAN></p>

Please don't post HTML email; it's a pain in the neck to quote.

> I have the following PHP to check an input string for non numeric characters:
>
> $tel = ereg_replace('[^[:digit:]]', "", $test); -- tel then equals only the numbers in test.

The closest equivalent we have to that is the regex-extraction version
of the substring() function --- see
http://www.postgresql.org/docs/8.0/static/functions-matching.html
It would go something like

substring($test from '[0-9]+')

However, what that actually gets you is the first all-numeric substring;
if there are multiple occurrences of digits separated by non-digits this
will not do what you want.

My advice is to write the function you want in one of the PLs that have
good string-mashing facilities --- either plperl or pltcl would
certainly do. (Probably plpython too, but I'm not very familiar with
Python.) Plain SQL is not very strong on string manipulation, but
that's why we have extension languages.

regards, tom lane

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Preston Landers 2005-02-12 22:32:57 database encoding "WIN" -- Western or Cyrillic?
Previous Message Scott Marlowe 2005-02-12 16:54:02 Re: regular expressions in query