No doubt someone more adept at perl can write
this function as a one-liner.
create or replace function just_digits(text)
returns text as
$$
my $innum = $_[0];
$innum =~ s/\D//g;
return $innum;
$$ language 'plperl'
SELECT telephone FROM addresses
WHERE user_id = 'bob'
AND just_digits(telephone) = '1115551212';
--elein
On Sat, Feb 12, 2005 at 12:27:20PM -0500, Tom Lane wrote:
> "F.Bissett" <fbissett@xxxxxxxxxxxxxxxx> 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;">>=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;">></SPAN><SPAN style=3D"font-size:=
> > 10pt;"><br /></SPAN><SPAN style=3D"font-size:10pt;color:navy;">>=A0Regar=
> > ds,</SPAN><SPAN style=3D"font-size:10pt;"><br /></SPAN><SPAN style=3D"font-=
> > size:10pt;color:navy;">>=A0Jeff Davis</SPAN><SPAN style=3D"font-size:10p=
> > t;"><br /></SPAN><SPAN style=3D"font-size:10pt;color:navy;">></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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@xxxxxxxxxxxxxx
