Pattern Match in Oracle

[jamesgao]

Hi All,

I need to write a function to convert a varchar2 value from a pattern of two alpha charatcer followed by two numeric character, then followed by a alpha character, then anthing after that to a three numeric charater after the first two characters. Such as "ab99c91" to "ab099c19". Please help.

Thanks,

--James

 

[sfvb]

This should work 'til you find something more efficient:

UPDATE my_tbl
SET    my_col = substr(my_col,1,2)||'0'||substr(my_col,3)
WHERE  length(my_col) = 7
  AND  substr(my_col,1,1) NOT between '0' and '9'
  AND  substr(my_col,2,1) NOT between '0' and '9'
  AND  substr(my_col,3,1)     between '0' and '9'
  AND  substr(my_col,4,1)     between '0' and '9'
  AND  substr(my_col,5,1) NOT between '0' and '9'
  AND  substr(my_col,6,1)     between '0' and '9'
  AND  substr(my_col,7,1)     between '0' and '9'

 

[sem]

substr(field,1,2)||'0'||substr(field,3)