How do I manipulate a VARCHAR field in SQL to simplify a collection of roadmaps in a Bugzilla to Redmine migration?
===

As per [an earlier question][1], I'm migrating from Bugzilla to Redmine and in doing so, I'd like to make use of the road-maps which Redmine offers.

In bugzilla bugs were always logged against the version of software which caused the issue to be raised and although I've now preserved this information in a custom field, I now need to reduce the roadmaps down to something more manageable i.e:

1. Change versions of all bugs which are closed to a simplified equivalent which fits with the roadmap (e.g 0.1234 becomes 0.1 and 2.9876 becomes 2.9). This allows any one road-mapped version to have up to 999 sub-versions which is what we tended to do with Bugzilla already.
1. Change all bugs which are open to a new 'Unplanned' version.
1. Remove all of the current unused version numbers

I imagine this could be achieved with the following steps:

1. Getting all current versions available
2. For each version retrieved, strip off all but the first three characters
3. Check whether a version number for that product already exists.
4. If the version number is new, add it as a new version.
5. Running through every issue in the database and (a) if the issue is closed, assign it to the shortened version number or (b) if the issue is still open, assign it to a 'Unplanned' version.

...but alas, my lack of SQL knowledge is letting me down. Does anyone have any idea how to solve this?


  [1]: http://stackoverflow.com/questions/11511331/how-can-i-migrate-versions-from-bugzilla-into-a-new-custom-source-version-fiel