OREPLACE

General
General covers Articles, Reference documentation, FAQs, Downloads and Blogs that do not belong to a specific subject area. General-purpose Articles about everything and anything
Enthusiast

OREPLACE

Hi,

 

  I have a scenario where i need to insert a column from another table. on Insert, i need to change values for a particular positions alone

  

  eg: In my source the field is 

 

   8274625624276823
   8274628724276828

   8274628694276821

 

    The target column should be populated from above field except that position 4 and 5 should be 62 instead of 46

 

   8276225624276823
   8276228724276828

   8276228694276821

   

  I am trying to find an option if we can do anything in Oreplace for this


Accepted Solutions
Junior Contributor

Re: OREPLACE

You can't use oReplace for that.

 

To replace any characters in position 4&5:

Substr(src, 1,3) || '62'|| Substr(src, 6)
RegExp_Replace(src, '(...)..(.*)', '\162\2')

 To replace the exact match '46' in position 4&5:

RegExp_Replace(src, '(...)46(.*)', '\162\2')

 

 

1 ACCEPTED SOLUTION
4 REPLIES
Junior Contributor

Re: OREPLACE

You can't use oReplace for that.

 

To replace any characters in position 4&5:

Substr(src, 1,3) || '62'|| Substr(src, 6)
RegExp_Replace(src, '(...)..(.*)', '\162\2')

 To replace the exact match '46' in position 4&5:

RegExp_Replace(src, '(...)46(.*)', '\162\2')

 

 

Enthusiast

Re: OREPLACE

Thankyou dnoeth ,

I was doing it by the first option u told on substr

the regular expression was something new for me, it  worked out

 

RegExp_Replace(src, '(...)46(.*)', '\162\2')

In this, what does the  '\1' and '\2' signify 

Junior Contributor

Re: OREPLACE

Those reference the 1st and 2nd match group, i.e. the characters between the ( ):

(...) = any three characters = \1
46 = exactly those two characters
(.*) = any number of characters = \2
Enthusiast

Re: OREPLACE

ok Thank you for the clarification