Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id C78739FBBB4 for ; Tue, 8 May 2007 11:00:32 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 83557-06 for ; Tue, 8 May 2007 11:00:27 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from nz-out-0506.google.com (nz-out-0506.google.com [64.233.162.236]) by postgresql.org (Postfix) with ESMTP id 327189FBBAF for ; Tue, 8 May 2007 11:00:27 -0300 (ADT) Received: by nz-out-0506.google.com with SMTP id s1so2083163nze for ; Tue, 08 May 2007 07:00:25 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=eIRAaOThhK5js4SbQmG9W0VkzU///fNU2tT5nkYgIyC5E9Gig1NvFsFtaKv1kEFo7VWh8BGT1m/GpDKWoOGx69Xym/G0FQlCLZE+ZNqu+dbEyZSvGylMCt2re4YH0tK3QCz8KSacCPzZr1LfVNva7yCrNufa/ZpnbVdI8XDfDYw= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=Jt/BC9fHxCk9Dq5Dpf/0MZ5/Uc1CISZuOK6/e0qWyQmm0tPZzwXZ44H7qkOyyshcEpzZSLPZpT7qGFpt9FeynrReNZYTRLnhvzuBJEObUJku8SHwVUJIc5134on4B0855LdD9e/il8+9LsDylKygOFGzNm5PlAZrmgq4Iy2aEg0= Received: by 10.64.249.18 with SMTP id w18mr12259782qbh.1178632825545; Tue, 08 May 2007 07:00:25 -0700 (PDT) Received: by 10.64.142.7 with HTTP; Tue, 8 May 2007 07:00:25 -0700 (PDT) Message-ID: <1aa8c50f0705080700i670b5c6dpce0076ae02fbbb13@mail.gmail.com> Date: Tue, 8 May 2007 09:00:25 -0500 From: "Reuven Abliyev" To: "Penchalaiah P." Subject: Re: Needs Query Cc: pgsql-sql@postgresql.org In-Reply-To: <20070508125854.GE19006@a-kretschmer.de> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_66427_14928068.1178632825479" References: <20070508125854.GE19006@a-kretschmer.de> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200705/61 X-Sequence-Number: 28342 ------=_Part_66427_14928068.1178632825479 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline or if you want it in the one query: >create table _t(f varchar(50)); select f, case when substr(f, length(f), 1)= substr(f, length(f)-1, 1) then regexp_replace(f, substr(f, length(f), 1)||'+$', substr(f, length(f), 1)) else f end from _t where length(f)>1; On 5/8/07, A. Kretschmer wrote: > > am Tue, dem 08.05.2007, um 17:48:47 +0530 mailte Penchalaiah P. > folgendes: > > > > > > > > > > > > > > > > > > In a table, a column contains > > > > > > > > Suneelllllllllllllll > > > > Sudhakarrrrrrrrrr > > > > Vivekkkkkkkkkk > > > > Ramanaaaaaaaaa > > > > > > > > > > > > But my output is > > > > Suneel > > > > Sudhakar > > > > Vivek > > > > Ramana > > > > I want a query to get my output like above??.. > > write a stored proc. within this proc: (oversimplified) > > - calculate the length of the string with length() -> length > - extract substring(string, length, length) -> last_char > - regexp_replace(string, last_char||'*$', last_char) > > > Andreas > -- > Andreas Kretschmer > Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) > GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > ------=_Part_66427_14928068.1178632825479 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline or if you want it in the one query:

>create table _t(f varchar(50));

select f, case when substr(f, length(f), 1)= substr(f, length(f)-1, 1) then regexp_replace(f, substr(f, length(f), 1)||'+$', substr(f, length(f), 1)) else f end from _t where length(f)>1;




On 5/8/07, A. Kretschmer <andreas.kretschmer@schollglas.com > wrote:
am  Tue, dem 08.05.2007, um 17:48:47 +0530 mailte Penchalaiah P. folgendes:
>
>
>
>
>
>
>
>
> In a table, a column contains
>
>
>
> Suneelllllllllllllll
>
> Sudhakarrrrrrrrrr
>
> Vivekkkkkkkkkk
>
> Ramanaaaaaaaaa
>
>
>
>
>
> But my output is
>
> Suneel
>
> Sudhakar
>
> Vivek
>
> Ramana
>
>  I want a query to get my output like above??..

write a stored proc. within this proc: (oversimplified)

- calculate the length of the string with length() -> length
- extract substring(string, length, length) -> last_char
- regexp_replace(string, last_char||'*$', last_char)


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

------=_Part_66427_14928068.1178632825479--