Skip to Main Content
  • Questions
  • Regular expression to replace characters

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Mr.

Asked: October 19, 2016 - 6:57 am UTC

Last updated: October 19, 2016 - 12:47 pm UTC

Version: 10g

Viewed 1000+ times

You Asked

Can we use regular expression to only replace the string from position 3 to 8. eg: 1234567890 should give 12######90

and Chris said...

Sure.

(..)(.{1,6})(.*)


Matches any two characters. Followed by any 1-6 characters. Then any number of trailing characters.

You can pass this to regexp_replace and use back references to keep the values for the first and third matches.

Oracle doesn't support conditional references though. So if you have strings between 3 and 8 characters long I'm not sure of a way to only show #s up to the length of the string:

with rws as (
  select 1234567890 x from dual union all
  select 1 x from dual union all
  select 12345 x from dual 
)
  select regexp_replace(x, '(..)(.{1,6})(.*)', '\1######\3') from rws;

REGEXP_REPLACE(X,'(..)(.{1,6})(.*)','\1######\3')  
12######90                                         
1                                                  
12######


Though you don't really need to use regular expressions! Standard substr also does the job:

with rws as (
  select 1234567890 x from dual union all
  select 1 x from dual union all
  select 12345 x from dual 
)
  select substr(x, 1, 2) || rpad('#', length(substr(x, 3, 8)), '#') || substr(x, 9)
  from   rws;

SUBSTR(X,1,2)||RPAD('#',LENGTH(SUBSTR(X,3,8)),'#')||SUBSTR(X,9)  
12########90                                                     
1                                                                
12###

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks

Mr Baig, October 19, 2016 - 10:03 am UTC

Thanks
Oh! my bad. I actually wanted to ask how can we keep first and last few characters and replace rest.
Chris Saxon
October 19, 2016 - 12:47 pm UTC

That's what the solutions do? What precisely are you trying to achieve?