remove a varchar2 string from the middle of table data values

Posted by Michelle Daniel on Stack Overflow See other posts from Stack Overflow or by Michelle Daniel
Published on 2012-10-31T14:45:22Z Indexed on 2012/10/31 17:00 UTC
Read the original article Hit count: 176

Filed under:
|

Data in the file_name field of the generation table should be an assigned number, then _01, _02, or _03, etc. and then .ldf (example 82617_01.pdf).

Somewhere, the program is putting a state name and sometimes a date/time stamp, between the assigned number and the 01, 02, etc. (82617_ALABAMA_01.pdf or 19998_MAINE_07-31-2010_11-05-59_AM.pdf or 5485325_OREGON_01.pdf for example).

We would like to develop an SQL statement to find the bad file names and fix them. In theory it seems rather simple to find file_names that include a varchar2 data type and remove it, but putting the statement together is beyond me.

Any help or suggestions apprecuiated.

Something like ...........

UPDATE GENERATION

SET FILE_NAME (?)

WHERE FILE_NAME (?...LIKE '%STRING%');?

© Stack Overflow or respective owner

Related posts about Oracle

Related posts about oracle11g