Can we use regular expression to only replace the string from position 3 to 8. eg: 1234567890 should give 12######90
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###