Hi,
First of all, thanks for viewing and I appreciate your great help.
I am new to 11g and trying explore whether this is doable or not using regexp_replace function.
Here is my requirement.
I have a column, which has 300 character fixed-width text, so i know 1 to 9 characters are SSN, 11 to 20 characters are Date of Birth, 21 to 45 are belongs to an ID (The ID contains upto 25 characters, Some time it will contain only 10 characters, some times 20. It depends, So if it contains only 10, the remaining 15 will be spaces ) etc..
To retrieve ID, I use TRIM(SUBSTR(col, 21, 25)) ID from Table
The requirement is, we run some validations against the SSN in other databases and update the ID in this table if necessary.
In the older versions, I
can achieve the result in the following way.
UPDATE table set col = substr(col, 1, 20) || AddSpacesIfLessthan25Characters(newID) || substr(col, 46) where SSN=123456789
In 11g, how can i achieve the above result, Is there any simpler way? Does regexp_replace function work for me?
Thanks