Thread: Needs Query

<div class="Section1"><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size:
10.0pt;font-family:Arial;color:navy"> </span></font><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
style="font-size:
10.0pt;font-family:Arial;color:navy"> </span></font><p class="MsoNormal"><font face="Times New Roman" size="3"><span
style="font-size:
12.0pt"> </span></font><p class="MsoNormal"><font face="Times New Roman" size="3"><span style="font-size:
12.0pt"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">In a table, a <b><span style="font-weight:bold">column</span></b> contains</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal" style="margin-left:.5in"><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">Suneelllllllllllllll</span></font><pclass="MsoNormal"
style="margin-left:.5in"><fontface="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">Sudhakarrrrrrrrrr</span></font><pclass="MsoNormal"
style="margin-left:.5in"><fontface="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">Vivekkkkkkkkkk</span></font><pclass="MsoNormal"
style="margin-left:.5in"><fontface="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">Ramanaaaaaaaaa</span></font><pclass="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">But my <b><span style="font-weight:bold">output</span></b> is</span></font><p class="MsoNormal"
style="margin-left:.5in"><fontface="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">Suneel</span></font><pclass="MsoNormal" style="margin-left:.5in"><font
face="Arial"size="2"><span style="font-size:10.0pt;font-family:Arial">Sudhakar</span></font><p class="MsoNormal"
style="margin-left:.5in"><fontface="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">Vivek</span></font><pclass="MsoNormal" style="margin-left:.5in"><font
face="Arial"size="2"><span style="font-size:10.0pt;font-family:Arial">Ramana</span></font><p class="MsoNormal"><font
color="navy"face="Arial" size="2"><span style="font-size: 
10.0pt;font-family:Arial;color:navy"> I want a query to get my output like above……..</span></font><font face="Arial"
size="2"><spanstyle="font-size:10.0pt;font-family:Arial"></span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Please solve my problem ASAP</span></font><p class="MsoNormal"><font color="navy" face="Arial"
size="2"><spanstyle="font-size: 
10.0pt;font-family:Arial;color:navy"> </span></font><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
style="font-size:
10.0pt;font-family:Arial;color:navy"> </span></font><p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
style="font-size:
10.0pt;font-family:Arial;color:navy">Regards</span></font><p class="MsoNormal"><font color="navy" face="Arial"
size="2"><spanstyle="font-size: 
10.0pt;font-family:Arial;color:navy">penchal</span></font><p class="MsoNormal"><font color="navy" face="Arial"
size="2"><spanstyle="font-size: 
10.0pt;font-family:Arial;color:navy"> </span></font></div><table><tr><td bgcolor="#ffffff"><font
color="#000000">============================================================================================================================<br
/><br/><br /> Disclaimer:<br /><br /> This message and the information contained herein is proprietary and confidential
andsubject to the Tech Mahindra policy statement, you may review at <a
href="http://www.techmahindra.com/Disclaimer.html">http://www.techmahindra.com/Disclaimer.html</a>externally and <a
href="http://tim.techmahindra.com/Disclaimer.html">http://tim.techmahindra.com/Disclaimer.html</a>internally within
TechMahindra.<br /><br />
============================================================================================================================<br
/></font></td></tr></table><table><tr><tdbgcolor="#ffffff"><font color="#000000">Information transmitted by this e-mail
isproprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or
theentity to which it is addressed, and may contain information that is privileged, confidential or exempt from
disclosureunder applicable law. If you are not the intended recipient or it appears that this mail has been forwarded
toyou without proper authority, you are notified that any use or dissemination of this information in any manner is
strictlyprohibited. In such cases, please notify us immediately at info.in@infics.com and delete this email from your
records.<br/></font></td></tr></table> 

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

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;