A SQL same Data merge problem

I have a watch now, so let's just skip the table and see what I want.
Name Cart
Huangxin bad guys & classmates
Lu Xiuxiu Classmate
Liu Yi Friends & Classmates

I'm going to create a new B-table to save the cart in table A. b, like this.
ID Cartname
1 students
2 Friends
3 bad guys

That is, the data to be re-deposited into the new table. What kind of code do you want to use?

Reply to discussion (solution)

Do not understand the building of the two-meter relationship, nor clear the intention of the landlord

It is recommended to deal with this problem directly with a script, which is basically not possible with a SQL solution (not really recommended)

The program executes the relevant processing logic regularly, then inserts the latest data into the B table, and as for the deduplication operation, the script logic is very well handled and not much to say.

INSERT into Test (cartname) Select cart from UserInfo GROUP by cart;


I've been doing this for a while, but there's A & symbol to go back to the problem.
In fact, a table of the category label field to re-move into the new table

Select Substring_index (Cart, ' & ', 1) from Table A
Union
Select Substring_index (Substring_index (Cart, ' & ', 2), ' & ', -1) from a table
Union
Select Substring_index (Substring_index (Cart, ' & ', 3), ' & ', -1) from a table

Set broken repeats several times.

INSERT into Table_b (cartname)
(
SELECT DISTINCT (cart) from table_a
)

& the part of the symbol segmentation also needs to be heavy, the use of PHP script to deal with the efficiency may be a little higher

Select Substring_index (Cart, ' & ', 1) from Table A
Union
Select Substring_index (Substring_index (Cart, ' & ', 2), ' & ', -1) from a table
Union
Select Substring_index (Substring_index (Cart, ' & ', 3), ' & ', -1) from a table

Set broken repeats several times.

Okay, moderator is the moderator with your code has been successful, but I only executed once it seems that there is no problem thank you moderator

$intosql = "INSERT into table B (cart) Select Substring_index (cartname, ' & ', 1) from table Aunionselect Substring_index ( Substring_index (Cartname, ' & ', 2), ' & ', -1) from table Aunionselect Substring_index (Substring_index (Cartname, ' & ', 3), ' & ', -1) from Table A "; mysql_query ($intosql);

You now have only one ' & ' in the cart, but there may be multiple
Union
Select Substring_index (Substring_index (Cart, ' & ', 3), ' & ', -1) from a table
Should be more than the number of ' & ' so that it is not easy to make mistakes
Notice that the 3 is the number one.

You now have only one ' & ' in the cart, but there may be multiple
Union
Select Substring_index (Substring_index (Cart, ' & ', 3), ' & ', -1) from a table
Should be more than the number of ' & ' so that it is not easy to make mistakes
Notice that the 3 is the number one.

Well, that's just a few & symbols, just a few words.
Union
Select Substring_index (Substring_index (Cartname, ' & ', [color= #0000FF]3), ' & ', -1) from table A[/color]
Yes, and then the blue number is incremented. If it is 1 & symbols, then a select Substring_index (Cart, ' & ', 1) from a can you do that? Then I'm going to use your 3 right now.

No problem, there is a problem (the item is not removed) and then add
Obviously, the more you add, the less efficient.

No problem, there is a problem (the item is not removed) and then add
Obviously, the more you add, the less efficient.

Well thank you moderator

