We have a primary key in the Oracle table which is of the format EA00000032 and I need to increase it by one every time I insert a new row.

I could:

• remove the EA
• convert 00000032 into an integer (32)
• Add 1 (33)
• convert to string
• Add 6 zeros back

And this is all well and good but when you roll from 99 to 100 or 999 to 1000 you have to compute the length of the string and add the appropriate number of zeros to make it back up to the 8 characters

• WAY too many steps

So here’s a much more efficient way of doing it avoiding the need to compute the length of 32, 99 or whatever

• Start with a billion (100000000)
• add everything to the right of the EA +1 –> 00000099 +1 (100000100)
• EA +the back 8 characters of the new number

sEXP_AUTH_ID=”EA00000099″
sEXP_AUTH_ID=”EA”+Right(CStr(100000000+Clng(StrRight(sEXP_AUTH_ID, “EA”))+1), 8)

Productive or Lazy?