I have a time shift column in my tblCalendar table called TimeShift and need to fill two other columns in the same table, both columns have Null values and their names are StartTime and EndTime.

This serves as an example:

I need to fill the StartTime and EndTime columns by doing a split on the first column.

I figured out how to use SQL functions to split the first column on '-' char.

But I didn't find a way to fill the Null columns with these extracted split data.

The query I found is:

SELECT
    LEFT(TimeShift, CHARINDEX('-', TimeShift) - 2) AS StartTime, 
    RIGHT(TimeShift, LEN(TimeShift) - CHARINDEX('-', TimeShift) -1) AS EndTime 
FROM
    tblCalendar

which will return the required data, but my question is: how to fill these values into the StartTime and EndTime columns in my original table?

Many thanks

CodePudding user response：

Well, obviously, you'll need an UPDATE statement to update an existing table - and you can basically use the two expressions from your SELECT query.

Try something like this:

UPDATE dbo.tblCalendar
SET StartTime = LEFT(TimeShift, CHARINDEX('-', TimeShift) - 2), 
    EndTime = RIGHT(TimeShift, LEN(TimeShift) - CHARINDEX('-', TimeShift) -1) 
WHERE
    StartTime IS NULL AND EndTime IS NULL;

Depending on the datatypes of your columns, this might not work right away - maybe you also need to do some conversions from e.g. VARCHAR to TIME ....