Split data in column based on value of in another column

I have a table full of Artist and Track Titles that is imported from two different places.

Some are correct with Artist and Title in different columns but then there are other rows where everything is in the Title column separated with ‘-‘ and the Artist column is NULL.

Changing it at the source is not an option because both data formats come in the same log file.

I think I need get all of the rows with a NULL value in the Artist column and then use STRING_SPLIT to split the Title column. My database is SQL server 2019.

I have tried a modifying a bunch of different examples on the internet but nothing comes close to working.

Thank you for your help!

Answer

You can do this as a select query:

select t.*,
       (case when artist is null and title like '%-%'
             then left(title, charindex('-', title) - 1)
             else artist
        end) as imputed_artist,
       (case when artist is null and title like '%-%'
             then stuff(title, 1, charindex('-', title) + 1, '')
             else title
        end) as imputed_title
from t;

Similar logic can be applied in an update.