How to convert fixed-length accession numbers in all books of koha

Before applying the following commands you must take a backup of the koha database for safety purposes. 

1. Prefix  Zeros and make the length of the accession numbers six-digit 

sudo su

sudo mysql -uroot -p

(Enter MySQL root password)

use koha_library;     

("library" is a koha Instance Name)           

UPDATE items SET barcode = lpad(barcode , 6 , '0');

exit;

This is how all accession numbers will be updated to six-digit length with ''0'' as leading

2. Remove all leading ''0'' from all accession numbers

sudo mysql -uroot -p

(Enter MySQL root password)

use koha_library;                

UPDATE items SET barcode = trim(LEADING '0' FROM barcode);

exit;

This is how all leading '0' will be deleted from the accession numbers 

3. Prefix specific characters to all accession numbers (e.g add Character ''GEN" to all accession numbers)

sudo MySQL -uroot -p

(Enter MySQL root password)

use koha_library;        

UPDATE items SET barcode = CONCAT( "GEN" , barcode);

exit;

This is how the character "GEN" will be prefixed to all accession numbers

4. Delete a prefix character from all accession numbers (e.g.  "GEN" from all accession numbers) 

sudo MySQL -uroot -p

(Enter MySQL root password)

use koha_library;   

UPDATE items SET barcode = trim(LEADING 'GEN' FROM barcode);

exit;

This is how the prefix character "GEN" will be removed from all accession numbers

References:- https://texicon.in/how-to-add-fixed-length-characters-before-the-accession-number-in-all-books-of-koha