Can I split text from one column into to two existing columns in My SQL

Question

SELECT `Appointment`,
   SUBSTRING_INDEX(SUBSTRING_INDEX(`Appointment`, ' ', 1), ' ', -1) AS Date,
   SUBSTRING_INDEX(SUBSTRING_INDEX(`Appointment`, ' ', 2), ' ', -1) AS Time
   FROM go_applicants

which separates the date and time but creates 2 new columns, is it possible to insert/update this split into existing columns in the table.

This would allow me then to run this as an event instead and continually update the table.

score 2 · Accepted Answer · answered Aug 16 '21 at 16:25

Store in existing columns:

UPDATE go_applicants
SET cDate = SUBSTRING_INDEX(`Appointment`, ' ', 1),
    cTime = SUBSTRING_INDEX(SUBSTRING_INDEX(`Appointment`, ' ', 2), ' ', -1);

Make generated columns:

ALTER TABLE go_applicants
    ADD COLUMN cDate DATE AS (SUBSTRING_INDEX(`Appointment`, ' ', 1)),
    ADD COLUMN cTime TIME AS (SUBSTRING_INDEX(SUBSTRING_INDEX(`Appointment`, ' ', 2), ' ', -1));

PS. If you store date and time of some datetime value in 2 separate static columns - this is "bad practice" in most cases.

Can I split text from one column into to two existing columns in My SQL

1 Answers1