updating 1st 5 digit of a column value in sql

Question

I need assistance here for updating 1st 4 digit of a column value

for E.g I have value 999999999 (9 digit) and I need to mask 1st 5 digits with 11111

output should look like 111119999

However, in the same column I have a value called MIS I don't want to touch those value

What query should I use in Oracle?

What I have tried:

I have tried various update query like

UPDATE table SET TAX_ID = replace(TAX_ID, '4', '1111');

Answer 1

Something like this? If value consists of digits only, replace first 5 characters with 1s; else, do nothing.

SQL> with test (col) as
  2    (select '999999999' from dual union all
  3     select '123456789' from dual union all
  4     select 'MIS'       from dual union all
  5     select 'ab12345cc' from dual
  6    )
  7  select col,
  8    case when regexp_like(col, '^\d+$') then 11111 || substr(col, 6)
  9         else col
 10    end result
 11  from test;

COL       RESULT
--------- ---------------------
999999999 111119999
123456789 111116789
MIS       MIS
ab12345cc ab12345cc

SQL>

Answer 2

or this

with t as
 (select '123456789' val from dual union all
  select '987654321' from dual union all
  select 'MIS'       from dual 
)  
select val, regexp_replace(val,'^\d{5}', '11111')
  from t; 

Please, google for oracle regular expressions, and for regexp_replace function. Expressions here:

^ - our search string will be in the beginning

\d - digit

{5} - repeats 5 times