Home » SQL & PL/SQL » SQL & PL/SQL » Replace
Replace [message #195317] Wed, 27 September 2006 22:19 Go to next message
sanjit
Messages: 65
Registered: November 2001
Member
Replace and transalte
I have data like:

SOB	segments				
1	700C.780600.000000.B0000.UK.200C.000000	
84	200C.680600.000000.B0000.AU.700C.000000	


I want get the corresponding account like
SOB	segments				
1	700C.780600.000000.B0000.[B]000[/B].200C.000000	
84	200C.680600.000000.B0000.[B]000[/B].700C.000000


ie replacing fifth segment with default value like '000'

I have used TRANSLATE FUNCTION BUT COULD NOT ACHIVE THIS...IS THERE IS ANY WAY TO
select b.concatenated_segments ORIGINAL,
TRANSLATE(b.concatenated_segments ,'AU','000') NEW
FROM TABLE
...WHERE ...CONDITION


Re: Replace [message #195320 is a reply to message #195317] Wed, 27 September 2006 23:10 Go to previous messageGo to next message
hobbes
Messages: 173
Registered: January 2006
Senior Member
TRANSLATE provides one-to-one character substitution. For string substitution use REPLACE.

Use INSTR/SUBSTR to extract the segment to be replaced.
Re: Replace [message #195378 is a reply to message #195317] Thu, 28 September 2006 05:39 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
If you are on 10g, you could use:

SQL> select * from test_reg;

    SOB SEGMENTS
------- ---------------------------------------------
      1	700C.780600.000000.B0000.UK.200C.000000	
     84	200C.680600.000000.B0000.AU.700C.000000	


SQL> select sob,
  2	regexp_replace(segments, '\.([^.]*)\.([^.]*\.[^.]*)$','.000.\2') "SEGMENTS"
  3  from test_reg;

    SOB SEGMENTS
------- ---------------------------------------------
      1	700C.780600.000000.B0000.000.200C.000000	
     84	200C.680600.000000.B0000.000.700C.000000	


Previous Topic: join strings
Next Topic: Whats wrong with query.
Goto Forum:
  


Current Time: Fri Dec 02 16:30:09 CST 2016

Total time taken to generate the page: 0.36236 seconds