How to massively scatter information from one table column to several others?

Question

PHP

Hello!
There is a DB, in it the table, we will assume, users. It has several columns and rows.
Columns: id , login , info , email , phone , skype .
Lines:
id : 1 , login : qwe , info : Soap|[email protected]||Phone|79211234567||Skype|qwe , email : , phone : , skype : .
id : 2 , login : asd , info :Soap|[email protected]||Skype|asd , email : , phone : , skype : .
id : 3 , login : zxc , info : Soap|[email protected]||Phone|79217654321 , email : , phone : , skype : .
Etc.
Those. initially, all information about all users was hammered into one single column called info. When it finally dawned on me that this was not convenient, I added more columns and faced the problem: how to accurately transfer this data from one column to the rest? Separation of information is done by means of vertical sticks. One vertical stick separates the title and some content, two sticks - different information. At the beginning and at the end, as you can see, there are no sticks; they are separating. Another problem is that not everyone has complete information (in the example, only id1 has all the information, id2 and id3 are missing something).
I need to sort the information in such a way that it looks like this:
id : 1 , login : qwe , info: , email : [email protected] , phone : [phone] , skype : qwe .
id : 2 , login : asd , info : , email : [email protected] , phone : , skype : asd .
id : 3 , login : zxc , info : , email : [email protected] , phone : [phone] ,Skype : .
As unnecessary , the info column can be removed altogether.
Of course, this cannot be done manually, since the database is quite large.
Please suggest options. There is a SQL query, but I just did not find it, and you will call it to me - I will be very grateful. If you have to decide by third-party methods (up to importing into Excel), well, what can you do - we import.
Sincerely,
Alexander.

Answer 1

UPDATE `table` SET 
    `email` = IF(LOCATE('Мыло|', `info`) = 0, NULL, 
                SUBSTRING_INDEX(SUBSTRING_INDEX(`info`, 'Мыло|', -1), '|', 1)),
    `phone` = IF(LOCATE('Телефон|', `info`) = 0, NULL,
                SUBSTRING_INDEX(SUBSTRING_INDEX(`info`, 'Телефон|', -1), '|', 1)),
    `skype` = IF(LOCATE('Skype|', `info`) = 0, NULL, 
                SUBSTRING_INDEX(SUBSTRING_INDEX(`info`, 'Skype|', -1), '|', 1));

Answer 2

start transaction
select id, info from users for update; in PHP
By any method on the php side, you parse this format into the necessary parts
update users set ... where id=?
commit;
It is possible in pieces of a hundred records, if everything cannot be locked during the update.

Answer 3

You can sketch a simple script in php (or any other convenient language). I selected a record, parsed the info field into the necessary data and wrote it back.