M
I'll show you a solution example.In this case the right thing would be to have two columns of the type INT to keep the goals in favor and against. If for any reason they want to represent themselves like this: 40:20That can be done at the exit. In BD it is advisable to keep each data as a proper entity and with the type of data itself. Do not ever make the error of saving a numerical or date data as VARCHAR. You will have problems in the long run to keep the data and to present them.Suppose they ask you for total goals in favor separately, or total goals in favor of global, or total goals in 2014, or total goals against when you played with team X. It will be hard to calculate that data if you have a column VARCHAR Two data in one. It's more, it may even occupy more space in the DB than having two. INT...Then to solve this, we would have to:create two new columns of the type INT. In the example I called them a_favor and en_contra.do a query that gets on the one hand the values that are before : and on the other hand those who are after :and assign those values to the columns quoted above.This consultation would be something like this:UPDATE validar_goles SET
a_favor= SUBSTRING_INDEX( goals , ':', 1 ),
en_contra= SUBSTRING_INDEX(SUBSTRING_INDEX( goals , ':', 2 ),':',-1) ;
Don't do this.This doesn't have to be applied anymore, but I put it here if you ever needed it for another case that I do.If you want to validate the column data goles to a data Really. DECIMAL, could be made creating a new column, which I called decimal_goalsunite the table with itself by replacing the two column points goals for one puntoand inserting that value into the new decimal column. What he will do REPLACE will create a convertible (parable) data to DECIMAL that will allow you to register the values correctly in your new column of the type DECIMAL.The consultation would be: UPDATE validar_goles AS t2
LEFT JOIN validar_goles AS t1 ON (t1.ID = t2.ID)
SET t1.decimal_goals = REPLACE(t2.goals,':','.');
NOTES:These queries would update the entire table with a single execution, they should not be executed for each row.Therefore, it always agrees before making these changes make a backup of the table.It is possible if the current column GOALS of your board VARCHAR some values are wrongly entered. This is another of the added problems when we use data types that are not the right ones. There may be values with blank spaces, with characters whatever are not numbers or the two points. However, the query could fail in those values and you must correct them. For the issue of blank spaces you can clean the column with suitable functions as https://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_trim .DemoHere is a proof of concept: http://rextester.com/JEM42303 CREATE TABLE IF NOT EXISTS validar_goles
(
ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
goals VARCHAR(10),
decimal_goals DECIMAL(10,2),
a_favor INT,
en_contra INT
)ENGINE=INNODB;
INSERT INTO validar_goles (goals)
VALUES
('50:14'),
('41:22'),
('30:26')
;
SELECT * FROM validar_goles;
UPDATE validar_goles SET
a_favor= SUBSTRING_INDEX( goals , ':', 1 ),
en_contra= SUBSTRING_INDEX(SUBSTRING_INDEX( goals , ':', 2 ),':',-1) ;
UPDATE validar_goles AS t2
LEFT JOIN validar_goles AS t1 ON (t1.ID = t2.ID)
SET t1.decimal_goals = REPLACE(t2.goals,':','.');
SELECT * FROM validar_goles;
Outcome:This is the table before the changes:ID goals decimal_goals a_favor en_contra
1 50:14 NULL NULL NULL
2 41:22 NULL NULL NULL
3 30:26 NULL NULL NULL
And after the changes:ID goals decimal_goals a_favor en_contra
1 50:14 50,14 50 14
2 41:22 41,22 41 22
3 30:26 30,26 30 26
