SQL: How do I increment an alphanumeric field by + 1?
February 4, 2012 7:52 AM Subscribe
I'm a bit of a SQL novice but learning at a decent clip. I'm using SQL (Aqua Data Studio, if that matters to you) to update a couple of fields in an Oracle database table. I'd like to do this across a range of one of the fields. One field is a numeric field (trans_id - a transaction id) that I need to increase by 1 -- I think have that part licked. The next field is alphanumeric (calendar_name - represents the name of a week) and also needs to be incremented by one. The doozy (for me) is the alphanumeric field. Who's the guru?
The numeric portion of each is the same (trans_id 201119 corresponds to calendar_name W201119). Examples:
trans_id (numeric field): 201119 updates to 201120, 201130 updates to 201131, 201148 updates to 201149, etc.
calendar_name (alphanumeric field): W201119 updates to W201120, W201130 updates to W201131, W201148 updates to W201149, etc.
Here's the UPDATE statement I wrote (it's obvious the calendar_name cannot be updated as written but you probably get the idea of what I'm after if you're reading this.):
SET trans_id = (trans_id + 1), calendar_name = (calendar_name + 1)
WHERE calendar_name between 'W201119' and 'W201148';