Update column by parsing another column with T-SQL

Question

I have this table and data in my database:

tblPhotos

ID | Title | Description
------------------------------
1 | NULL | Some data - Title 123 - Subtitle - Photographer: John Doe
2 | NULL | Some data - Photographer: Jane Doe
3 | NULL | Some data - Title 345 - Photographer: John Doe Jr

The data in the Description column comes in two formats:

{GARBAGE DATA} - {TITLE DATA} - Photographer: ..., or
{GARBAGE DATA} - Photographer: ...

Basically, I have thousands of rows, where the Title column is empty. I need to somehow pull it out of the Description column.

The Title exists between the first dash and the and the last dash of the Description column. Here's what the data should look like:

tblPhotos

ID | Title                | Description
-------------------------------------------------------------------------------------
 1 | Title 123 - Subtitle | Some data - Title 123 - Subtitle - Photographer: John Doe
 2 | NULL                 | Some data - Photographer: Jane Doe
 3 | Title 345            | Some data - Title 345 - Photographer: John Doe Jr

How can I make a script that will parse a column, and update another column with that parsed data?

Answer 1

The location of the first dash is easy: CHARINDEX('-', Description). The location of the second dash is easy, too, but you want the location of the last dash. That's a bit harder because you have to reverse the string to get it: LEN(Description) - CHARINDEX('-', REVERSE(Description)). To get the stuff in between you need to find the length by subtracting the two positions:

SUBSTRING(Description, CHARINDEX('-', Description) + 1, LEN(Description) - CHARINDEX('-', REVERSE(Description))) - CHARINDEX('-', Description)

Putting it all together you get:

UPDATE tblPhotos
SET Title = RTRIM(LTRIM(
            SUBSTRING(Description,
                      CHARINDEX('-', Description) + 1,
                      LEN(Description) - CHARINDEX('-', REVERSE(Description))
                                       - CHARINDEX('-', Description))))
WHERE Title IS NULL