From: Colin Wetherbee <cww@denterprises.org>
To: Jeff Frost <jeff@frostconsultingllc.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: finding columns that have three or fewer distinct characters
Date: Wed, 05 Mar 2008 12:56:01 -0500
Message-ID: <47CEDEB1.3050902@denterprises.org> (raw)
In-Reply-To: <Pine.LNX.4.64.0803050912570.4804@discord.home.frostconsultingllc.com>
References: <Pine.LNX.4.64.0803050912570.4804@discord.home.frostconsultingllc.com>
Jeff Frost wrote:
> I've got an interesting one...I'm trying to find columns that have three
> or fewer distinct characters (for example, "aaaaaaaaaa"). Wondering if
> I need to write a function or if someone has an idea how to do it with
> built in functions and/or pattern matching?
>
> I think the thing to do would be to lowercase everything, then remove
> all duplicate chars and spaces, then use length() on that, but it's not
> obvious to me how I might remove the duplicate chars with the pattern
> matching support in the docs.
It's interesting, indeed.
Here's how you might do it with a PL/Perl function. :)
CREATE OR REPLACE FUNCTION remove_duplicates(TEXT) RETURNS TEXT AS
$$
my ($text) = @_;
while ($text =~ s/(.)(.*)\1/$1$2/g != 0) {};
return $text;
$$ LANGUAGE plperl;
cww=# SELECT
remove_duplicates('ffffoooooooobbbbaaaarrrr.!@#$.foobar-baz-qux');
remove_duplicates
-------------------
fobar.!@#$-zqux
(1 row)
Colin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: cww@denterprises.org, jeff@frostconsultingllc.com
Subject: Re: finding columns that have three or fewer distinct characters
In-Reply-To: <47CEDEB1.3050902@denterprises.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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