Padding zeros to the end of a string and incrementing them

I have a string that is up to 10 characters long (EX: 2021-00445) when it’s first created in SQL.

I would like to pad it with a trailing dash(-) and three zeros (-000). I also want to have them incremented in the interval of 1, so if it has been incremented once, then the new value of the trailing zeros would be ‘-001’. Or if it has been incremented 23 times, the value is ‘-023’. (EX: 2021-00445-000).

How would this be implemented in Toad SQL?

Answer

You could create a sequence and use something like:

your_col := your_col||'-'||LPAD(your_seq.NEXTVAL,3,'0'); /* plsql style*/

UPDATE your_table
   SET your_col = your_col||'-'||LPAD(your_seq.NEXTVAL,3,'0')
 WHERE <your_conditions>; 

however this is pretty generic since you haven’t mentioned when or on what condition the update will happen.