Number processing in Oracle strings

I recently started writing the stored procedure. There is a requirement that the database table has a field (20-bit serial number, which may contain characters, and the last 7 digits are numbers ), this field must be calculated based on the quantity.

1. Declare
2. I integer:=123; -- Increment
3. V_ I varchar2 (25):='123456789a0000001234'; -- Serial number
4. Begin
5. Dbms_output.enable; -- set the output
6. V_ I:=Substr(V_ I, 1, 13) | (substr (v_ I, 14) + I-1 );
7. Dbms_output.put_line (v_ I );

The output is 123456789A0001357;

When the last seven digits of substr (v_ I, 14) are intercepted for calculation, pl/SQL automatically converts the character to an integer and removes the previous '0.

Studying Oracle internal functions, we found that there is a function that can convert the format to the string to_char (exp, 'reg ') exp to the number to be converted, and reg to the conversion format

ModifyV_ I: = substr (v_ I, 0000000) | to_char (substr (v_ I, 14) + I, '123 ');-- Reg is 7 '0'. If you want to keep the digits correct, add '0' in front'

Output: 123456789A000 0001357,

Multiple spaces are found in the middle. I don't know why. Continue to use oracle internal function trim (exp) to remove space at both ends of exp

To:V_ I: = substr (v_ I, 0000000) | trim (to_char (substr (v_ I, 14) + I, '123 '));

The output result is 123456789A0000001357 ,.This is what we want

To sum up, you should be familiar with oracle internal functions, but there may be a BUG above. If the last seven digits generate carry, the above processing is incorrect, all characters must be intercepted by the business.