pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: select substr???
8+ messages / 5 participants
[nested] [flat]

* Re: select substr???
@ 2001-04-09 17:05 Tim Johnson <tim@celestialdesign.co.uk>
  2001-04-09 18:38 ` Re: Re: select substr??? Peter Eisentraut <peter_e@gmx.net>
  2001-04-10 16:38 ` Re: Re: select substr??? Albert REINER <areiner@tph.tuwien.ac.at>
  0 siblings, 2 replies; 8+ messages in thread

From: Tim Johnson @ 2001-04-09 17:05 UTC (permalink / raw)
  To: pgsql-sql

Hi,

I have postgres 6.x (where x is something).

I have the following list of data

data
--------
ABC*
ABC
ABC-
ABC+
ABC
QWE~
ASD+
ASD#
KLASDHK-
KLASDHK+
KLASDHK
KLASDHK*


what I want to do is 'select distinct(data) [ignoring non alphanumeric
characters] order by data'

is there a way to do that? Changing the data stored in the table is not an
option as the suffixes are needed elsewhere..

Please help !!

Thanks,
Tim.




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Re: select substr???
  2001-04-09 17:05 Re: select substr??? Tim Johnson <tim@celestialdesign.co.uk>
@ 2001-04-09 18:38 ` Peter Eisentraut <peter_e@gmx.net>
  1 sibling, 0 replies; 8+ messages in thread

From: Peter Eisentraut @ 2001-04-09 18:38 UTC (permalink / raw)
  To: Tim Johnson <tim@celestialdesign.co.uk>; +Cc: pgsql-sql

Tim Johnson writes:

> Hi,
>
> I have postgres 6.x (where x is something).
>
> I have the following list of data
>
> data
> --------
> ABC*
> ABC
> ABC-
> ABC+
> ABC
> QWE~
> ASD+
> ASD#
> KLASDHK-
> KLASDHK+
> KLASDHK
> KLASDHK*
>
>
> what I want to do is 'select distinct(data) [ignoring non alphanumeric
> characters] order by data'

Write a function that strips off the suffixes and do 'select distinct
voodoo(data) ...'.

-- 
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Re: select substr???
  2001-04-09 17:05 Re: select substr??? Tim Johnson <tim@celestialdesign.co.uk>
@ 2001-04-10 16:38 ` Albert REINER <areiner@tph.tuwien.ac.at>
  1 sibling, 0 replies; 8+ messages in thread

From: Albert REINER @ 2001-04-10 16:38 UTC (permalink / raw)
  To: pgsql-sql

On Mon, Apr 09, 2001 at 06:05:55PM +0100, Tim Johnson wrote:
> Hi,
> 
> I have postgres 6.x (where x is something).
> 
> I have the following list of data
> 
> data
> --------
> ABC*
> ABC
> ABC-
...
> what I want to do is 'select distinct(data) [ignoring non alphanumeric
> characters] order by data'

somewhere I use the following, which might be adapted to do what you
want.  I am sure there are more elegant ways of doing this, though.

     create function ComparisonString(text) returns text  as '
  declare
    t text;
    r text;
    c char;
    ns bool;
  begin
    if $1 is null then
      return NULL;
    end if;
    t = lower(trim(both $1));
    r = '''';
    ns = false;
    for i in 1 .. char_length(t) loop
      c = substring(t from i for 1);
      if c = '' '' then
        if ns then
          r = r || '' '';
        end if;
        ns = false;
      else
        if position(c in ''abcdefghijklmnopqrstuvwxyz0123456789'') > 0 then
          r = r || c;
          ns = true;
        end if;
      end if;
    end loop;
    return trim(both r);
  end;
' language 'plpgsql' with (IsCachable);

Albert.



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* RE: Re: select substr???
@ 2001-04-10 22:41 Jeff Eckermann <jeckermann@verio.net>
  2001-04-12 15:43 ` Re: RE: Re: select substr??? Albert REINER <areiner@tph.tuwien.ac.at>
  0 siblings, 1 reply; 8+ messages in thread

From: Jeff Eckermann @ 2001-04-10 22:41 UTC (permalink / raw)
  To: pgsql-sql

Regular expressions make this much easier.  The below could be shortened to:

	    create function ComparisonString(text) returns text  as '
	  declare
	    t alias for $1;
	    r text;
	    c char;
	begin   
	    if t is null  or t !~ ''[^a-zA-Z0-9]''
	   then
	      return t;
	    end if;
	    r = '''';
	    for i in 1 .. char_length(t) loop
	      c = substring(t from i for 1);
	      if c ~ ''[a-zA-Z0-9]''
	      then
	          r = r || c;
	      end if;
	    end loop;
	    return r;
	  end;
	' language 'plpgsql' with (IsCachable);



> -----Original Message-----
> From:	Albert REINER [SMTP:areiner@tph.tuwien.ac.at]
> Sent:	Tuesday, April 10, 2001 11:38 AM
> To:	pgsql-sql@postgresql.org
> Subject:	Re: Re: select substr???
> 
> On Mon, Apr 09, 2001 at 06:05:55PM +0100, Tim Johnson wrote:
> > Hi,
> > 
> > I have postgres 6.x (where x is something).
> > 
> > I have the following list of data
> > 
> > data
> > --------
> > ABC*
> > ABC
> > ABC-
> ...
> > what I want to do is 'select distinct(data) [ignoring non alphanumeric
> > characters] order by data'
> 
> somewhere I use the following, which might be adapted to do what you
> want.  I am sure there are more elegant ways of doing this, though.
> 
>      create function ComparisonString(text) returns text  as '
>   declare
>     t text;
>     r text;
>     c char;
>     ns bool;
>   begin
>     if $1 is null then
>       return NULL;
>     end if;
>     t = lower(trim(both $1));
>     r = '''';
>     ns = false;
>     for i in 1 .. char_length(t) loop
>       c = substring(t from i for 1);
>       if c = '' '' then
>         if ns then
>           r = r || '' '';
>         end if;
>         ns = false;
>       else
>         if position(c in ''abcdefghijklmnopqrstuvwxyz0123456789'') > 0
> then
>           r = r || c;
>           ns = true;
>         end if;
>       end if;
>     end loop;
>     return trim(both r);
>   end;
> ' language 'plpgsql' with (IsCachable);
> 
> Albert.
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://www.postgresql.org/search.mpl



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: RE: Re: select substr???
  2001-04-10 22:41 RE: Re: select substr??? Jeff Eckermann <jeckermann@verio.net>
@ 2001-04-12 15:43 ` Albert REINER <areiner@tph.tuwien.ac.at>
  0 siblings, 0 replies; 8+ messages in thread

From: Albert REINER @ 2001-04-12 15:43 UTC (permalink / raw)
  To: pgsql-sql

Thanks, interesting. I did not find any mention of regular expressions
in the 7.0.2 docs I installed locally.

BTW, your code does not do exactly the same, as it removes any
whitespace while the other one only collapses consecutive blanks. But,
of course, regular expressions in PL/pgSQL make this much easier.

As a further aside, I think that in both versions of the function the
check for `$1 IS NULL' is not necessary; I got the impression that
passing NULL as an argument to a function will automatically return
NULL as the result, doesn't it?

Albert.


On Tue, Apr 10, 2001 at 05:41:26PM -0500, Jeff Eckermann wrote:
> Regular expressions make this much easier.  The below could be shortened to:
> 
> 	    create function ComparisonString(text) returns text  as '
> 	  declare
> 	    t alias for $1;
> 	    r text;
> 	    c char;
> 	begin   
> 	    if t is null  or t !~ ''[^a-zA-Z0-9]''
> 	   then
> 	      return t;
> 	    end if;
> 	    r = '''';
> 	    for i in 1 .. char_length(t) loop
> 	      c = substring(t from i for 1);
> 	      if c ~ ''[a-zA-Z0-9]''
> 	      then
> 	          r = r || c;
> 	      end if;
> 	    end loop;
> 	    return r;
> 	  end;
> 	' language 'plpgsql' with (IsCachable);
> 
> > -----Original Message-----
> > From:	Albert REINER [SMTP:areiner@tph.tuwien.ac.at]
...
> > 
> > somewhere I use the following, which might be adapted to do what you
> > want.  I am sure there are more elegant ways of doing this, though.
> > 
> >      create function ComparisonString(text) returns text  as '
> >   declare
> >     t text;
> >     r text;
> >     c char;
> >     ns bool;
> >   begin
> >     if $1 is null then
> >       return NULL;
> >     end if;
> >     t = lower(trim(both $1));
> >     r = '''';
> >     ns = false;
> >     for i in 1 .. char_length(t) loop
> >       c = substring(t from i for 1);
> >       if c = '' '' then
> >         if ns then
> >           r = r || '' '';
> >         end if;
> >         ns = false;
> >       else
> >         if position(c in ''abcdefghijklmnopqrstuvwxyz0123456789'') > 0
> > then
> >           r = r || c;
> >           ns = true;
> >         end if;
> >       end if;
> >     end loop;
> >     return trim(both r);
> >   end;
> > ' language 'plpgsql' with (IsCachable);
> > 



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* RE: Re: select substr???
@ 2001-04-10 23:08 Jeff Eckermann <jeckermann@verio.net>
  0 siblings, 0 replies; 8+ messages in thread

From: Jeff Eckermann @ 2001-04-10 23:08 UTC (permalink / raw)
  To: pgsql-sql

And if you have plperl installed, something like this is even easier:
create function texttrim(text) returns text as '$_[0] =~ s/\\W//g; return
$_[0]' language 'plperl';
(I just read the docs) :-)

> -----Original Message-----
> From:	Jeff Eckermann [SMTP:jeckermann@verio.net]
> Sent:	Tuesday, April 10, 2001 5:41 PM
> To:	pgsql-sql@postgresql.org
> Subject:	RE: Re: select substr???
> 
> Regular expressions make this much easier.  The below could be shortened
> to:
> 
> 	    create function ComparisonString(text) returns text  as '
> 	  declare
> 	    t alias for $1;
> 	    r text;
> 	    c char;
> 	begin   
> 	    if t is null  or t !~ ''[^a-zA-Z0-9]''
> 	   then
> 	      return t;
> 	    end if;
> 	    r = '''';
> 	    for i in 1 .. char_length(t) loop
> 	      c = substring(t from i for 1);
> 	      if c ~ ''[a-zA-Z0-9]''
> 	      then
> 	          r = r || c;
> 	      end if;
> 	    end loop;
> 	    return r;
> 	  end;
> 	' language 'plpgsql' with (IsCachable);
> 
> 
> 
> > -----Original Message-----
> > From:	Albert REINER [SMTP:areiner@tph.tuwien.ac.at]
> > Sent:	Tuesday, April 10, 2001 11:38 AM
> > To:	pgsql-sql@postgresql.org
> > Subject:	Re: Re: select substr???
> > 
> > On Mon, Apr 09, 2001 at 06:05:55PM +0100, Tim Johnson wrote:
> > > Hi,
> > > 
> > > I have postgres 6.x (where x is something).
> > > 
> > > I have the following list of data
> > > 
> > > data
> > > --------
> > > ABC*
> > > ABC
> > > ABC-
> > ...
> > > what I want to do is 'select distinct(data) [ignoring non alphanumeric
> > > characters] order by data'
> > 
> > somewhere I use the following, which might be adapted to do what you
> > want.  I am sure there are more elegant ways of doing this, though.
> > 
> >      create function ComparisonString(text) returns text  as '
> >   declare
> >     t text;
> >     r text;
> >     c char;
> >     ns bool;
> >   begin
> >     if $1 is null then
> >       return NULL;
> >     end if;
> >     t = lower(trim(both $1));
> >     r = '''';
> >     ns = false;
> >     for i in 1 .. char_length(t) loop
> >       c = substring(t from i for 1);
> >       if c = '' '' then
> >         if ns then
> >           r = r || '' '';
> >         end if;
> >         ns = false;
> >       else
> >         if position(c in ''abcdefghijklmnopqrstuvwxyz0123456789'') > 0
> > then
> >           r = r || c;
> >           ns = true;
> >         end if;
> >       end if;
> >     end loop;
> >     return trim(both r);
> >   end;
> > ' language 'plpgsql' with (IsCachable);
> > 
> > Albert.
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> > 
> > http://www.postgresql.org/search.mpl
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* RE: RE: Re: select substr???
@ 2001-04-12 19:04 Jeff Eckermann <jeckermann@verio.net>
  2001-04-13 18:38 ` Re: RE: RE: Re: select substr??? Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 8+ messages in thread

From: Jeff Eckermann @ 2001-04-12 19:04 UTC (permalink / raw)
  To: pgsql-sql

The references to REs in the docs are a little hard to find (maybe it's just
me).  Bruce's book gives a good coverage, with the benefit of an index to
find where that is.  AFAICT, the implementation is "extended regular
expressions", like egrep.
My choice of characters to match was a little arbitrary: it would depend on
the specification.
You are correct, the check for "$1 is null" is not required.  I was
attempting an optimisation, as in "don't do anything else if this is null".
The gain would depend on how much further processing the function would
attempt before recognizing that it was dealing with a null value, which is
something that I don't know enough to tell.

> -----Original Message-----
> From:	Albert REINER [SMTP:areiner@tph.tuwien.ac.at]
> Sent:	Thursday, April 12, 2001 10:44 AM
> To:	pgsql-sql@postgresql.org
> Subject:	Re: RE: Re: select substr???
> 
> Thanks, interesting. I did not find any mention of regular expressions
> in the 7.0.2 docs I installed locally.
> 
> BTW, your code does not do exactly the same, as it removes any
> whitespace while the other one only collapses consecutive blanks. But,
> of course, regular expressions in PL/pgSQL make this much easier.
> 
> As a further aside, I think that in both versions of the function the
> check for `$1 IS NULL' is not necessary; I got the impression that
> passing NULL as an argument to a function will automatically return
> NULL as the result, doesn't it?
> 
> Albert.
> 
> 
> On Tue, Apr 10, 2001 at 05:41:26PM -0500, Jeff Eckermann wrote:
> > Regular expressions make this much easier.  The below could be shortened
> to:
> > 
> > 	    create function ComparisonString(text) returns text  as '
> > 	  declare
> > 	    t alias for $1;
> > 	    r text;
> > 	    c char;
> > 	begin   
> > 	    if t is null  or t !~ ''[^a-zA-Z0-9]''
> > 	   then
> > 	      return t;
> > 	    end if;
> > 	    r = '''';
> > 	    for i in 1 .. char_length(t) loop
> > 	      c = substring(t from i for 1);
> > 	      if c ~ ''[a-zA-Z0-9]''
> > 	      then
> > 	          r = r || c;
> > 	      end if;
> > 	    end loop;
> > 	    return r;
> > 	  end;
> > 	' language 'plpgsql' with (IsCachable);
> > 
> > > -----Original Message-----
> > > From:	Albert REINER [SMTP:areiner@tph.tuwien.ac.at]
> ...
> > > 
> > > somewhere I use the following, which might be adapted to do what you
> > > want.  I am sure there are more elegant ways of doing this, though.
> > > 
> > >      create function ComparisonString(text) returns text  as '
> > >   declare
> > >     t text;
> > >     r text;
> > >     c char;
> > >     ns bool;
> > >   begin
> > >     if $1 is null then
> > >       return NULL;
> > >     end if;
> > >     t = lower(trim(both $1));
> > >     r = '''';
> > >     ns = false;
> > >     for i in 1 .. char_length(t) loop
> > >       c = substring(t from i for 1);
> > >       if c = '' '' then
> > >         if ns then
> > >           r = r || '' '';
> > >         end if;
> > >         ns = false;
> > >       else
> > >         if position(c in ''abcdefghijklmnopqrstuvwxyz0123456789'') > 0
> > > then
> > >           r = r || c;
> > >           ns = true;
> > >         end if;
> > >       end if;
> > >     end loop;
> > >     return trim(both r);
> > >   end;
> > > ' language 'plpgsql' with (IsCachable);
> > > 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://www.postgresql.org/search.mpl



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: RE: RE: Re: select substr???
  2001-04-12 19:04 RE: RE: Re: select substr??? Jeff Eckermann <jeckermann@verio.net>
@ 2001-04-13 18:38 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 8+ messages in thread

From: Tom Lane @ 2001-04-13 18:38 UTC (permalink / raw)
  To: Jeff Eckermann <jeckermann@verio.net>; +Cc: pgsql-sql

Jeff Eckermann <jeckermann@verio.net> writes:
> You are correct, the check for "$1 is null" is not required.  I was
> attempting an optimisation, as in "don't do anything else if this is null".
> The gain would depend on how much further processing the function would
> attempt before recognizing that it was dealing with a null value, which is
> something that I don't know enough to tell.

In 7.1, checking for null would be appropriate unless you've declared
the function "strict".  A strict function won't even be called for null
input, rather a null result will be assumed automatically --- with much
less overhead than an explicit test for null would need.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2001-04-13 18:38 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-04-09 17:05 Re: select substr??? Tim Johnson <tim@celestialdesign.co.uk>
2001-04-09 18:38 ` Peter Eisentraut <peter_e@gmx.net>
2001-04-10 16:38 ` Albert REINER <areiner@tph.tuwien.ac.at>
2001-04-10 22:41 RE: Re: select substr??? Jeff Eckermann <jeckermann@verio.net>
2001-04-12 15:43 ` Albert REINER <areiner@tph.tuwien.ac.at>
2001-04-10 23:08 RE: Re: select substr??? Jeff Eckermann <jeckermann@verio.net>
2001-04-12 19:04 RE: RE: Re: select substr??? Jeff Eckermann <jeckermann@verio.net>
2001-04-13 18:38 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox