How can I implement a MySQL string that separates the content of a field and writes it to other fields? Use SUBSTRING_INDEX and CONCAT to implement this function.
SUBSTRING_INDEX and CONCAT can be used to split and splice MYSQL strings. The following describes the usage of the MYSQL string processing statement for your reference.
Split the content of a field and write it to other fields:
The field content is relatively regular, so select the SUBSTRING_INDEX function and use the CONCAT string link function to effectively cooperate and achieve the final effect.
The telephone number is as follows: 010-88888882-5612. The telephone number is divided into the switchboard and extension number:
SELECT SUBSTRING_INDEX (phone, '-', 2) AS PNumber, SUBSTRING_INDEX (phone, '-',-1) AS Ext, phone FROM tb_user WHERE ucid = 271338; + -------------- + ------ + ----------------- + | PNumber | Ext | phone | + ---------------- + ------ + ------------------- + | 010-88888882 | 5612 | 010-88888882-5612 | + -------------- + ------ + hour ------------------- +
Change extension number to 1234:
UPDATE tb_user SET phone = CPNCAT (SUBSTRING_INDEX (phone, '-', 2), '-', '123') WHERE ucid = 1234;