MySQL String processing example

In the project, to determine a certain number of fields in the table, if a value is replaced, using a few typical string operations, the record Note implementation scheme is as follows:

Note: If the substitution string is unique, you can use replace, where the concat stitching is used.

DELIMITER//DROP PROCEDURE IF EXISTSp_modify_year;CREATE PROCEDUREp_modify_year ()BEGIN    DECLARETmpidint(8); DECLARETmppciddvarchar( -); DECLARETmpprocodevarchar( -); DECLAREDoneINT DEFAULTFALSE; DECLARECurCURSOR  for SELECTProduct.id fromProductWHEREProstatus=0; DECLARE CONTINUEHANDLER for  notFOUNDSETDone=TRUE; OPENcur; Myloop:loopFETCHCur intoTmpid; IFDone ThenLEAVE Myloop; END IF; SELECT SUBSTRING(Product.pcidd,2,2), Product.procode intoTmppcidd, Tmpprocode fromProductWHEREId=Tmpid;
　　　　　The 2nd and 3 bits in//pcidd and Procode are replaced by AU, where procode may be empty.          IFTmppcidd= ' -'  Then                UPDATEupe_productSetPcidd=CONCAT (SUBSTRING(Pcidd,1,1),'AU',SUBSTRING(Pcidd,4, LENGTH (Pcidd)+1))             WHEREId=Tmpid; IF!ISNULL(Tmpprocode) Then                    UPDATEProductSetProcode=CONCAT (SUBSTRING(Procode,1,1),'AU',SUBSTRING(Procode,4, LENGTH (Procode)+1))WHEREId=Tmpid; END IF; END IF; SETTmpprocode= NULL; SETTmppcidd= NULL; SETDone=FALSE; ENDLOOP; CLOSEcur; END//

MySQL String processing example