How do you easily add 1 to EA00000032?

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?

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s