Home » SQL & PL/SQL » SQL & PL/SQL » help in string replace (oracle 10g)
help in string replace [message #420590] Mon, 31 August 2009 12:24 Go to next message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member

Hi

I have a string as below

'Internet & Multimedia Services (KT Whole)'

I want to print my output for as below

*Int_Mul_Ser_KT_Who*

I am trying all stuff but failing.I think regexp_replace can work but i am unable to do it.

Please help me out



[Updated on: Mon, 31 August 2009 12:29]

Report message to a moderator

Re: help in string replace [message #420594 is a reply to message #420590] Mon, 31 August 2009 12:53 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

SELECT '*Int_Mul_Ser_KT_Who*' FROM DUAL;

[Updated on: Mon, 31 August 2009 12:54]

Report message to a moderator

Re: help in string replace [message #420609 is a reply to message #420590] Mon, 31 August 2009 20:42 Go to previous messageGo to next message
sherrycao
Messages: 1
Registered: August 2009
Junior Member
Maybe you can use regular expression
Re: help in string replace [message #420621 is a reply to message #420609] Tue, 01 September 2009 00:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
...Or maybe not.
Who knows what the specifications of the transformation?

Regards
Michel
Re: help in string replace [message #420957 is a reply to message #420590] Thu, 03 September 2009 08:41 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I win today's prize for unreadable nested regexp statements:
with src as ( select 'Internet + Multimedia Services (KT Whole)' col_1 from dual)
select regexp_replace(regexp_replace(regexp_replace(col_1,'([^A-Za-z ])',''),'([A-Za-z]{3})[^ ]*','\1'),'([ ]+)','_')
from src;
Re: help in string replace [message #420976 is a reply to message #420957] Thu, 03 September 2009 09:17 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
JRowbottom wrote on Thu, 03 September 2009 15:41
I win today's prize for unreadable nested regexp statements:
with src as ( select 'Internet + Multimedia Services (KT Whole)' col_1 from dual)
select regexp_replace(regexp_replace(regexp_replace(col_1,'([^A-Za-z ])',''),'([A-Za-z]{3})[^ ]*','\1'),'([ ]+)','_')
from src;


hm...
SQL> with src as ( select 'Internet + Multimedia Services (KT Whole)' col_1 from dual)
  2  select regexp_replace(regexp_replace(regexp_replace(col_1,'([^A-Za-z ])',''),'([A-Za-z]{3})[^ ]*','\1'),'([ ]+)','_')
  3  from src;

THE_ANSWER
----------
        42


I think you're on to something here, JRow..
Re: help in string replace [message #421015 is a reply to message #420590] Thu, 03 September 2009 12:11 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
how about good old decode


decode(co_name,'Internet + Multimedia Services (KT Whole)','*Int_Mul_Ser_KT_Who*',co_name)
Re: help in string replace [message #421074 is a reply to message #420976] Fri, 04 September 2009 02:28 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Oh ok, I'll provide some explanations....

The innermost regexp_replace gets rid of all the non-letter characters out of the string:
SQL> with src as ( select 'Internet + Multimedia Services (KT Whole)' col_1 from dual)
  2  select regexp_replace(col_1,'([^A-Za-z ])','')
  3  from src;

REGEXP_REPLACE(COL_1,'([^A-ZA-Z])','')
--------------------------------------
Internet  Multimedia Services KT Whole


The second Regexp_replace takes the output from the first, and looks for strings matching the pattern '3 letters followed by any number of characters that aren't spaces. It replaces each of these strings with the '3 letters' part of the string.
SQL> with src as ( select 'Internet  Multimedia Services KT Whole' col_1 from dual)
  2  select regexp_replace(col_1,'([A-Za-z]{3})[^ ]*','\1')
  3  from src;

REGEXP_REPLACE(COL_
-------------------
Int  Mul Ser KT Who


The final regexp_replace takes this output, and replaces 1 or more contiguous spaces with a single underscore character:
SQL> with src as ( select 'Int  Mul Ser KT Who' col_1 from dual)
  2  select regexp_replace(col_1,'([ ]+)','_')
  3  from src;

REGEXP_REPLACE(COL
------------------
Int_Mul_Ser_KT_Who
Re: help in string replace [message #421201 is a reply to message #420590] Sat, 05 September 2009 12:13 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Another option using regex:

SQL> select a1
  2     , rtrim(regexp_replace(a1,'([^[:alpha:]]*)([[:alpha:]]{1,3})([^ ]*)', '\2_'), '_') str
  3  from full_name;

A1                                            STR
--------------------------------------------- -------------------------
Internet & Multimedia Services (KT Whole)     Int_Mul_Ser_KT_Who
ABCD + XYZ Corp (PQ Half)                     ABC_XYZ_Cor_PQ_Hal
A B C D E F G                                 A_B_C_D_E_F_G
Re: help in string replace [message #421202 is a reply to message #421201] Sat, 05 September 2009 13:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
./fa/2115/0/ A nice one indeed!

Regards
Michel
Re: help in string replace [message #421246 is a reply to message #420590] Sun, 06 September 2009 20:07 Go to previous message
swas_fly
Messages: 220
Registered: March 2008
Location: Bhubaneswar
Senior Member
Hi All

Many Thanks for the explanation.

Best Regards
Swastik
Previous Topic: Trace files location and user commits question (2 merged)
Next Topic: Problem with annonymous block
Goto Forum:
  


Current Time: Sun Dec 04 22:45:32 CST 2016

Total time taken to generate the page: 0.21684 seconds