Quick Links

Re: regular expressions in query

From:	elein(at)varlena(dot)com (elein)
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-13 04:40:45
Message-ID:	20050213044045.GC21990@varlena.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Thread:
Lists:	pgsql-general

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

In response to

Re: regular expressions in query at 2005-02-12 17:27:20 from Tom Lane

Responses

Re: regular expressions in query at 2005-02-13 09:57:58 from Russ Brown

Browse pgsql-general by date

	From	Date	Subject
Next Message	Russell Smith	2005-02-13 05:36:41	Re: [GENERAL] Website Documentation
Previous Message	Neil Dugan	2005-02-13 04:35:31	Re: find next in an index