The output is 123456789A0001357. It is found that when substr (v_ I, 14) truncates the last seven digits for computation, plsql automatically converts the character to an integer and converts the previous lsquo; 0 rsqu
The output is 123456789A0001357. It is found that when substr (v_ I, 14) truncates the last seven digits for computation, pl/SQL automatically converts the character to an integer and converts the previous lsquo; 0 rsqu
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.
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.