pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedfinding columns that have three or fewer distinct characters
4+ messages / 3 participants
[nested] [flat]
* finding columns that have three or fewer distinct characters
@ 2008-03-05 17:33 Jeff Frost <jeff@frostconsultingllc.com>
0 siblings, 2 replies; 4+ messages in thread
From: Jeff Frost @ 2008-03-05 17:33 UTC (permalink / raw)
To: pgsql-sql
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.
--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: finding columns that have three or fewer distinct characters
@ 2008-03-05 17:56 Colin Wetherbee <cww@denterprises.org>
parent: Jeff Frost <jeff@frostconsultingllc.com>
1 sibling, 0 replies; 4+ messages in thread
From: Colin Wetherbee @ 2008-03-05 17:56 UTC (permalink / raw)
To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: finding columns that have three or fewer distinct characters
@ 2008-03-05 18:46 Osvaldo Rosario Kussama <osvaldo.kussama@gmail.com>
parent: Jeff Frost <jeff@frostconsultingllc.com>
1 sibling, 1 reply; 4+ messages in thread
From: Osvaldo Rosario Kussama @ 2008-03-05 18:46 UTC (permalink / raw)
To: Jeff Frost <jeff@frostconsultingllc.com>; +Cc: pgsql-sql
Jeff Frost escreveu:
> 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.
>
Try:
SELECT
array_to_string(array(
SELECT DISTINCT lower(substr('aabbcdddef AB',i,1))
FROM generate_series(1,length('aabbcdddef AB'))
s(i))
, '');
Osvaldo
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: finding columns that have three or fewer distinct characters
@ 2008-03-05 20:03 Jeff Frost <jeff@frostconsultingllc.com>
parent: Osvaldo Rosario Kussama <osvaldo.kussama@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Jeff Frost @ 2008-03-05 20:03 UTC (permalink / raw)
To: Osvaldo Rosario Kussama <osvaldo.kussama@gmail.com>; +Cc: pgsql-sql
On Wed, 5 Mar 2008, Osvaldo Rosario Kussama wrote:
> Jeff Frost escreveu:
>> 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.
>>
>
> Try:
> SELECT
> array_to_string(array(
> SELECT DISTINCT lower(substr('aabbcdddef AB',i,1))
> FROM generate_series(1,length('aabbcdddef AB'))
> s(i))
> , '');
>
Osvaldo, that appears to work perfectly! Thank you much!
--
Jeff Frost, Owner <jeff@frostconsultingllc.com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2008-03-05 20:03 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-03-05 17:33 finding columns that have three or fewer distinct characters Jeff Frost <jeff@frostconsultingllc.com>
2008-03-05 17:56 ` Colin Wetherbee <cww@denterprises.org>
2008-03-05 18:46 ` Osvaldo Rosario Kussama <osvaldo.kussama@gmail.com>
2008-03-05 20:03 ` Jeff Frost <jeff@frostconsultingllc.com>
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