Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 3C4362E0061 for ; Wed, 5 Mar 2008 14:43:10 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 54441-05 for ; Wed, 5 Mar 2008 14:42:58 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from ag-out-0708.google.com (ag-out-0708.google.com [72.14.246.241]) by postgresql.org (Postfix) with ESMTP id 0704A2E2F08 for ; Wed, 5 Mar 2008 14:42:57 -0400 (AST) Received: by ag-out-0708.google.com with SMTP id 23so2458216agd.5 for ; Wed, 05 Mar 2008 10:42:57 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding:from; bh=1xHpo1hLe0Fi4y+rz0jG3gP3YcRlpAoMj8R4aGmsuQs=; b=erbsc2I+07FNmlGwm9tYS4+b/g3q1X/Fho2k8hMF7JrC4ANVDuOYXWbFNeAg9IhdMgfP3noQTzAuRJ0KVZIvC/qKtzpmu6Svman3yBBYrOU+lKA002IZIsKtSzfa21Qaa13+c9Vejf+lukhTOmQrQs+lznuraXPzu6SXmF3EPRU= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding:from; b=V9xXLD5DACaqtjAH+Qkrjo2szWi/Z1iBUsyjrJsh5N4F6f622Vh8VlT/JXxLHf4/UHgJYTqxHnVNMW549vX3sZN9LgIaMKmdHeS+DFa72Gyg0BIT9yZVKkIGgsPDj3IItSPS3Jb90KoiJKvhFoBkw56NKWqp3mULeU7Nsv7Cpjw= Received: by 10.100.107.7 with SMTP id f7mr7474324anc.74.1204742576105; Wed, 05 Mar 2008 10:42:56 -0800 (PST) Received: from ?10.2.200.151? ( [189.25.45.65]) by mx.google.com with ESMTPS id 5sm1973949agc.14.2008.03.05.10.42.51 (version=TLSv1/SSLv3 cipher=RC4-MD5); Wed, 05 Mar 2008 10:42:53 -0800 (PST) Message-ID: <47CEEAA3.3040004@gmail.com> Date: Wed, 05 Mar 2008 15:46:59 -0300 User-Agent: Thunderbird 1.5.0.12 (X11/20070530) MIME-Version: 1.0 To: Jeff Frost CC: pgsql-sql@postgresql.org Subject: Re: finding columns that have three or fewer distinct characters References: In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit From: Osvaldo Rosario Kussama X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200803/19 X-Sequence-Number: 30347 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