I've done this kind of thing in SQL. The trick is matching what you need using just SQL, since its string matching is somewhat verbose. I've also done this using SQL in PHP--just looping through a query result set, making the changes using PHP's RegEx or basic string substitution, and then updating the entries.
To get at the field data in EE1, you'll want to do a query like:
select entry_id, field_id_10 from exp_weblog_data
where channel_id = 1 and field_id_10 like '%src="{filedir_2}%.jpg%'
You can find the field_ids for the fields you need, via this:
select * from exp_weblog_fields
Or, go to edit a field in the EE control panel, and grab the field ID from the URL.
updated: here's an example query for parsing out the file info. Note that there's a +4 in the equation because we look-up the position of .jpg (which is 4 characters). If you end-up doing this totally in SQL, then I'd run separate queries for .jpg, .gif, .png as needed.
select entry_id, field_id_10,
substr(field_id_10,
locate('{filedir_2}', field_id_10),
locate('.jpg', field_id_10, locate('{filedir_2}', field_id_10))
- locate('{filedir_2}', field_id_10) + 4
)
from exp_weblog_data
where channel_id = 1 and field_id_10 like '%src="{filedir_2}%.jpg%'
So, ultimately, you'd want to create a new field for the new image value (say it's field_id_11), and do something like this:
update exp_weblog_data set field_id_11 =
substr(field_id_10,
locate('{filedir_2}', field_id_10),
locate('.jpg', field_id_10, locate('{filedir_2}', field_id_10))
- locate('{filedir_2}', field_id_10) + 4
)
where channel_id = 1 and field_id_10 like '%src="{filedir_2}%.jpg%'